SQL Server 通用分頁預存程序：動態 SQL 實作與參數化設計

預存程序簽名

CREATE PROCEDURE GetRecordByPage
    @TableName   NVARCHAR(100),   -- 目標資料表
    @PrimaryKey  NVARCHAR(50),    -- 主鍵欄位（單欄）
    @Fields      NVARCHAR(1000),  -- 要查詢的欄位清單，如 'id, name, age'
    @CurrentPage INT,             -- 當前頁碼（1-based）
    @PageSize    INT              -- 每頁筆數
AS
BEGIN
    SET NOCOUNT ON;

    -- 參數驗證
    IF @CurrentPage < 1 SET @CurrentPage = 1;
    IF @PageSize < 1 SET @PageSize = 20;

    -- 動態建構分頁查詢（採用 OFFSET/FETCH，SQL Server 2012+）
    DECLARE @sql NVARCHAR(MAX) = N'
        SELECT ' + @Fields + N'
        FROM ' + QUOTENAME(@TableName) + N'
        ORDER BY ' + QUOTENAME(@PrimaryKey) + N'
        OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY;';

    DECLARE @offset INT = (@CurrentPage - 1) * @PageSize;

    EXEC sp_executesql @sql,
        N'@offset INT, @pageSize INT',
        @offset = @offset, @pageSize = @PageSize;
END;

關鍵設計要點

• 參數化動態 SQL：使用 sp_executesql 配合參數標記（@offset, @pageSize），避免 SQL 注入並促進計畫重用。
• QUOTENAME() 防注入：資料表名、欄位名經 QUOTENAME 包裹為 [識別碼] 形式。
• OFFSET/FETCH 語法：SQL Server 2012+ 原生支援，執行計畫穩定、可讀性高。舊版本需改用 ROW_NUMBER() OVER () 子查詢或 TOP + NOT IN 模式。
• 排序欄位固定為主鍵：確保分頁結果確定性；若需多欄位排序，可擴充參數 @OrderBy。

進階擴充：回傳總筆數與總頁數

-- 在同一批次中取得總數（避免雙次掃描可用 COUNT(*) OVER() 窗口函數，SQL 2012+）
DECLARE @totalCount INT;
SET @sql = N'
    SELECT @totalCount = COUNT(*) FROM ' + QUOTENAME(@TableName) + N';
    ' + @sql;  -- 合併為單一批次

EXEC sp_executesql @sql,
    N'@offset INT, @pageSize INT, @totalCount INT OUTPUT',
    @offset, @PageSize, @totalCount OUTPUT;

SELECT @totalCount AS TotalCount,
       CEILING(1.0 * @totalCount / @PageSize) AS TotalPages;

效能優化建議

1. 確保 @PrimaryKey 有叢集索引，ORDER BY 才能避免排序運算子。
2. 大資料表深分頁（大 @offset）時，OFFSET 仍需掃描前置列；改用 **Keyset Pagination**：
   WHERE pk > @lastSeenPk ORDER BY pk OFFSET 0 ROWS FETCH NEXT @PageSize ROWS ONLY。
3. 若 @Fields 固定，建議寫死查詢而非動態拼接，獲得最佳計畫穩定性。