SQL Server 預存程序實戰精要：ALTER vs CREATE、輸出參數陷阱、優缺點權衡與動態 SQL

預存程序是資料庫邏輯封裝的核心單元。本文聚焦實戰易踩坑：ALTER/CREATE 差異、輸出參數雙重 OUTPUT、EXEC 作用域隔離、動態 SQL 注入防範、效能計畫快取陷阱。

一、ALTER PROCEDURE vs CREATE PROCEDURE

最佳實務： 部署腳本統一用 IF EXISTS ... DROP ... CREATE 或 CREATE OR ALTER（SQL Server 2016 SP1+），避免權限遺失。

二、輸出參數：雙重 OUTPUT 鐵律

-- 定義
CREATE PROC dbo.GetOrderCount @CustomerID INT, @Total INT OUTPUT
AS
  SELECT @Total = COUNT(*) FROM Orders WHERE CustomerID = @CustomerID;
GO

-- 呼叫：定義與呼叫「雙方」都要加 OUTPUT
DECLARE @Cnt INT;
EXEC dbo.GetOrderCount @CustomerID = 123, @Total = @Cnt OUTPUT;  -- ← 這裡必須加 OUTPUT
PRINT @Cnt;

⚠ 呼叫端漏寫 OUTPUT：不報錯、不回傳值、變數保持 NULL，極難除錯。

三、EXEC 執行陷阱

1. 作用域隔離：EXEC 內的變數外部不可見，外部變數需轉字串拼接才能傳入（或用 sp_executesql 參數化）
2. 安全內容：預設以呼叫者身分執行，而非擁有者（模組簽名/EXECUTE AS 可改變）
3. 同一連線/交易：與呼叫者共享連線與交易巢狀層級
4. 字串拼接順序：動態 SQL 字串必須在 EXEC 前組裝完成，不可在同一批次中邊組裝邊執行
5. 不可用於 UDF 內：純量/表值函數禁止 EXEC

四、動態 SQL 安全模板

CREATE PROC dbo.SearchOrders @Keyword NVARCHAR(100), @Status TINYINT = NULL
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'
        SELECT OrderID, CustomerID, Status, CreatedAt
        FROM dbo.Orders
        WHERE 1=1';
    DECLARE @params NVARCHAR(MAX) = N'@Keyword NVARCHAR(100), @Status TINYINT';

    IF @Keyword IS NOT NULL
        SET @sql += N' AND (CustomerName LIKE @Keyword + N''%'' OR OrderNo LIKE @Keyword + N''%'')';
    IF @Status IS NOT NULL
        SET @sql += N' AND Status = @Status';

    EXEC sp_executesql @sql, @params, @Keyword, @Status;
END;

✅ sp_executesql 參數化 → 計畫重用 + 防注入 + 型別安全。

五、預存程序優缺點權衡

六、參數嗅探應對策略

• OPTION (RECOMPILE)：每次重新編譯，適用高變異度、低頻程序
• OPTION (OPTIMIZE FOR @param = 'typical_value')：指定典型值
• 區域變數賦值：DECLARE @local = @param; ... WHERE col = @local（優化器視為未知值，走平均密度）
• 查詢存儲 啟用後可強制計畫

結語： 預存程序是利器，但需建立「命名規範、參數化動態 SQL、計畫監控、自動化部署」四大工程化配套，才能發揮最大效益。