SQL Server 2005 高性能分頁存儲過程：基於 ROW_NUMBER() 的通用方案

設計目標

• 通用性：支持任意表、任意字段、多字段排序、動態 WHERE 條件。
• 高性能：利用 2005 引入的 ROW_NUMBER() OVER() 窗口函數，避免舊版 TOP + NOT IN 或游標的低效。
• 健壯性：事務包裹、參數校驗、邊界處理、錯誤回滾。

存儲過程完整代碼

CREATE PROCEDURE dbo.up_Page2005
    @TableName   VARCHAR(50),      -- 表名
    @Fields      VARCHAR(5000) = '*',       -- 字段列表，* 表所有
    @OrderField  VARCHAR(5000),             -- 排序字段（必填，支持多字段）
    @SqlWhere    VARCHAR(5000) = NULL,      -- WHERE 條件（不含 WHERE 關鍵字）
    @PageSize    INT,                       -- 每頁行數
    @PageIndex   INT = 1,                   -- 當前頁碼，默認 1
    @TotalPage   INT OUTPUT                 -- 返回總頁數
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRAN;

    DECLARE @sql NVARCHAR(4000), @totalRecord INT;

    -- 1. 計算總記錄數
    IF @SqlWhere IS NULL OR @SqlWhere = ''
        SET @sql = N'SELECT @totalRecord = COUNT(*) FROM ' + @TableName;
    ELSE
        SET @sql = N'SELECT @totalRecord = COUNT(*) FROM ' + @TableName + N' WHERE ' + @SqlWhere;

    EXEC sp_executesql @sql, N'@totalRecord INT OUTPUT', @totalRecord OUTPUT;

    -- 2. 計算總頁數（CEILING 需浮點除法）
    SELECT @TotalPage = CEILING((@totalRecord + 0.0) / @PageSize);

    -- 3. 邊界修正
    IF @PageIndex <= 0 SET @PageIndex = 1;
    IF @PageIndex > @TotalPage SET @PageIndex = @TotalPage;

    -- 4. 起止行號
    DECLARE @StartRecord INT = (@PageIndex - 1) * @PageSize + 1;
    DECLARE @EndRecord   INT = @StartRecord + @PageSize - 1;

    -- 5. 構建分頁查詢（CTE + ROW_NUMBER）
    IF @SqlWhere IS NULL OR @SqlWhere = ''
        SET @sql = N'SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderField + N') AS RowId, ' + @Fields + N' FROM ' + @TableName + N') AS ' + @TableName + N' WHERE RowId BETWEEN ' + CAST(@StartRecord AS VARCHAR(20)) + N' AND ' + CAST(@EndRecord AS VARCHAR(20));
    ELSE
        SET @sql = N'SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderField + N') AS RowId, ' + @Fields + N' FROM ' + @TableName + N' WHERE ' + @SqlWhere + N') AS ' + @TableName + N' WHERE RowId BETWEEN ' + CAST(@StartRecord AS VARCHAR(20)) + N' AND ' + CAST(@EndRecord AS VARCHAR(20));

    -- 6. 執行並返回結果集
    EXEC(@sql);

    -- 7. 錯誤處理
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRAN;
        RETURN -1;
    END
    ELSE
    BEGIN
        COMMIT TRAN;
        RETURN @totalRecord;  -- 返回總記錄數便於前端計算
    END
END

關鍵點解析

1. sp_executesql 參數化輸出總記錄數，避免動態 SQL 注入風險。
2. CEILING((@totalRecord+0.0)/@PageSize) 強制浮點除法，精確計算總頁數。
3. ROW_NUMBER() OVER(ORDER BY ...) 確保排序確定性，**@OrderField 必填且建議含唯一鍵**，否則分頁結果不穩定。
4. 事務僅包裹讀操作，實際上可移除 BEGIN TRAN/COMMIT，保留僅為模板完整性；若涉及寫操作則必須保留。
5. 返回值：成功返回總記錄數，失敗返回 -1，前端可據此判斷。

使用示例

DECLARE @tp INT;
EXEC up_Page2005
    @TableName = 'Orders',
    @Fields = 'OrderID, CustomerID, OrderDate, TotalAmount',
    @OrderField = 'OrderDate DESC, OrderID',
    @SqlWhere = 'CustomerID = ''ALFKI''',
    @PageSize = 20,
    @PageIndex = 3,
    @TotalPage = @tp OUTPUT;
SELECT @tp AS TotalPages;

遷移提示

SQL Server 2012+ 可直接使用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY 語法，更簡潔標準；本過程適用於 2005/2008/R2 環境。