存储过程分页

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

-- =============================================
-- Author:       
-- Create date: 2008-12-16
-- Description:    支持DISTINCT的通用分页存储过程（SQL2005）
-- =============================================
ALTER PROCEDURE [dbo].[GetPaged]
(
    -- 列名称
    @Fields            NVARCHAR(1000) = '*',
    -- 表名称
    -- 不允许为空
    @Table            NVARCHAR(1000),
    -- 查询条件
    @Where            NVARCHAR(1000) = '',
    -- 排序的字段
    -- 不允许为空，无排序字段时可指定主键
    @Order            NVARCHAR(100),
    -- 页码
    @PageIndex        INT = 1,
    -- 每页数据
    @PageSize        INT = 10,
    -- 是否使用DISTINCT
    @UseDistinct    BIT = 0,
    -- 处理动作
    -- 0表示返回查询结果和总数，1表示只返回查询结果，2表示只返回总数
    @Action            TINYINT = 0
)
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
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
  DECLARE @tempField  NVARCHAR(1000)
  if(@UseDistinct = 1)
   set @tempField='*'
  ElSE
   set @tempField=@Fields
        SET @sql = 'SELECT * FROM (SELECT ' + @tempField + ',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
END

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;