SQL Server 2005 萬用分頁預存程序：臨時表 + 游標實作主從表分頁

應用場景

主表 OrderInfo、明細表 OrderDetail，需分頁顯示主表並帶出對應明細。

完整實作

-- 1. 建立主表暫存表
CREATE TABLE #temp (
    rownumber BIGINT,
    orderseqno VARCHAR(36),
    goodsname VARCHAR(50),
    companyname VARCHAR(100)
);

-- 2. 建立明細暫存表
CREATE TABLE #detail (
    orderseqno VARCHAR(36),
    detailid UNIQUEIDENTIFIER,
    unitprice DECIMAL(12,2),
    qty INT
);

-- 3. 主表分頁查詢寫入暫存表 (第 10-20 筆)
INSERT INTO #temp
SELECT oo.rownumber, oo.OrderSeqNO, oo.GoodsName, oo.CompanyName
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY oi.CreateDate DESC) AS rownumber,
           oi.OrderSeqNO, oi.GoodsName, ci.CompanyName
    FROM OrderInfo oi
    INNER JOIN CompanyInfo ci ON oi.CompanyID = ci.CompanyID
    WHERE oi.CreateDate < GETDATE()
) AS oo
WHERE rownumber BETWEEN 10 AND 20;

-- 4. 宣告游標遍歷主表暫存表
DECLARE @temp_cursor CURSOR;
SET @temp_cursor = CURSOR FOR SELECT orderseqno, goodsname FROM #temp;

DECLARE @orderseqno VARCHAR(36), @goodsname VARCHAR(50);

OPEN @temp_cursor;
FETCH NEXT FROM @temp_cursor INTO @orderseqno, @goodsname;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 5. 查詢明細寫入明細暫存表
    INSERT INTO #detail
    SELECT od.OrderSeqNO, od.OrderDetailID, od.UnitPrice, od.Qty
    FROM OrderDetail od
    WHERE od.OrderSeqNO = @orderseqno;

    FETCH NEXT FROM @temp_cursor INTO @orderseqno, @goodsname;
END

CLOSE @temp_cursor;
DEALLOCATE @temp_cursor;

-- 6. 輸出結果
SELECT * FROM #temp;
SELECT * FROM #detail;

-- 7. 清理
DROP TABLE #temp;
DROP TABLE #detail;

關鍵點解析

• ROW_NUMBER() OVER (ORDER BY ...) 實作分頁 (SQL 2005 引入)。
• 暫存表 (#temp) 隔離分頁結果，避免重複排序。
• 游標逐筆抓取明細，適合「主表分頁 + 明細完整」需求。
• 若明細筆數固定/較少，可改用 JOIN + ROW_NUMBER() 單查詢完成，效能更佳。

現代寫法 (SQL 2012+)

SELECT o.*, d.*
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY CreateDate DESC) AS rn
    FROM OrderInfo
    WHERE CreateDate < GETDATE()
) o
LEFT JOIN OrderDetail d ON o.OrderSeqNO = d.OrderSeqNO
WHERE o.rn BETWEEN 10 AND 20
ORDER BY o.rn, d.OrderDetailID;

單查詢、無游標、最佳化器可產生更有效計畫。

適用版本

原文標註「僅在 SQL Server 2005 測試成功」，實際上 SQL 2005+ 皆可執行，但建議升級至現代語法。