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
        @p1,
        16,
        @CurrentPageIndex,
        @PageSize;

EXEC    sp_cursorclose
        @p1

 调用方法：

DECLARE    @return_value int,
        @RowCount int,
        @PageCount int

EXEC    @return_value = [dbo].[SplitPage]
        @SelectCommandText = N'SELECT * FROM Log',
        @CurrentPageIndex = 0,
        @PageSize = 4,
        @RowCount = @RowCount OUTPUT,
        @PageCount = @PageCount OUTPUT

SELECT    @RowCount as N'@RowCount',
        @PageCount as N'@PageCount'

SELECT    'Return Value' = @return_value

GO