- create procedure pagination
- @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 TotalPages,@rowcount as TotalRows,@currentpage as CurPage
- set @currentpage=(@currentpage-1)*@pagesize+1
- exec sp_cursorfetch @P1,16,@currentpage,@pagesize
- exec sp_cursorclose @P1
- set nocount off
- --用法实例
- exec pagination 'select * from Table1',1,10
- --返回三个结果集,第一个是空的,第二个返回的列分别是总页数,总行数,当前页,第三个是查询的数据结果集。
http://www.cnblogs.com/easydata/archive/2008/06/20/1226347.html