SQL Server数据库三种分页方案详尽分析

create  procedure XiaoZhengGe

@sqlstr nvarchar(4000), --查询字符串

@currentpage int, --第N页

@pagesize int --每页行数

as

set nocount on

declare @P1 int, --P1是游标的id

 @rowcount int

exec sp_cursoropen @P1 output,@sqlstr,

@scrollopt=1,@ccopt=1,@rowcount=@rowcount output

select ceiling(1.0*@rowcount/@pagesize) 

as 总页数--,@rowcount as 总行数,@currentpage as 当前页 

set @currentpage=(@currentpage-1)*@pagesize 1

exec sp_cursorfetch @P1,16,@currentpage,@pagesize 

exec sp_cursorclose @P1

set nocount off