SQL Server EXEC 執行動態 SQL 與巢狀預存程序：取得傳回值與輸出參數完整技巧

在預存程序中動態建構 SQL 字串並透過 EXEC 或 sp_executesql 執行，或呼叫另一個有輸出參數的預存程序，皆需正確捕獲傳回值。本文系統整理兩大場景的正確寫法、參數化技巧與常見錯誤。

場景一：EXEC 執行動態 SQL 並取得結果集/輸出參數

❌ 錯誤寫法：直接 EXEC 字串變數

DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM T WHERE ID = 1';
EXEC (@SQL);  -- 無法將結果指派給變數，亦無法參數化，易受注入攻擊

✅ 正確寫法：sp_executesql 參數化

DECLARE @SQL NVARCHAR(MAX) = N'SELECT @Total = SUM(Amount) FROM Orders WHERE CustID = @CustID';
DECLARE @CustID INT = 1001;
DECLARE @Total MONEY;

EXEC sp_executesql
    @SQL,
    N'@CustID INT, @Total MONEY OUTPUT',
    @CustID = @CustID,
    @Total = @Total OUTPUT;

SELECT @Total;  -- 取得結果

關鍵優勢

• 計畫重用：參數化查詢可重用執行計畫，降低編譯 CPU。
• 防注入：參數與程式碼分離。
• 型別安全：明確宣告參數型別。

場景二：呼叫帶 OUTPUT 參數的預存程序

被呼叫端範例

CREATE PROC Proc_CalcBonus
    @EmpID INT,
    @Bonus MONEY OUTPUT
AS
BEGIN
    SELECT @Bonus = CASE WHEN Salary > 50000 THEN Salary * 0.1 ELSE Salary * 0.05 END
    FROM Employee WHERE EmpID = @EmpID;
END

呼叫端正確寫法

DECLARE @MyBonus MONEY;
EXEC Proc_CalcBonus @EmpID = 101, @Bonus = @MyBonus OUTPUT;  -- 必須加 OUTPUT
SELECT @MyBonus;

⚠️ 常見錯誤

-- 錯誤：漏寫 OUTPUT
EXEC Proc_CalcBonus @EmpID = 101, @Bonus = @MyBonus;  -- @MyBonus 仍為 NULL

-- 錯誤：變數宣告型別不符
DECLARE @MyBonus INT;  -- 應為 MONEY
EXEC Proc_CalcBonus @EmpID = 101, @Bonus = @MyBonus OUTPUT;

進階：動態決定呼叫哪個預存程序

DECLARE @ProcName SYSNAME = N'Proc_CalcBonus';  -- 可能來自設定表
DECLARE @SQL NVARCHAR(MAX) = N'EXEC ' + QUOTENAME(@ProcName) + N' @EmpID = @EmpID, @Bonus = @Bonus OUTPUT';
DECLARE @EmpID INT = 101, @Bonus MONEY;

EXEC sp_executesql @SQL, N'@EmpID INT, @Bonus MONEY OUTPUT', @EmpID, @Bonus OUTPUT;
SELECT @Bonus;

搭配 QUOTENAME 防止物件名稱注入。

傳回值 vs 輸出參數

最佳實務建議

• ✅ 優先使用 sp_executesql 而非 EXEC (@Str)。
• ✅ 所有動態 SQL 參數化，嚴禁字串拼接使用者輸入。
• ✅ 輸出參數務必在呼叫端加 OUTPUT 關鍵字。
• ✅ 動態物件名稱使用 QUOTENAME 包裹。
• ✅ 錯誤處理包裹在 TRY...CATCH 中，記錄 ERROR_MESSAGE()。