SQL Server 2005 通用分頁預存程序 (支援多表聯查、CTE、動態排序)

通用分頁預存程序 proc_SelectForPager，接受動態 SQL 查詢、排序欄位、頁碼、頁面大小，輸出總筆數與當頁資料。採用 ROW_NUMBER() OVER 視窗函數 (SQL 2005+ 支援)，效能優於舊版 TOP ... NOT IN 寫法。

預存程序完整碼

IF OBJECT_ID('proc_SelectForPager') IS NOT NULL
    DROP PROCEDURE proc_SelectForPager;
GO

CREATE PROCEDURE proc_SelectForPager
    @Sql        NVARCHAR(MAX),   -- 查詢語句 (不含 ORDER BY)
    @Order      NVARCHAR(4000),  -- 排序子句 (不含 ORDER BY 關鍵字)
    @CurrentPage INT,           -- 目前頁碼 (1-based)
    @PageSize   INT,            -- 每頁筆數
    @TotalCount INT OUTPUT      -- 總筆數輸出
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ExecSql NVARCHAR(MAX);

    -- 1. 取得總筆數
    SET @ExecSql = N'SET @TotalCount = (SELECT COUNT(1) FROM (' + @Sql + N') AS a)';
    EXEC sp_executesql @ExecSql, N'@TotalCount INT OUTPUT', @TotalCount OUTPUT;

    -- 2. 處理排序
    SET @Order = ISNULL(' ORDER BY ' + NULLIF(@Order, ''), ' ORDER BY GETDATE()');

    -- 3. 組合分頁查詢 (第 1 頁優化：少一層 CTE)
    IF @CurrentPage = 1
        SET @ExecSql = N'
            ;WITH CTE_Exec AS (' + @Sql + N')
            SELECT TOP(@PageSize) *, ROW_NUMBER() OVER(' + @Order + N') AS r
            FROM CTE_Exec ORDER BY r';
    ELSE
        SET @ExecSql = N'
            ;WITH CTE_Exec AS (
                SELECT *, ROW_NUMBER() OVER(' + @Order + N') AS r
                FROM (' + @Sql + N') AS a
            )
            SELECT * FROM CTE_Exec
            WHERE r BETWEEN (@CurrentPage - 1) * @PageSize + 1 AND @CurrentPage * @PageSize
            ORDER BY r';

    -- 4. 執行
    EXEC sp_executesql @ExecSql, N'@CurrentPage INT, @PageSize INT', @CurrentPage, @PageSize;
END
GO

呼叫範例

單表查詢

DECLARE @Total INT;
EXEC proc_SelectForPager
    @Sql = N'SELECT * FROM Contacts WHERE ContactType = 1',
    @Order = N'ContactID DESC',
    @CurrentPage = 3,
    @PageSize = 20,
    @TotalCount = @Total OUTPUT;
SELECT @Total AS TotalRows;

多表聯查

DECLARE @Total INT;
EXEC proc_SelectForPager
    @Sql = N'
        SELECT a.Staff, a.OU, b.FName + b.LName AS Name
        FROM StaffOUHist a
        INNER JOIN Staff b ON b.ID = a.Staff AND a.ExpiryDate = ''30001231''
    ',
    @Order = N'Name',
    @CurrentPage = 3,
    @PageSize = 20,
    @TotalCount = @Total OUTPUT;
SELECT @Total AS TotalRows;

使用限制與注意事項

• @Sql 參數不可包含 CTE (即不能有 WITH ... AS)，否則巢狀 CTE 會語法錯誤。
• 動態 SQL 注入風險：呼叫端務必確保 @Sql 來源可信，避免串接使用者輸入。
• 大量資料效能：ROW_NUMBER() 需對全結果集排序，百萬列以上建議改用 Keyset Pagination (游標式分頁) 或 OFFSET/FETCH (SQL 2012+)。
• 排序欄位唯一性：@Order 建議包含唯一鍵 (如 PK)，避免相同排序值導致頁面資料重複/遺漏。

進階擴充建議

• 加入 @WhereClause 參數，由程序內部組合 WHERE 子句，減少動態 SQL 風險。
• 支援 @SelectColumns 指定輸出欄位，避免 SELECT *。
• 輸出 @PageCount 總頁數供前端直接使用。