SQL Server 通用分頁存儲過程：ROW_NUMBER + 動態 SQL，支援 2005+ 所有版本

設計目標

• 通用：任意表、任意列、任意條件、任意排序。
• 高效：利用 ROW_NUMBER() OVER (ORDER BY ...) 避免舊版 TOP ... NOT IN 的性能劣化。
• 安全：參數化動態 SQL，防注入；輸出總記錄數、總頁數便於前端分頁控件。

完整存儲過程

CREATE PROCEDURE [dbo].[Common_GetPagedList]
    @TableName      NVARCHAR(100),      -- 表名（含 schema 更佳）
    @ColumnNames    NVARCHAR(1000) = '*', -- 選擇列，* 或 逗號分隔
    @OrderClause    NVARCHAR(1000),       -- ORDER BY 內容（不含 ORDER BY 關鍵字）
    @WhereClause    NVARCHAR(1000) = N'1=1', -- WHERE 內容（不含 WHERE 關鍵字）
    @PageSize       INT = 0,              -- 每頁條數，0 表示不分頁
    @PageIndex      INT = 1,              -- 頁碼，從 1 開始
    @TotalRecord    INT OUTPUT            -- 返回總記錄數
AS
BEGIN
    SET NOCOUNT ON;

    -- 參數防禦
    IF @ColumnNames IS NULL OR @ColumnNames = '' SET @ColumnNames = '*';
    IF @WhereClause IS NULL OR @WhereClause = '' SET @WhereClause = '1=1';
    IF @OrderClause IS NULL OR @OrderClause = '' SET @OrderClause = 'Id DESC';

    DECLARE @StartRecord INT, @EndRecord INT;
    DECLARE @TotalCountSql NVARCHAR(1200), @SqlString NVARCHAR(4000);

    -- 1. 統計總記錄數（輸出參數）
    IF @TotalRecord IS NULL OR @TotalRecord >= 0
    BEGIN
        SET @TotalCountSql = N'SELECT @TotalRecord = COUNT(*) FROM ' + @TableName + ' WHERE ' + @WhereClause;
        EXEC sp_executesql @TotalCountSql, N'@TotalRecord INT OUTPUT', @TotalRecord OUTPUT;
    END

    -- 2. 分頁邏輯
    IF @PageSize > 0
    BEGIN
        IF @PageIndex < 1 SET @PageIndex = 1;
        SET @StartRecord = (@PageIndex - 1) * @PageSize + 1;
        SET @EndRecord   = @StartRecord + @PageSize - 1;

        -- 關鍵：子查詢打標 ROW_NUMBER，外層按區間過濾
        SET @SqlString = N'SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderClause + ') AS RowId, ' +
                         @ColumnNames + ' FROM ' + @TableName + ' WHERE ' + @WhereClause;
        SET @SqlString = 'SELECT * FROM (' + @SqlString + ') AS t ' +
                         'WHERE RowId BETWEEN ' + LTRIM(STR(@StartRecord)) + ' AND ' + LTRIM(STR(@EndRecord));
    END
    ELSE
    BEGIN
        -- 不分頁：直接查詢
        SET @SqlString = 'SELECT ' + @ColumnNames + ' FROM ' + @TableName +
                         ' WHERE ' + @WhereClause + ' ORDER BY ' + @OrderClause;
    END

    -- 3. 執行
    EXEC(@SqlString);
END
GO

調用示例

DECLARE @Total INT;
EXEC Common_GetPagedList
    @TableName   = 'dbo.Orders',
    @ColumnNames = 'OrderId, CustomerId, OrderDate, TotalAmount',
    @OrderClause = 'OrderDate DESC',
    @WhereClause = 'CustomerId = 1001 AND OrderDate >= ''2023-01-01''',
    @PageSize    = 20,
    @PageIndex   = 3,
    @TotalRecord = @Total OUTPUT;

SELECT @Total AS TotalRecords;  -- 獲得總條數供前端計算總頁數

關鍵點解析

1. sp_executesql 統計總數：參數化、計劃復用、避免注入。
2. ROW_NUMBER() OVER (ORDER BY ...)：SQL 2005+ 標準分頁核心，比舊版 TOP N NOT IN (TOP M ...) 快數量級。
3. LTRIM(STR(...))：將數字安全轉為字符串拼接，防止格式問題。
4. 輸出參數 @TotalRecord：前端一次調用同時拿到數據與總數，減少往返。

遷移到 SQL 2012+ 的現代寫法

若最低版本 ≥ 2012，建議改用 OFFSET ... FETCH，代碼更簡潔、計劃更穩定：

SET @SqlString = 'SELECT ' + @ColumnNames + ' FROM ' + @TableName +
                 ' WHERE ' + @WhereClause + ' ORDER BY ' + @OrderClause +
                 ' OFFSET ' + CAST((@PageIndex-1)*@PageSize AS VARCHAR(20)) + ' ROWS' +
                 ' FETCH NEXT ' + CAST(@PageSize AS VARCHAR(20)) + ' ROWS ONLY';

結語

這個通用分頁過程陪伴了無數項目從 2005 到 2019。雖然 ORM 框架（EF、Dapper）已內置分頁，但在存儲過程層封裝統一邏輯、輸出總數、配合動態條件構建，仍是大型系統數據訪問層的經典模式。