玖叶教程网

1.使用游标方式进行分页

create PROCEDURE [dbo].[P_PageData] 
@sqlstr nvarchar(3500), --SQL字串
@curpage int, --第N页
@pagesize int --每页数量
AS
begin
set nocount on
declare @P1 int, --P1是游标的id
 @rowcount int, @pagecount int
SET @sqlstr = RTRIM(@sqlstr)
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
set @pagecount = ceiling(1.0*@rowcount/@pagesize)
if @curpage<=1
 set @curpage =1
if @curpage>@pagecount
 set @curpage=@pagecount
select @rowcount as Total, @pagecount as PageCount,@curpage as CurPage 
set @curpage=(@curpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@curpage,@pagesize 
exec sp_cursorclose @P1
end

2.使用如下：

exec P_PageData 'select * from user order by id desc',1,10