多表查询的存储过程分页

来源：互联网发布：淘宝运营简历编辑：程序博客网时间：2024/04/29 09:54

该存储过程源程序引用http://jiny-z.cnblogs.com/archive/2006/04/12/373146.html，由于其中多表操作时有一个BUG，做了改进，修改的部分红色标示，解决了@PrimaryKey = 'Table.ID'（表名.主键）时，参数@Type无法赋值，进而查询结果为空的问题。

修改后的代码如下：

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

    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 = UPPER(@Order)
        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 @SortTable = @TableNames
SET @SortColumn = @PrimaryKey
SET @SortName = @SortColumn

SET @pos1 = CHARINDEX('.', @SortName)
        IF @pos1 > 0
            BEGIN
                SET @SortTable = SUBSTRING(@SortName, 1, @pos1-1)
                SET @SortName = SUBSTRING(@SortName, @pos1 + 1, LEN(@SortName))
            END

        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

    Declare @StrSQL NVarChar(2000)
    Set @StrSQL = 'DECLARE @SortColumnBegin ' + CAST(@type AS VARCHAR) + '
  SET ROWCOUNT ' + CAST(@TopRows AS varchar) +'
  SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
        SET ROWCOUNT ' + CAST(@PageSize AS VARCHAR) + '
        SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + ' ' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '
      '
    Exec(@StrSQL)
    Print(@StrSQL)

END