萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 通用分頁存儲過程實現(基於 ROW_NUMBER)

存儲過程完整代碼

CREATE PROCEDURE [dbo].[proc_Opinion_BaseInfo]
    @TableName     VARCHAR(4000),   -- 目標表名
    @PkField       VARCHAR(100),    -- 主鍵/排序字段
    @PageIndex     INT = 1,         -- 頁碼(從1開始)
    @PageSize      INT = 10,        -- 每頁條數
    @SqlWhere      NVARCHAR(4000),  -- WHERE 子句(不含 WHERE 關鍵字)
    @RowCount      BIGINT OUTPUT,   -- 總行數
    @PageCount     BIGINT OUTPUT    -- 總頁數
AS
BEGIN
    SET NOCOUNT ON;

    -- 兼容傳入 '1' 作為無條件查詢
    IF (@SqlWhere = '1') SET @SqlWhere = '1=1';

    DECLARE @sql NVARCHAR(MAX), @start INT, @end INT;

    -- 動態構建分頁查詢:ROW_NUMBER() 開窗函數
    SET @sql = N'SELECT * FROM (
                    SELECT ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@PkField) + ' DESC) AS rowId, *
                    FROM ' + QUOTENAME(@TableName) + '
                    WHERE ' + @SqlWhere + '
                 ) t WHERE rowId BETWEEN @start AND @end';

    SET @start = (@PageIndex - 1) * @PageSize + 1;
    SET @end   = @start + @PageSize - 1;

    -- 使用參數化執行防注入
    EXEC sp_executesql @sql,
                       N'@start INT, @end INT',
                       @start = @start, @end = @end;

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

    -- 計算總頁數
    IF (@RowCount % @PageSize = 0)
        SET @PageCount = @RowCount / @PageSize;
    ELSE
        SET @PageCount = @RowCount / @PageSize + 1;
END

關鍵設計點解析

  • 動態 SQL + 參數化:表名、列名無法參數化,故用 QUOTENAME() 防注入;分頁範圍參數化傳入 sp_executesql,復用計劃、杜絕注入。
  • ROW_NUMBER() 排序:依賴確定性排序列(通常為主鍵),確保分頁穩定不重複不遺漏。
  • 雙查詢分離:數據查詢與 COUNT 分離,避免 COUNT(*) OVER() 在大數據集下的性能陷阱。
  • 輸出參數回傳統計:@RowCount、@PageCount 供前端渲染分頁控件。

適用場景與局限

適用於中小型表、後台管理列表、報表分頁。百萬級以上數據建議改用 Keyset Pagination(基於游標/Seek 法) 或 列存索引 + 延遲關聯 方案,避免深分頁 OFFSET ... FETCH 的掃描開銷。

copyright © 萬盛學電腦網 all rights reserved