SQL Server 預存程序實戰：游標合併兩表前兩列資料至暫存表

示範 T-SQL 預存程序使用 雙游標 同步讀取兩張表的前 2 筆資料，合併插入暫存表並輸出。適用於需逐列對應處理的跨表邏輯。

場景

• 表 1：orders (本季產品銷售)
• 表 2：region (本季欠款區域)
• 需在單一結果集中並排顯示

完整預存程序碼

CREATE PROCEDURE test_merge_top2
AS
BEGIN
    SET NOCOUNT ON;  -- 不回傳影響列數

    DECLARE @col1 VARCHAR(20), @col2 VARCHAR(20);
    DECLARE @idx INT = 1;

    -- 建立暫存表 (# 表存於 tempdb，會話結束自動刪除)
    CREATE TABLE #tmp_tbl (
        colID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        col1 VARCHAR(20),
        col2 VARCHAR(20)
    );

    -- 宣告兩個游標
    DECLARE cur1 CURSOR FOR SELECT TOP 2 customerid FROM orders;
    DECLARE cur2 CURSOR FOR SELECT TOP 2 regiondescription FROM region;

    OPEN cur1;
    OPEN cur2;

    FETCH cur2 INTO @col2;
    FETCH cur1 INTO @col1;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO #tmp_tbl (col1, col2) VALUES (@col1, @col2);
        FETCH NEXT FROM cur1 INTO @col1;
        FETCH NEXT FROM cur2 INTO @col2;
    END

    CLOSE cur1; CLOSE cur2;
    DEALLOCATE cur1; DEALLOCATE cur2;

    SELECT * FROM #tmp_tbl;
    DROP TABLE #tmp_tbl;
END;
GO

關鍵點解析

現代替代寫法 (無游標、集合思維)

-- 使用 ROW_NUMBER() 對齊 (SQL Server 2005+)
WITH cte1 AS (SELECT customerid, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn FROM orders WHERE rn <= 2),
     cte2 AS (SELECT regiondescription, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn FROM region WHERE rn <= 2)
SELECT c1.customerid, c2.regiondescription
FROM cte1 c1 FULL JOIN cte2 c2 ON c1.rn = c2.rn;

• 無游標開銷、可平行化、語意宣告式
• 若兩表筆數不等，FULL JOIN 保留較長者 (NULL 補齊)

結語

游標是 T-SQL 中的逐列處理工具，效能較差、易寫錯 (忘記 DEALLOCATE)、難除錯。現代開發優先用集合運算 (JOIN、窗口函數、APPLY)，僅在必須逐列呼叫外部程序/動態 SQL 時考慮游標。