存储过程分页方案
方案一:
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
