/****** 对象: StoredProcedure [dbo].[P_CommonPagination] 脚本日期: 07/22/2009 10:22:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: 2008-12-16 -- Description: 支持DISTINCT的通用分页存储过程(SQL2005) -- ============================================= CREATE PROCEDURE [dbo].[P_CommonPagination] ( -- 列名称 @Fields NVARCHAR(MAX) = '*', -- 表名称 -- 不允许为空 @Table NVARCHAR(MAX), -- 查询条件 @Where NVARCHAR(MAX) = '', -- 排序的字段 -- 不允许为空,无排序字段时可指定主键 @Order NVARCHAR(MAX), -- 页码 @PageIndex INT = 1, -- 每页数据 @PageSize INT = 10, -- 是否使用DISTINCT @UseDistinct BIT = 0, -- 处理动作 -- 0表示返回查询结果和总数,1表示只返回查询结果,2表示只返回总数 @Action TINYINT = 0, -- 记录总数 @TotalCount BIGINT OUTPUT ) AS SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- ************************** -- 未做输入参数检查 -- ************************** DECLARE @sql NVARCHAR(MAX) DECLARE @Distinct NVARCHAR(50) IF @Fields = '' SET @Fields = '*' SET @Order = ' ORDER BY ' + @Order IF @Where <> '' SET @Where = ' WHERE ' + @Where SET @TotalCount = 0 IF @UseDistinct = 1 SET @Distinct = ' DISTINCT ' ELSE SET @Distinct = '' IF @Action <> 2 BEGIN -- Get Paging Data IF @PageIndex = 1 SET @sql = 'SELECT ' + @Distinct + ' TOP ' + STR(@PageSize) + ' ' + @Fields + ',0 AS RowNumber FROM ' + @Table + ' ' + @Where + ' ' + @Order ELSE BEGIN DECLARE @BeginRowNumber INT SET @BeginRowNumber = (@PageIndex - 1) * @PageSize + 1 DECLARE @EndRowNumber INT SET @EndRowNumber = @PageIndex * @PageSize SET @sql = 'SELECT ' + @Fields + ' FROM (SELECT ' + @Fields + ',ROW_NUMBER() OVER(' + @Order + ') AS RowNumber FROM ' IF @UseDistinct = 1 SET @sql = @sql + '(SELECT DISTINCT ' + @Fields + ' FROM ' + @Table + ' ' + @Where + ') AS S' ELSE SET @sql = @sql + @Table + ' ' + @Where SET @sql = @sql + ') AS T WHERE RowNumber BETWEEN ' + STR(@BeginRowNumber)+' AND ' + STR(@EndRowNumber) + ' ORDER BY RowNumber' END --print @sql EXEC SP_EXECUTESQL @sql END IF @Action <>1 BEGIN -- Get TotalCount SET @sql = 'SELECT @TotalCount=COUNT(1) FROM (SELECT ' + @Distinct + ' ' +@Fields+ ' FROM '+ @Table + ' ' + @Where +') AS temptable' --print @sql EXEC SP_EXECUTESQL @sql, N'@TotalCount INT OUTPUT', @TotalCount OUTPUT END SET TRANSACTION ISOLATION LEVEL READ COMMITTED;