SQL Server 存儲過程實現高效分頁查詢

資料分頁查詢的方案眾多，無論前端還是後端都有許多優秀做法，這些方法的共同特點是在實現分頁的同時兼顧網路資源佔用。本文將探討使用 SQL Server 存儲過程實現分頁的方法。

基礎場景分析

在擁有連續 ID 主鍵的資料表中，查詢第 51 至 100 行資料相對簡單：

SELECT * FROM tab WHERE ID BETWEEN 51 AND 100

若 ID 不連續或主鍵非 ID，可採用以下方式：

SELECT TOP 50 * FROM tab WHERE ID NOT IN (SELECT TOP 50 ID FROM tab)

或使用變量控制：

SELECT TOP 50 * FROM tab WHERE ID > @lastpage_endid

配合 SET ROWCOUNT @pagesize 亦可實現。

無主鍵表的分頁挑戰

對於無主鍵且存在大量重複記錄的表，常見做法是臨時表加身份列：

SELECT IDENTITY(INT,1,1) AS ID, * INTO #T FROM tab
SELECT * FROM #T WHERE ID BETWEEN 51 AND 100

此法雖可行但效率低下、資源消耗大。更優方案是為原表添加身份列主鍵：

ALTER TABLE tab ADD ID INT IDENTITY PRIMARY KEY

通用解決方案：系統存儲過程 sp_cursoropen

當無修改表權限時，可利用 SQL Server 提供的 sp_cursoropen 實現通用分頁：

EXEC sp_cursoropen @P1 OUTPUT, @sqlstr
EXEC sp_cursorfetch @P1, 16, @begincol, @pagesize
EXEC sp_cursorclose @P1

• @P1：生成的游標 ID
• @sqlstr：定義游標的 SELECT 字符串
• @begincol：起始行數
• @pagesize：輸出行數

存儲過程實現

以下存儲過程通過傳入表名、頁碼、頁大小實現分頁：

CREATE PROC getpage
    @tablename VARCHAR(255),
    @pagecount INT = 1,
    @pagesize INT = 99999999
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @P1 INT, @sqlstr NVARCHAR(400)
    SET @pagecount = (@pagecount - 1) * @pagesize + 1
    SET @sqlstr = 'SELECT * FROM ' + @tablename
    EXEC sp_cursoropen @P1 OUTPUT, @sqlstr
    EXEC sp_cursorfetch @P1, 16, @pagecount, @pagesize
    EXEC sp_cursorclose @P1
END

調用示例：EXEC getpage 'tab', 10, 100（查詢 tab 表第 10 頁，每頁 100 行）

進階靈活版本

將 SQL 字符串作為參數傳入，支援任意查詢條件：

CREATE PROC getpage
    @sqlstr NVARCHAR(4000),
    @pagecount INT = 1,
    @pagesize INT = 99999999
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @P1 INT
    SET @pagecount = (@pagecount - 1) * @pagesize + 1
    EXEC sp_cursoropen @P1 OUTPUT, @sqlstr
    EXEC sp_cursorfetch @P1, 16, @pagecount, @pagesize
    EXEC sp_cursorclose @P1
END

調用示例：EXEC getpage 'SELECT * FROM tab WHERE 條件', 10, 100

此方法不依賴表結構，適用於無主鍵、複雜查詢等場景，是通用分頁的有效補充。