SQL Server 通用分頁存儲過程：ROW_NUMBER 動態 SQL 實現與參數化設計

存儲過程完整代碼

CREATE PROCEDURE [dbo].[proc_Page_BaseInfo]
    @TableName NVARCHAR(4000),      -- 表名（建議含架構 dbo.Table）
    @PkField NVARCHAR(100),         -- 排序/分頁鍵（唯一列）
    @PageIndex INT = 1,             -- 1-based 頁碼
    @PageSize INT = 10,             -- 頁大小
    @SqlWhere NVARCHAR(4000) = '1=1', -- WHERE 條件（不含 WHERE）
    @RowCount BIGINT OUTPUT,        -- 總行數
    @PageCount BIGINT OUTPUT        -- 總頁數
AS
BEGIN
    SET NOCOUNT ON;

    -- 參數修正
    IF @PageSize <= 0 SET @PageSize = 10;
    IF @PageIndex <= 0 SET @PageIndex = 1;
    IF @SqlWhere = '1' SET @SqlWhere = '1=1';

    DECLARE @StartRow BIGINT = (@PageIndex - 1) * @PageSize + 1;
    DECLARE @EndRow BIGINT = @StartRow + @PageSize - 1;
    DECLARE @Sql NVARCHAR(MAX);

    -- 分頁查詢（ROW_NUMBER 模式，SQL 2005+）
    SET @Sql = N'
        SELECT * FROM (
            SELECT ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@PkField) + N' DESC) AS RowId, *
            FROM ' + @TableName + N'
            WHERE ' + @SqlWhere + N'
        ) t
        WHERE RowId BETWEEN ' + CAST(@StartRow AS NVARCHAR(20)) + N' AND ' + CAST(@EndRow AS NVARCHAR(20));

    EXEC sp_executesql @Sql;

    -- 統計總行數
    SET @Sql = N'SELECT @RowCount = COUNT(*) FROM ' + @TableName + N' WHERE ' + @SqlWhere;
    EXEC sp_executesql @Sql, N'@RowCount BIGINT OUTPUT', @RowCount OUTPUT;

    -- 計算總頁數
    SET @PageCount = CASE WHEN @RowCount % @PageSize = 0
        THEN @RowCount / @PageSize
        ELSE @RowCount / @PageSize + 1 END;
END;

調用示例

DECLARE @Rows BIGINT, @Pages BIGINT;
EXEC proc_Page_BaseInfo
    @TableName = 'dbo.Orders',
    @PkField = 'OrderID',
    @PageIndex = 3,
    @PageSize = 20,
    @SqlWhere = 'OrderDate >= ''2024-01-01'' AND Status = 1',
    @RowCount = @Rows OUTPUT,
    @PageCount = @Pages OUTPUT;
SELECT @Rows AS TotalRows, @Pages AS TotalPages;

設計要點與改進建議

現代替代：OFFSET-FETCH (SQL 2012+)

DECLARE @Offset INT = (@PageIndex - 1) * @PageSize;
SELECT * FROM dbo.Orders
WHERE OrderDate >= '2024-01-01' AND Status = 1
ORDER BY OrderID DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;

優勢：語法原生、優化器支持更好、無需動態 SQL、計劃穩定。

1. 上一篇文章： 數據庫設計與 SQL Server 實踐的五大核心認知：大數據時代、完整性、安全性、系統觀與工程化
2. 下一篇文章： SQL Server 觸發器完整操作指南：創建、修改、刪除、查看、魔法表與 6 大實戰案例

關於SqlServer

SQL Server 資料類型完整圖鑑：系統與使用者定義類型深度解析

資料類型定義了欄位、預存程序參數與區域變數的資料特徵，決定儲

SSIS 範本化開發：以專案範本加速 ETL 封裝建立與標準化

ETL 專案中，大量封裝共用相同架構：啟動記錄、結束更新狀態

SQL Server 2000 分析服務數據挖掘：決策樹與聚類算法實戰

數據挖掘是 SQL Server 2000 分析服務最令人振

MS SQL Server UPDATE 敘述句完整解析：語法、範例與大量更新策略

11.3.1 UPDATE 語法結構 UPDATE tabl