SQL Server 通用分頁預存程序：游標法實作與效能考量

預存程序完整碼

CREATE PROCEDURE dbo.GetRecordSet
    @strSql     NVARCHAR(MAX),   -- 查詢 SQL（不含 ORDER BY），如：SELECT * FROM [User]
    @PageIndex  INT,             -- 頁碼（從 1 開始）
    @PageSize   INT              -- 每頁筆數
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @cursorHandle INT,
            @rowCount     INT,
            @pageCount    INT,
            @currentPos   INT;

    -- 1. 開啟游標（KEYSET 型，可捲動、可統計總筆數）
    EXEC sp_cursoropen @cursorHandle OUTPUT, @strSql,
        @scrollopt = 1,  -- KEYSET 游標
        @ccopt     = 1,  -- 唯讀
        @rowcount  = @rowCount OUTPUT;

    -- 2. 計算總頁數與起始位置
    SET @pageCount = CEILING(1.0 * @rowCount / @PageSize);
    SET @currentPos = (@PageIndex - 1) * @PageSize + 1;  -- 游標位置從 1 開始

    -- 3. 回傳總筆數、總頁數（供前端分頁控制項使用）
    SELECT @rowCount AS TotalRows, @pageCount AS TotalPages;

    -- 4. 擷取指定頁資料
    EXEC sp_cursorfetch @cursorHandle, 16, @currentPos, @PageSize;  -- 16 = FETCH_NEXT

    -- 5. 關閉游標釋放資源
    EXEC sp_cursorclose @cursorHandle;

    SET NOCOUNT OFF;
END
GO

呼叫範例

EXEC dbo.GetRecordSet
    @strSql = N'SELECT UserID, UserName, Email, CreateDate FROM dbo.Users WHERE IsActive = 1 ORDER BY CreateDate DESC',
    @PageIndex = 3,
    @PageSize = 20;

核心機制解析

優缺點評析

優點

• **通用性極高**：接受任意 SELECT 字串，無需修改預存程序即可支援新查詢。
• **精確總筆數**：KEYSET 游標開啟時即計算總筆數，前端分頁元件可直接使用。
• **程式碼簡潔**：核心邏輯僅 20 行左右。

缺點與風險

• **游標記憶體壓力**：KEYSET 游標在 tempdb 建立暫存表儲存鍵值集，大結果集會佔用大量 tempdb 空間與 I/O。
• **鎖定爭用**：游標開啟期間對基底表加共享鎖，長時間未關閉（如中間層異常）會阻塞 DDL/DML。
• **無法參數化**：@strSql 直接拼接，存在 SQL 注入風險，**嚴禁**直接串接使用者輸入。
• **效能隨頁碼遞增而下降**：FETCH 到後頁仍需掃描前面所有鍵值。
• **已淘汰技術**：sp_cursor* 為舊版 ODBC/OLE DB 相容介面，現代驅動程式（ODBC 3.8+、OLE DB、ADO.NET）皆建議使用 **OFFSET/FETCH** 或 **ROW_NUMBER()** 分頁。

現代替代方案（SQL Server 2012+）

CREATE PROCEDURE dbo.GetRecordSet_V2
    @Sql        NVARCHAR(MAX),
    @PageIndex  INT,
    @PageSize   INT,
    @TotalRows  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 統計總筆數（可優化：僅 COUNT(1) FROM 原查詢移除 ORDER BY）
    DECLARE @CountSql NVARCHAR(MAX) = N'SELECT @Total = COUNT(1) FROM (' + @Sql + N') AS t';
    EXEC sp_executesql @CountSql, N'@Total INT OUTPUT', @TotalRows OUTPUT;

    -- 2. 分頁查詢（OFFSET/FETCH 需固定 ORDER BY）
    DECLARE @PageSql NVARCHAR(MAX) = @Sql + N' ORDER BY (SELECT 0) OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY';
    EXEC sp_executesql @PageSql,
        N'@Offset INT, @PageSize INT',
        @Offset = (@PageIndex - 1) * @PageSize,
        @PageSize = @PageSize;
END

最佳實務建議

1. 新專案請直接使用 OFFSET/FETCH：標準語法、無游標負擔、執行計畫可重用。
2. 舊系統維護：若必須相容 SQL 2000/2005，游標法可用，但嚴格限制 @strSql 來源（僅內部固定 SQL 片段），並加上 SET ROWCOUNT 保護。
3. 大資料量分頁：採用「鍵值集分頁」或「延遲關聯」：先只查主鍵分頁，再 JOIN 回完整欄位，避免排序大量寬資料列。
4. 快取總筆數：高頻查詢的總筆數可用 Redis/Memcached 快取 1-5 分鐘，減輕資料庫 COUNT 負擔。