您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 通用分頁預存程序 proc_CurrencyPage：ROW_NUMBER() 現代實作

編輯：關於SqlServer

前言

SQL Server 2005 引入 ROW_NUMBER() OVER() 視窗函數，使分頁邏輯大幅簡化。本文提供一個支援任意表、任意欄位、任意條件、任意排序的通用分頁預存程序，並輸出總頁數與總筆數，適合中小型專案快速套用。

預存程序完整程式碼

CREATE PROCEDURE proc_CurrencyPage
(
    @Columns     varchar(max),   -- 回傳欄位，如 'ID, Name, CreateDate'
    @TableName   varchar(max),   -- 表名或聯接查詢 (可含 JOIN)
    @Condition   varchar(max),   -- WHERE 條件，勿加 WHERE 關鍵字
    @OrderBy     varchar(max),   -- ORDER BY 欄位，勿加 ORDER BY 關鍵字
    @PageNum     int,            -- 頁碼 (1-based)
    @PageSize    int,            -- 每頁筆數
    @PageCount   int OUTPUT,     -- 總頁數
    @RecordCount bigint OUTPUT   -- 總筆數
)
AS
BEGIN
    DECLARE @Sql nvarchar(max)

    /* 1. 統計總筆數 */
    SET @Sql = N'SELECT @CountOut = COUNT(*) FROM ' + @TableName + N' WHERE ' + @Condition
    EXEC sp_executesql @Sql, N'@CountOut bigint OUTPUT', @CountOut = @RecordCount OUTPUT

    /* 2. 計算總頁數、修正頁碼 */
    SET @PageCount = @RecordCount / @PageSize
    IF (@RecordCount % @PageSize > 0) SET @PageCount = @PageCount + 1
    IF (@PageNum < 1) SET @PageNum = 1
    IF (@PageNum > @PageCount) SET @PageNum = @PageCount

    /* 3. 計算分頁區間 (ROW_NUMBER 從 1 起算) */
    DECLARE @BRowNum int, @ERowNum int
    SET @BRowNum = (@PageNum - 1) * @PageSize
    SET @ERowNum = @BRowNum + @PageSize

    /* 4. 核心分頁查詢：CTE + ROW_NUMBER */
    SET @Sql = N'
    SELECT * FROM (
        SELECT ' + @Columns + N', ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + N') AS RowNum
        FROM ' + @TableName + N'
        WHERE ' + @Condition + N'
    ) AS TempT
    WHERE RowNum > ' + CAST(@BRowNum AS varchar(10)) + N'
      AND RowNum <= ' + CAST(@ERowNum AS varchar(10)) + N'
    ORDER BY ' + @OrderBy

    EXEC (@Sql)
END
GO

參數說明

參數	型別	說明
@Columns	varchar(max)	需回傳欄位清單，逗號分隔；不可用 * (ROW_NUMBER 需顯式欄位)
@TableName	varchar(max)	單一表名或完整聯接查詢片段 (如 `dbo.Orders o JOIN dbo.Customers c ON o.CustID=c.ID`)
@Condition	varchar(max)	WHERE 條件片段，勿加 WHERE；無條件傳 `1=1`
@OrderBy	varchar(max)	排序欄位片段，勿加 ORDER BY；如 `CreateDate DESC, ID ASC`
@PageNum	int	頁碼，從 1 起算
@PageSize	int	每頁筆數
@PageCount	int OUTPUT	回傳總頁數
@RecordCount	bigint OUTPUT	回傳總筆數

測試呼叫範例

DECLARE 
    @Columns varchar(max) = 'ID, ReportID, ReportCondition',
    @TableName varchar(max) = '[PSYT_TS_ReportSort]',
    @Condition varchar(max) = "ReportSort = 'Z'",
    @OrderBy varchar(max) = 'ID',
    @PageNum int = 2,
    @PageSize int = 20,
    @PageCount int,
    @RecordCount bigint

EXEC proc_CurrencyPage 
    @Columns, @TableName, @Condition, @OrderBy, 
    @PageNum, @PageSize, 
    @PageCount OUTPUT, @RecordCount OUTPUT

SELECT @PageCount AS TotalPages, @RecordCount AS TotalRecords

優缺點分析與適用場景

優點	缺點/限制	適用場景
語法簡潔、可讀性高	動態 SQL 有注入風險，參數需嚴格驗證	中小型專案、報表分頁、後台管理列表
支援任意複雜查詢 (JOIN/子查詢)	深分頁 (大頁碼) 效能遞減，需掃描前 N 列	資料量 < 100 萬、頁碼通常 < 100
自動輸出總筆數/總頁數	COUNT(*) 可能較慢，大表建議快取或估算	需精確分頁資訊的介面
SQL 2005+ 通用，無版本相依	ORDER BY 必須唯一確定排序，否則頁面資料可能重複/遺漏	舊版本相容專案

大數據量深分頁優化方向

Keyset Pagination (Seek 法)：記住上一頁最大排序鍵值，WHERE SortKey > @LastVal ORDER BY SortKey LIMIT @PageSize，無 OFFSET 掃描開銷
覆蓋索引：建立 INCLUDE 覆蓋 @Columns + @OrderBy，避免回表
延遲聯接：分頁先只取主鍵/排序鍵，再 JOIN 明細欄位
SQL 2012+ OFFSET/FETCH：標準語法，計畫穩定，ORDER BY ... OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY