存储过程分页

use pubs
go

alter proc feiye
@pageSize int, --每页中记录数
@pageNum int  --第几页
as
declare @count int --总记录数
declare @currentpage int --当前页实际的记录数
declare @sum int --需要显示的最多记录数

set @count =(select count(*) from authors)

set @sum =@pageSize * @pageNum
if(@sum >@count)
 set @currentpage =@count % @pagesize8
else
 set @currentpage =@pagesize
if(@sum<=0)
begin
 set @sum =@pageSize * 1
 
 set @currentpage =@pagesize
end
 
 exec('select top '+@currentpage+' * from (select top '+@currentpage+' * from( select top '+@sum+' * from authors as a order by au_id asc) as b order by au_id desc) as c order by au_id asc')

 

 

exec feiye @pageSize=5,@pageNum=-3