SQL 分页存取过程

create proc get_titles_info
(
 @pagesize int ,---每页显示的记录数
 @currentpage int,----需要现实的第几页
 @totalpage int output ----一个输出参数  --页的总数
)
as
 ----声明保存查询语句的局部变量
declare @strQuery as varchar(1000)
 ----声明保存记录总数的局部变量
declare @rowcount as int
select  @rowcount=count(title_id) from titles
 ---将根据每页的行数得到的总页数 保存到 输出参数中

 set @totalpage=ceiling(cast(@rowcount as float)/cast(@pagesize as float))

if @currentpage>1
 begin
  -----如果当前页数的输入至大于总页数
  if @currentpage>@totalpage
  begin
   set @currentpage=@totalpage----当前页等于最后一页
  end
  
  set @strQuery ='select top'+cast(@pagesize as varchar(10))+' * from titles
    where title_id not in (select top'+cast(@pagesize*(@currentpage-1) as varchar(10))+' title_id from titles order by title_id)
  order by title_id'
 end
else
 begin
  set @strQuery ='select top'+cast(@pagesize as varchar(10))+'* from userinfo order by userid'
  
 end
go

declare @total int

exec get_titles_info 2,2,@total output
print '总页数为:'+cast(@total as varchar(6))+'页'
go