存储过程分页方案

 1 create procedure [StorePaged]
 2 @sql varchar(8000),    --要执行的sql语句
 3 @page int=1,         --要显示的页码
 4 @pageSize int,        --每页的大小
 5 @pageCount int=0 out,    --总页数
 6 @recordCount int=0 out    --总记录数
 7 as
 8 set nocount off
 9 if @page < 1 set @Page = 1
10 if @pageSize < 1 set @pageSize = 5
11 declare @p1 int,@CurrentPageOut int
12 set @CurrentPageOut = @Page
13 exec sp_cursoropen @p1 output,@sql,@scrollopt = 1,@ccopt = 1,@rowcount = @pagecount output
14 set @recordCount = @pageCount
15 select @pagecount=ceiling(1.0 * @pagecount/@pagesize),@page = (@page - 1) * @pagesize + 1
16 exec sp_cursorfetch @p1,16,@page,@pagesize
17 exec sp_cursorclose @p1
18 select @pagecount as PageCount,@recordCount as RecordCount,@CurrentPageOut as CurrentPage,@pageSize as PageSize