SQL Server 分頁查詢預存程序（支援動態表名、欄位、排序）

需求背景

大量資料查詢時需分頁顯示，避免一次傳輸過多資料導致記憶體/網路壓力。本文提供一個通用分頁預存程序，支援動態表名、查詢欄位、主鍵、排序、條件。

預存程式碼

CREATE PROCEDURE SelectPage
    @TableName   NVARCHAR(64),      -- 表名
    @KeyField    NVARCHAR(64),      -- 主鍵/排序欄位（需唯一）
    @Fields      NVARCHAR(512),     -- 查詢欄位，* 或逗號分隔列表
    @PageIndex   INT,               -- 頁碼（從 1 開始）
    @PageSize    INT = 15           -- 每頁筆數
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    -- 第 1 頁：直接 TOP
    IF @PageIndex = 1
    BEGIN
        SET @sql = N'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + N' ' + @Fields +
                   N' FROM ' + QUOTENAME(@TableName);
    END
    ELSE
    BEGIN
        -- 第 N 頁：WHERE KeyField > (第 (N-1) 頁最後一筆的 KeyField 最大值)
        -- 子查詢：取前 (N-1)*PageSize 筆的最大 KeyField
        SET @sql = N'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + N' ' + @Fields +
                   N' FROM ' + QUOTENAME(@TableName) +
                   N' WHERE ' + QUOTENAME(@KeyField) + N' > (' +
                   N'    SELECT MAX(' + QUOTENAME(@KeyField) + N') FROM (' +
                   N'        SELECT TOP ' + CAST((@PageIndex - 1) * @PageSize AS NVARCHAR(10)) + N' ' + QUOTENAME(@KeyField) +
                   N'        FROM ' + QUOTENAME(@TableName) +
                   N'        ORDER BY ' + QUOTENAME(@KeyField) + N' ASC' +
                   N'    ) AS T' +
                   N')' +
                   N' ORDER BY ' + QUOTENAME(@KeyField) + N' ASC';
    END

    -- PRINT @sql;  -- 除錯用
    EXEC sp_executesql @sql;
END;
GO

使用範例

-- 查詢 Orders 表，第 3 頁，每頁 20 筆，按 OrderID 排序
EXEC SelectPage @TableName = 'Orders', @KeyField = 'OrderID', @Fields = '*', @PageIndex = 3, @PageSize = 20;

-- 指定欄位、加條件（需自行修改 SP 擴充 @Condition 參數）

原理解析

1. 第 1 頁：TOP N ORDER BY KeyField，最簡單、效能最好（可用索引）。
2. 第 N 頁：利用 鍵值游標法——找出前 (N-1)*PageSize 筆的最大鍵值，再查 WHERE KeyField > 該值。避免了 OFFSET 掃描前 N 頁的開銷。
3. 前提：@KeyField 必須唯一且有索引（主鍵最佳）。若非唯一，需改用 ROW_NUMBER() 視窗函數。

現代寫法對比（SQL Server 2012+ / MySQL 8.0+ / PostgreSQL）

-- 標準 OFFSET/FETCH（簡潔、可讀、優化器支援良好）
SELECT * FROM Orders
ORDER BY OrderID
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;  -- 第 3 頁，每頁 20

• OFFSET/FETCH 內部同樣優化為鍵值游標或行號，語法標準、可移植。
• 動態 SQL 場景（表名/欄位為變數）仍需 sp_executesql，但內部查詢可用標準分頁語法。

效能優化建議

擴充版：支援條件、排序、總筆數輸出

CREATE PROCEDURE SelectPageEx
    @TableName   NVARCHAR(128),
    @KeyField    NVARCHAR(128),
    @Fields      NVARCHAR(1000) = '*',
    @PageIndex   INT,
    @PageSize    INT = 20,
    @Condition   NVARCHAR(2000) = '',  -- WHERE 條件（不含 WHERE 關鍵字）
    @OrderBy     NVARCHAR(200)  = '',  -- ORDER BY 條件（不含 ORDER BY 關鍵字）
    @TotalCount  INT OUTPUT,
    @TotalPages  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX), @where NVARCHAR(2100), @order NVARCHAR(300);

    SET @where = CASE WHEN LEN(@Condition) > 0 THEN N' WHERE ' + @Condition ELSE N'' END;
    SET @order = CASE WHEN LEN(@OrderBy) > 0 THEN N' ORDER BY ' + @OrderBy ELSE N' ORDER BY ' + QUOTENAME(@KeyField) END;

    -- 總筆數
    SET @sql = N'SELECT @TotalCount = COUNT(*) FROM ' + QUOTENAME(@TableName) + @where;
    EXEC sp_executesql @sql, N'@TotalCount INT OUTPUT', @TotalCount OUTPUT;
    SET @TotalPages = (@TotalCount + @PageSize - 1) / @PageSize;
    IF @PageIndex > @TotalPages SET @PageIndex = @TotalPages;
    IF @PageIndex < 1 SET @PageIndex = 1;

    -- 分頁查詢（使用 OFFSET/FETCH，SQL Server 2012+）
    DECLARE @offset INT = (@PageIndex - 1) * @PageSize;
    SET @sql = N'SELECT ' + @Fields + N' FROM ' + QUOTENAME(@TableName) + @where + @order +
               N' OFFSET ' + CAST(@offset AS NVARCHAR(10)) + N' ROWS FETCH NEXT ' + CAST(@PageSize AS NVARCHAR(10)) + N' ROWS ONLY';
    EXEC sp_executesql @sql;
END;
GO

小結

• 舊版 SQL Server：鍵值游標法（需唯一鍵）或 ROW_NUMBER() 視窗函數。
• SQL Server 2012+：優先使用標準 OFFSET ... FETCH，語法簡潔、優化器友善。
• 動態 SQL 必須防注入：表名/欄位名用 QUOTENAME() 包裹，條件值用參數化。
• 深分頁效能瓶頸：前端改用「游標式分頁」（傳遞上頁最大 ID），避免大 OFFSET。