SQL分页存储过程

CREATE PROCEDURE proc_Pagerlister
@TableNames  VARCHAR(200),    --表名，可以是多个表，但不能用别名
@PrimaryKey  VARCHAR(100),    --主键，可以为空，但@Order为空时该值不能为空
@Fields      VARCHAR(200),        --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize    INT,            --每页记录数
@CurrentPage INT,        --当前页，0表示第1页
@Filter      VARCHAR(200)  = '',    --条件，可以为空，不用填 where
@Group       VARCHAR(200)  = '',    --分组依据，可以为空，不用填 group by
@Order       VARCHAR(200)  = ''    --排序，可以为空，为空默认按主键升序排列，不用填 order by
WITH ENCRYPTION
AS
  SET NoCount  ON

DECLARE  @SortColumn VARCHAR(200)
DECLARE  @Operator CHAR(2)
DECLARE  @SortTable VARCHAR(200)
DECLARE  @SortName VARCHAR(200)
BEGIN
    IF @Fields = ''
      SET @Fields = '*'
    IF @Filter = ''
      SET @Filter = 'WHERE 1=1'
    ELSE
      SET @Filter = 'WHERE ' + @Filter
    IF @Group <> ''
      SET @Group = 'GROUP BY ' + @Group
    IF @Order <> ''
      BEGIN
        DECLARE  @pos1 INT,
                 @pos2 INT
        SET @Order = REPLACE(REPLACE(@Order,' asc',' ASC'),' desc',' DESC')

        IF Charindex(' DESC',@Order) > 0
          IF Charindex(' ASC',@Order) > 0
            BEGIN
              IF Charindex(' DESC',@Order) < Charindex(' ASC',@Order)
                SET @Operator = '<='
              ELSE
                SET @Operator = '>='
            END
          ELSE
            SET @Operator = '<='
        ELSE
          SET @Operator = '>='

        SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order,' ASC',''),' DESC',''),
                                  ' ','')

        SET @pos1 = Charindex(',',@SortColumn)

        IF @pos1 > 0
          SET @SortColumn = Substring(@SortColumn,1,@pos1 - 1)

        SET @pos2 = Charindex('.',@SortColumn)

        IF @pos2 > 0
          BEGIN
            SET @SortTable = Substring(@SortColumn,1,@pos2 - 1)

            IF @pos1 > 0
              SET @SortName = Substring(@SortColumn,@pos2 + 1,@pos1 - @pos2 - 1)
            ELSE
              SET @SortName = Substring(@SortColumn,@pos2 + 1,Len(@SortColumn) - @pos2)
          END
        ELSE
          BEGIN
            SET @SortTable = @TableNames

            SET @SortName = @SortColumn
          END
      END
    ELSE
      BEGIN
        SET @SortColumn = @PrimaryKey

        SET @SortTable = @TableNames

        SET @SortName = @SortColumn

        SET @Order = @SortColumn

        SET @Operator = '>='
      END

    DECLARE  @type VARCHAR(50)

    DECLARE  @prec INT

    SELECT @type = t.Name,
           @prec = c.prec
    FROM   sySobjects o
           JOIN sysColumns c
             ON o.Id = c.Id
           JOIN syStypes t
             ON c.xUserType = t.xUserType
    WHERE  o.Name = @SortTable
           AND c.Name = @SortName

    IF Charindex('char',@type) > 0
      SET @type = @type + '(' + CAST(@prec AS VARCHAR) + ')'

    DECLARE  @TopRows INT

    SET @TopRows = @PageSize * @CurrentPage + 1

    PRINT @TopRows

    PRINT @Operator

    EXEC( '
                    DECLARE @SortColumnBegin ' + @type + '
                    SET ROWCOUNT ' + @TopRows + '
                    SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
                    SET ROWCOUNT ' + @PageSize + '
                    SELECT ' + @Fields + ' FROM  ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '
                ')
  END