SQL Server 通用分頁查詢預存程序：動態 SQL 實作與效能優化

前言

本文提供一個高復用性的通用分頁預存程序 prcPageResult，支援動態資料表、欄位、條件、排序、主鍵與頁面大小，採用「主鍵範圍過濾」策略，在百萬列資料下仍能維持穩定效能。另附記錄數統計預存程序 prcRowsCount。

prcPageResult 完整程式碼

CREATE PROCEDURE prcPageResult
    @currPage     INT = 1,          -- 目標頁碼 (1-based)
    @showColumn   VARCHAR(2000) = '*', -- 輸出欄位清單
    @tabName      VARCHAR(2000),       -- 來源資料表名
    @strCondition VARCHAR(2000) = '',  -- WHERE 條件（不含 WHERE 關鍵字）
    @ascColumn    VARCHAR(100) = '',   -- 排序欄位
    @bitOrderType BIT = 0,             -- 0=ASC, 1=DESC
    @pkColumn     VARCHAR(50) = '',    -- 主鍵欄位名（必填）
    @pageSize     INT = 20             -- 每頁筆數
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @strOrderType VARCHAR(1000),  -- ORDER BY 子句
            @strTemp      VARCHAR(1000),  -- 子查詢比較運算子
            @strSql       VARCHAR(4000);  -- 最終動態 SQL

    /* 組裝 ORDER BY 與子查詢比較邏輯 */
    IF @bitOrderType = 1
    BEGIN
        SET @strOrderType = ' ORDER BY ' + @ascColumn + ' DESC';
        SET @strTemp      = ' < (SELECT MIN(' + @pkColumn + ') FROM (SELECT TOP ' + CAST((@currPage-1)*@pageSize AS VARCHAR) + ' ' + @pkColumn + ' FROM ' + @tabName + @strOrderType + ') AS TabTemp)';
    END
    ELSE
    BEGIN
        SET @strOrderType = ' ORDER BY ' + @ascColumn + ' ASC';
        SET @strTemp      = ' > (SELECT MAX(' + @pkColumn + ') FROM (SELECT TOP ' + CAST((@currPage-1)*@pageSize AS VARCHAR) + ' ' + @pkColumn + ' FROM ' + @tabName + @strOrderType + ') AS TabTemp)';
    END

    /* 組裝主查詢 */
    IF @currPage = 1
    BEGIN
        SET @strSql = 'SELECT TOP ' + CAST(@pageSize AS VARCHAR) + ' ' + @showColumn + ' FROM ' + @tabName +
                      CASE WHEN @strCondition <> '' THEN ' WHERE ' + @strCondition ELSE '' END +
                      @strOrderType;
    END
    ELSE
    BEGIN
        SET @strSql = 'SELECT TOP ' + CAST(@pageSize AS VARCHAR) + ' ' + @showColumn + ' FROM ' + @tabName +
                      CASE WHEN @strCondition <> '' THEN ' WHERE ' + @strCondition + ' AND ' ELSE ' WHERE ' END +
                      @pkColumn + @strTemp + @strOrderType;
    END

    EXEC (@strSql);
END
GO

呼叫範例

-- 查詢 TableName 第 1 頁，每頁 25 筆，依 CreateDate 降冪，主鍵 PkID
EXEC prcPageResult 1, '*', 'TableName', '', 'CreateDate', 1, 'PkID', 25;

prcRowsCount 記錄數統計

CREATE PROC prcRowsCount
    @tabName   VARCHAR(200),
    @colName   VARCHAR(200) = '*',
    @condition VARCHAR(200) = ''
AS
BEGIN
    DECLARE @strSql VARCHAR(255);
    IF @condition = ''
        SET @strSql = 'SELECT COUNT(' + @colName + ') FROM ' + @tabName;
    ELSE
        SET @strSql = 'SELECT COUNT(' + @colName + ') FROM ' + @tabName + ' WHERE ' + @condition;
    EXEC (@strSql);
END
GO

效能優化關鍵點

安全性強化（防 SQL 注入）

1. 呼叫端嚴格驗證 @tabName、@pkColumn、@ascColumn 僅含合法識別碼字元 [a-zA-Z0-9_$]。
2. 或改用 QUOTENAME() 包裹識別碼：SET @tabName = QUOTENAME(@tabName);。
3. @strCondition 若來自使用者輸入，**嚴禁直接拼接**，改用參數化或白名單驗證。

現代替代方案（SQL Server 2012+）

-- 參數化、可讀性高、計畫重用佳
DECLARE @PageSize INT = 20, @PageIndex INT = 3;
SELECT col1, col2, col3
FROM dbo.MyTable
WHERE status = 1
ORDER BY created_at DESC
OFFSET (@PageIndex - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

適用版本與遷移建議

• SQL Server 2000/2005/2008/R2：可使用本預存程序。
• SQL Server 2012+：**強烈建議改用 OFFSET/FETCH**，原生支援、最佳化器友善、無動態 SQL 風險。
• MySQL/PostgreSQL：語法相同 LIMIT ... OFFSET ... 或 OFFSET ... LIMIT ...。