SQLServer 通用分页存储过程(使用游标)
作者:青锋幽灵 日期:2008-09-10 01:58
SQL代码
- ----------------------------------------------------------------------
- -- 通用分页存储过程
- ----------------------------------------------------------------------
- CREATE PROCEDURE [dbo].[cmsdream_SP_MultiPage](
- @sql varchar(8000)='',
- @PageSize int=1,
- @CurrentPage int=1,
- @RecordCount int=0 output,
- @PageCount int=1 output
- )AS
- if @PageSize < 1 set @PageSize = 1
- if @CurrentPage < 1 set @CurrentPage = 1
- begin
- set nocount on
- declare @p1 int,
- @rowcount int
- exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
- set @PageCount = ceiling(1.0 * @rowcount / @PageSize)
- set @CurrentPage = (@CurrentPage - 1) * @PageSize + 1
- set @RecordCount = @rowcount
- exec sp_cursorfetch @p1, 16, @CurrentPage, @PageSize
- set nocount off
- end
- GO
- 1
