create database db_job
go
use db_job
go
create table t_student
(
f_id int identity(1,1) primary key,
f_name varchar(50),
f_sex varchar(50),
f_birthday datetime,
f_classId int
)
select top 20 * from t_student
execute sp_pager 5,3,0,0
--存储过程
create procedure sp_pager
@size int, --每页显示的记录数
@index int, --当前页码
@total int output, --总记录数
@count int output --总页数
as
declare @sql varchar(200) --执行动态SQL语句的变量
select @total=count(1) from t_student --获取总记录数
if(@total % @size = 0)
set @count = @total / @size --如果每一页都是满记录数
else
set @count = @total / @size + 1
if(@index=1) --当前页码是首页
set @sql = 'select top '+str(@size)+' * from t_student'
else
set @sql = 'select top '+str(@size)+' * from t_student where f_id >(select max(f_id) from t_student where f_id in(select top '+str(@size*(@index-1))+' f_id from t_student))'
execute(@sql)
