create proc get_titles_info( @pagesize int ,---每页显示的记录数 @currentpage int,----需要现实的第几页 @totalpage int output ----一个输出参数 --页的总数)as ----声明保存查询语句的局部变量declare @strQuery as varchar(1000) ----声明保存记录总数的局部变量declare @rowcount as intselect @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' endelse begin set @strQuery ='select top'+cast(@pagesize as varchar(10))+'* from userinfo order by userid' endgo
declare @total int
exec get_titles_info 2,2,@total outputprint '总页数为:'+cast(@total as varchar(6))+'页'go
