SqlServer支持多表关联的分页存储过程

具体代码如下所示：

CREATE PROCEDURE p_GetPager
    @TotalCount INT OUTPUT,     --总记录数
    @TotalPage INT OUTPUT,      --总页数
    @Table NVARCHAR(1000),      --表名称，可多表连接，如：TbA a LEFT JOIN TbB b ON a.User=b.User
    @Column NVARCHAR(1000),     --查询列，可多列，如：*或者a.ID,b.UserName
    @OrderColumn NVARCHAR(100), --排序字段，如：SortNo ASC或a.SortNo ASC,b.CrtTime DESC
    @GroupColumn NVARCHAR(150), --分组字段，如：UserName或a.UserName,b.Time
    @PageIndex INT,             --当前页数
    @PageSize INT,              --每页记录数
    @Group TINYINT,             --是否启用分组
    @Condition NVARCHAR(4000)   --查询条件，如：SortNo>100或a.SortNo>100 AND b.UserName='张三'
AS
DECLARE @PageCount INT,               --总页数
        @strSql NVARCHAR(4000),       --主查询语句
        @strTemp NVARCHAR(2000),      --临时变量
        @strCount NVARCHAR(1000),     --统计语句
        @strOrderType NVARCHAR(1000); --排序语句
BEGIN
    SET @PageCount = @PageSize * (@PageIndex - 1);
    SET @strOrderType = N' ORDER BY ' + @OrderColumn + N' ';
    IF @Condition != ''
    BEGIN
        IF @PageIndex = 1
        BEGIN
            IF @Group = 1
            BEGIN
                SET @strCount
                    = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' WHERE ' + @Condition + N' GROUP BY '
                      + @GroupColumn;
                SET @strCount = @strCount + N' SET @TotalCount=@@ROWCOUNT';
                SET @strSql
                    = N'SELECT TOP ' + STR(@PageSize) + N' ' + @Column + N' FROM ' + @Table + N' WHERE ' + @Condition
                      + N' GROUP BY ' + @GroupColumn + N' ' + @strOrderType;
            END;
            ELSE
            BEGIN
                SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' WHERE ' + @Condition;
                SET @strSql
                    = N'SELECT TOP ' + STR(@PageSize) + N' ' + @Column + N' FROM ' + @Table + N' WHERE ' + @Condition
                      + N' ' + @strOrderType;
            END;
        END;
        ELSE
        BEGIN
            IF @Group = 1
            BEGIN
                SET @strCount
                    = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' WHERE ' + @Condition + N' GROUP BY '
                      + @GroupColumn;
                SET @strCount = @strCount + N' SET @TotalCount=@@ROWCOUNT';
                SET @strSql
                    = N'SELECT * FROM (SELECT TOP (2000) ' + @Column + N',ROW_NUMBER() OVER(' + @strOrderType
                      + N') AS NUM FROM ' + @Table + N' WHERE ' + @Condition + N' GROUP BY ' + @GroupColumn
                      + N') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) + N' AND ' + STR(@PageCount + @PageSize);
            END;
            ELSE
            BEGIN
                SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' WHERE ' + @Condition;
                SET @strSql
                    = N'SELECT * FROM (SELECT TOP (2000) ' + @Column + N',ROW_NUMBER() OVER(' + @strOrderType
                      + N') AS NUM FROM ' + @Table + N' WHERE ' + @Condition + N') AS T WHERE NUM BETWEEN '
                      + STR(@PageCount + 1) + N' AND ' + STR(@PageCount + @PageSize);
            END;
        END;
    END;
    ELSE
    --没有查询条件
    BEGIN
        IF @PageIndex = 1
        BEGIN
            IF @Group = 1
            BEGIN
                SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' GROUP BY ' + @GroupColumn;
                SET @strCount = @strCount + N'SET @TotalCount=@@ROWCOUNT';
                SET @strSql
                    = N'SELECT TOP ' + STR(@PageSize) + N' ' + @Column + N' FROM ' + @Table + N' GROUP BY '
                      + @GroupColumn + N' ' + @strOrderType;
            END;
            ELSE
            BEGIN
                SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table;
                SET @strSql
                    = N'SELECT TOP ' + STR(@PageSize) + N' ' + @Column + N' FROM ' + @Table + N' ' + @strOrderType;
            END;
        END;
        ELSE
        BEGIN
            IF @Group = 1
            BEGIN
                SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' GROUP BY ' + @GroupColumn;
                SET @strCount = @strCount + N'SET @TotalCount=@@ROWCOUNT';
                SET @strSql
                    = N'SELECT * FROM (SELECT TOP (2000) ' + @Column + N',ROW_NUMBER() OVER(' + @strOrderType
                      + N') AS NUM FROM ' + @Table + N' GROUP BY ' + @GroupColumn + N') AS T WHERE NUM BETWEEN '
                      + STR(@PageCount + 1) + N' AND ' + STR(@PageCount + @PageSize);
            END;
            ELSE
            BEGIN
                SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table;
                SET @strSql
                    = N'SELECT * FROM (SELECT TOP (2000) ' + @Column + N',ROW_NUMBER() OVER(' + @strOrderType
                      + N') AS NUM FROM ' + @Table + N') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) + N' AND '
                      + STR(@PageCount + @PageSize);
            END;
        END;
    END;
    EXEC sp_executesql @strCount,
                       N'@TotalCount INT OUTPUT',
                       @TotalCount OUTPUT;
    IF @TotalCount > 2000
    BEGIN
        SET @TotalCount = 2000;
    END;
    IF @TotalCount % @PageSize = 0
    BEGIN
        SET @TotalPage = @TotalCount / @PageSize;
    END;
    ELSE
    BEGIN
        SET @TotalPage = @TotalCount / @PageSize + 1;
    END;
    SET NOCOUNT ON;
    EXEC (@strSql);
END;