SQL 預存程序與流程控制：從腳本重構到多表聯查實戰

問題情境：將 T-SQL 批次腳本封裝為預存程序

開發者常面臨將一系列具備流程控制（變數宣告、條件判斷、暫存表操作）的 T-SQL 陳述式，重構為可重複呼叫的預存程序。本文透過實際案例，示範如何正確處理區域變數作用域、簡化參數傳遞，並延伸至多表 LEFT JOIN 查詢技巧。

一、原始腳本分析

原始碼執行邏輯：

1. 宣告變數 @TeacherID、@a~@e
2. 從 Teacher 表查詢五門課程代碼至變數
3. 建立暫存表 classname 並依序插入非 NULL 的課程代碼
4. 巢狀 IF 判斷導致程式碼結構僵化、難以維護

二、首次嘗試錯誤：參數與區域變數混淆

-- 錯誤示範：將區域變數誤宣告為輸入參數
ALTER PROCEDURE Pr_GetClass
    @TeacherID int,
    @a char(50), @b char(50), @c char(50), @d char(50), @e char(50)
AS
BEGIN
    -- 邏輯同原始腳本
END

問題：呼叫端需傳入 6 個參數，實際上只需 @TeacherID；@a~@e 應為程序內部區域變數，而非介面契約。

三、正確寫法：區域變數置於 AS 之後

CREATE PROCEDURE Pr_GetClass
    @TeacherID int
AS
BEGIN
    -- 區域變數宣告
    DECLARE @a char(50), @b char(50), @c char(50), @d char(50), @e char(50);

    -- 查詢課程代碼
    SELECT 
        @a = DRClass1, @b = DRClass2, @c = DRClass3,
        @d = DRClass4, @e = DRClass5
    FROM Teacher
    WHERE TeacherID = @TeacherID;

    -- 重建暫存表
    IF OBJECT_ID('tempdb..#classname') IS NOT NULL
        DROP TABLE #classname;
    CREATE TABLE #classname (classname char(50));

    -- 簡化插入邏輯：使用 COALESCE 或 UNION ALL 替代巢狀 IF
    INSERT INTO #classname (classname) VALUES (@a);
    IF @b IS NOT NULL INSERT INTO #classname (classname) VALUES (@b);
    IF @c IS NOT NULL INSERT INTO #classname (classname) VALUES (@c);
    IF @d IS NOT NULL INSERT INTO #classname (classname) VALUES (@d);
    IF @e IS NOT NULL INSERT INTO #classname (classname) VALUES (@e);

    SELECT * FROM #classname;
END
GO

改進重點：

• 輸入參數僅保留 @TeacherID
• 區域變數移至 BEGIN 區塊內，符合作用域規則
• 使用暫存表 #classname 避免命名衝突
• 展平巢狀 IF，提升可讀性

四、進階優化：集合導向思維替代逐筆判斷

-- 使用 UNION ALL 將非 NULL 值一次性插入
INSERT INTO #classname (classname)
SELECT v.classname FROM (
    SELECT @a UNION ALL SELECT @b UNION ALL SELECT @c
    UNION ALL SELECT @d UNION ALL SELECT @e
) AS v(classname)
WHERE v.classname IS NOT NULL;

此寫法更符合 SQL 集合運算特性，效能優於逐列判斷。

五、多表聯查實戰：KJ、Teacher、Collage 三表關聯

需求：查詢所有 KJ 記錄，帶出對應教師姓名與學院名稱。

資料結構

• KJ：KJID, TeacherID, KJName...
• Teacher：TeacherID, TeacherName, CollageID...
• Collage：CollageID, CollageName

標準 LEFT JOIN 寫法

SELECT 
    T1.KJName,
    T2.TeacherName,
    T3.CollageName
FROM KJ T1
LEFT JOIN Teacher T2 ON T2.TeacherID = T1.TeacherID
LEFT JOIN Collage T3 ON T3.CollageID = T2.CollageID;

關鍵點：

1. 使用 LEFT JOIN 保留 KJ 主表所有列，即使教師或學院資料缺失
2. 別名 T1/T2/T3 簡化欄位引用
3. 聯接條件遵循 FK→PK 方向：T2.TeacherID = T1.TeacherID

六、實務建議總結