存储过程定义：
/**//****** 对象: 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
