SQL Server通用分页存储过程：利用SQL Server未公开的存储过程实现

存储过程定义：

/**//****** 对象: StoredProcedure [dbo].[SplitPage] 脚本日期: 04/23/2007 16:10:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[SplitPage]

(

@SelectCommandText nvarchar(4000), -- 要执行的查询命令

@CurrentPageIndex int = 0, -- 当前页的索引，从 0 开始

@PageSize int = 20, -- 每页的记录数

@RowCount int = 0 out, -- 总的记录数

@PageCount int = 0 out -- 总的页数

)

AS

SET NOCOUNT ON

DECLARE @p1 int

SET @CurrentPageIndex = @CurrentPageIndex + 1

EXEC sp_cursoropen

@p1 output,

@SelectCommandText,

@scrollopt = 1,

@ccopt = 1,

@RowCount = @RowCount output;

SELECT @RowCount;

SELECT @PageCount = ceiling(1.0 * @RowCount / @PageSize);

SELECT @CurrentPageIndex = (@CurrentPageIndex - 1) * @PageSize + 1

EXEC sp_cursorfetch