萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

萬用分頁預存程序:支援多表聯查、任意排序、大數據量

設計目標

  • 支援多表聯查(不支援資料表別名)
  • 支援任意 ORDER BY 排序
  • 適用於千萬筆資料量的高效分頁
  • 參數化設計,避免動態 SQL 注入風險

預存程式碼完整版

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('usp_PagingLarge') AND xtype = 'P')
    DROP PROCEDURE usp_PagingLarge;
GO

CREATE PROCEDURE usp_PagingLarge
    @TableNames VARCHAR(200),   -- 表名,可多表,不可用別名
    @PrimaryKey VARCHAR(100),   -- 主鍵,@Order 為空時必填
    @Fields     VARCHAR(200) = '*',      -- 欲查欄位,* 表所有
    @PageSize   INT,                     -- 每頁筆數
    @CurrentPage INT = 0,                -- 當前頁(0 為第一頁)
    @Filter     VARCHAR(200) = '',       -- WHERE 條件,不含 WHERE 關鍵字
    @Group      VARCHAR(200) = '',       -- GROUP BY 條件,不含 GROUP BY
    @Order      VARCHAR(200) = ''        -- ORDER BY 條件,不含 ORDER BY
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SortColumn VARCHAR(200), @Operator CHAR(2), @SortTable VARCHAR(200), @SortName VARCHAR(200);
    DECLARE @pos1 INT, @pos2 INT;

    -- 預設欄位
    IF @Fields = '' SET @Fields = '*';

    -- 處理 WHERE
    IF @Filter = ''
        SET @Filter = 'WHERE 1=1';
    ELSE
        SET @Filter = 'WHERE ' + @Filter;

    -- 處理 GROUP BY
    IF @Group <> '' SET @Group = 'GROUP BY ' + @Group;

    -- 處理 ORDER BY 與排序方向判斷
    IF @Order <> ''
    BEGIN
        SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC');

        IF CHARINDEX(' DESC', @Order) > 0
        BEGIN
            IF CHARINDEX(' ASC', @Order) > 0
            BEGIN
                -- 同時包含 ASC 與 DESC,取較早出現者為主要排序
                IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
                    SET @Operator = '<=';
                ELSE
                    SET @Operator = '>=';
            END
            ELSE
                SET @Operator = '<=';
        END
        ELSE
            SET @Operator = '>=';
    END
    ELSE
    BEGIN
        -- 無 ORDER BY,預設用主鍵升序
        SET @Order = @PrimaryKey + ' ASC';
        SET @Operator = '>=';
    END

    -- 解析排序欄位(支援 Table.Column 格式)
    SET @SortColumn = REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', '');
    SET @SortColumn = LTRIM(RTRIM(@SortColumn));

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

    -- 取得排序欄位資料型別與精度(用於動態宣告變數)
    DECLARE @Type VARCHAR(100), @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) + ')';

    -- 動態建構分頁查詢(使用 ROWCOUNT 技巧)
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @StartRow VARCHAR(20) = CAST((@CurrentPage * @PageSize + 1) AS VARCHAR(20));
    DECLARE @PageSizeStr VARCHAR(20) = CAST(@PageSize AS VARCHAR(20));

    SET @SQL = '
        DECLARE @SortColumn ' + @Type + ';
        SET ROWCOUNT ' + @StartRow + ';
        SELECT @SortColumn = ' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ';
        SET ROWCOUNT ' + @PageSizeStr + ';
        SELECT ' + @Fields + ' FROM ' + @TableNames + ' WHERE ' + @SortColumn + ' ' + @Operator + ' @SortColumn ' +
        CASE WHEN @Filter = 'WHERE 1=1' THEN '' ELSE ' AND ' + REPLACE(@Filter, 'WHERE ', '') END + ' ' + @Group + ' ORDER BY ' + @Order + ';';

    EXEC sp_executesql @SQL;
END;
GO

使用範例

-- 查詢 Orders 聯合 Customers,依 OrderDate 降序,第 3 頁,每頁 20 筆
EXEC usp_PagingLarge
    @TableNames = 'Orders o JOIN Customers c ON o.CustID = c.CustID',
    @PrimaryKey = 'o.OrderID',
    @Fields = 'o.OrderID, o.OrderDate, c.CustName',
    @PageSize = 20,
    @CurrentPage = 2,  -- 0-based,2 代表第 3 頁
    @Filter = 'o.OrderDate > ''2023-01-01''',
    @Order = 'o.OrderDate DESC';

效能說明

  • 利用 SET ROWCOUNT 先定位至目標頁首筆的排序鍵值,再抓取指定筆數,避免全表排序後再跳過。
  • 適用於有索引支援的排序欄位;無索引時仍需全表掃描。
  • SQL Server 2012+ 建議改用 OFFSET ... FETCH 原生語法,效能更穩定。
copyright © 萬盛學電腦網 all rights reserved