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

王朝mssql·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

存储过程定义:

/**//****** 对象: 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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航