SQL Server 分頁預存程序：支援多表聯查的通用方案

SQL Server 分頁預存程序：支援多表聯查的通用方案

針對大數據量場景，本文提供一個支援多表聯查、動態排序、可選總記錄數統計的通用分頁預存程序，採用「雙重 TOP 巢狀查詢」邏輯，相容 SQL Server 2005+ 版本。

預存程序完整碼

CREATE PROCEDURE [dbo].[getInQuiryAllByPage]
    @tableNames varchar(5000),      -- 表名，支援多表，逗號分隔
    @tbFields varchar(5000) = '*',  -- 欄位名，多表需帶前綴
    @conditionStr varchar(5000) = '', -- WHERE 條件，不含 WHERE 關鍵字
    @sortedStr varchar(5000),       -- 排序欄位，必填，含 ASC/DESC，不含 ORDER BY
    @needCount bit = 1,             -- 是否統計總記錄數
    @pageIndex int = 1,             -- 頁碼（從 1 開始）
    @pageSize int = 10,             -- 每頁筆數
    @recordCount int = 0 OUTPUT,    -- 返回總記錄數
    @pageCount int = 0 OUTPUT       -- 返回總頁數
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX), @sortStr NVARCHAR(MAX), @sortStr2 NVARCHAR(MAX), @sortStr3 NVARCHAR(MAX);
    DECLARE @whereStr NVARCHAR(MAX);

    -- 標準化排序字串：將 DESC->ASC、ASC->DESC 用於反向排序
    SET @sortStr = LOWER(@sortedStr);
    SET @sortStr2 = REPLACE(REPLACE(REPLACE(@sortStr, 'desc', ' @a@ '), 'asc', ' @d@ '), ' @a@ ', ' asc ');
    SET @sortStr2 = REPLACE(@sortStr2, ' @d@ ', ' desc ');
    
    -- 移除表別名前綴（a. b. c. 等）以構建外層排序
    SET @sortStr3 = @sortStr;
    SET @sortStr3 = REPLACE(@sortStr3, 'a.', ''); -- 可擴展更多別名
    SET @sortStr3 = REPLACE(@sortStr3, 'b.', '');
    -- ... 省略其他別名替換
    
    SET @sortStr = ' ORDER BY ' + @sortedStr;
    SET @sortStr2 = ' ORDER BY ' + @sortStr2;
    SET @sortStr3 = ' ORDER BY ' + @sortStr3;

    -- WHERE 條件處理
    IF (@conditionStr IS NOT NULL AND LTRIM(RTRIM(@conditionStr)) <> '')
        SET @whereStr = ' WHERE ' + @conditionStr;
    ELSE
        SET @whereStr = '';

    -- 統計總記錄數
    IF (@needCount = 1 OR @pageIndex = 1)
    BEGIN
        DECLARE @R INT;
        SET @sql = 'SELECT @R = COUNT(*) FROM ' + @tableNames + @whereStr;
        EXEC SP_EXECUTESQL @sql, N'@R INT OUTPUT', @R OUTPUT;
        SET @recordCount = @R;
        SET @pageCount = CASE WHEN @pageSize > 0 THEN ((@recordCount - 1) / @pageSize) + 1 ELSE 1 END;
    END

    -- 分頁查詢邏輯
    IF (@pageIndex < 2)  -- 第一頁
    BEGIN
        SET @sql = 'SELECT TOP ' + CAST(@pageSize AS VARCHAR(10)) + ' ' + @tbFields + 
                   ' FROM ' + @tableNames + @whereStr + @sortStr;
    END
    ELSE  -- 非第一頁
    BEGIN
        IF (@recordCount > @pageIndex * @pageSize)
        BEGIN
            -- 標準雙重 TOP 邏輯
            SET @sql = 'SELECT * FROM ( ' +
                       'SELECT TOP ' + CAST(@pageSize AS VARCHAR(10)) + ' * FROM ( ' +
                       'SELECT TOP ' + CAST(@pageSize * @pageIndex AS VARCHAR(10)) + ' ' + @tbFields + 
                       ' FROM ' + @tableNames + @whereStr + @sortStr + 
                       ') AS a ' + @sortStr2 + 
                       ') AS b ' + @sortStr3;
        END
        ELSE
        BEGIN
            -- 最後一頁優化：直接取剩餘記錄
            DECLARE @lastPageSize INT = @recordCount - @pageSize * (@pageCount - 1);
            SET @sql = 'SELECT * FROM ( ' +
                       'SELECT TOP ' + CAST(@lastPageSize AS VARCHAR(10)) + ' ' + @tbFields + 
                       ' FROM ' + @tableNames + @whereStr + @sortStr2 + 
                       ') AS a ' + @sortStr3;
        END
    END

    -- 執行動態 SQL
    EXEC SP_EXECUTESQL @sql;
END

使用範例

-- 多表聯查分頁：查詢訂單與客戶資訊，按訂單日期倒序
DECLARE @rc INT, @pc INT;
EXEC getInQuiryAllByPage 
    @tableNames = 'Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID',
    @tbFields = 'o.OrderID, o.OrderDate, c.CustomerName, c.City',
    @conditionStr = 'o.OrderDate > ''2023-01-01''',
    @sortedStr = 'o.OrderDate DESC',
    @pageIndex = 2,
    @pageSize = 20,
    @recordCount = @rc OUTPUT,
    @pageCount = @pc OUTPUT;
SELECT @rc AS TotalRecords, @pc AS TotalPages;

關鍵設計說明

• 動態 SQL 防注入： 僅信任內部調用，外部應用請透過參數化介面調用，嚴禁直接拼接用戶輸入。
• 排序字串雙重反轉： 內層正序、中層反序、外層正序，確保最終結果順序正確。
• 最後一頁優化： 避免大 OFFSET 掃描，直接計算剩餘筆數查詢。
• 相容性： 不依賴 OFFSET/FETCH (SQL Server 2012+)，支援舊版本。

效能優化建議

1. 確保排序欄位有索引覆蓋。
2. 大數據量建議改用 Keyset Pagination (Seek Method) 替代 OFFSET 分頁。
3. 將常用查詢封裝為 Table-Valued Function，減少動態 SQL 解析開銷。