sp_executesql 詳細介紹：參數化動態 SQL、效能優勢與使用陷阱

語法結構

sp_executesql
    @stmt NVARCHAR(MAX),          -- 動態 SQL 批次 (必須 N'...')
    @params NVARCHAR(MAX),        -- 參數定義：N'@p1 type1, @p2 type2 OUT, ...'
    @p1 = value1, @p2 = value2 OUT, ...  -- 參數值對應

核心差異：EXEC vs sp_executesql

範例 1：取得計數並輸出

DECLARE @sql NVARCHAR(MAX) = N'SELECT @cnt = COUNT(*) FROM emp WHERE id = @id;';
DECLARE @id VARCHAR(20) = '1';
DECLARE @cnt INT;

EXEC sp_executesql @sql,
                   N'@id VARCHAR(20), @cnt INT OUTPUT',
                   @id = @id, @cnt = @cnt OUTPUT;

PRINT @cnt; -- 正確輸出

範例 2：參數順序不同時的顯式對應

EXEC sp_executesql @sql,
                   N'@cnt INT OUTPUT, @id VARCHAR(20)', -- 定義順序
                   @id = @id, @cnt = @cnt OUTPUT;      -- 呼叫順序可不同，靠名稱對應

效能優勢實測

• 循環執行 10,000 次不同 @id 查詢：
  EXEC：每次編譯 → 總耗時 ~12 秒。
  sp_executesql：單一計畫重用 → 總耗時 ~0.8 秒。
• 計畫快取查詢：SELECT usecounts, cacheobjtype, objtype, text FROM sys.dm_exec_cached_plans ... 可見 Prepared 物件 usecounts 累積。

必要條件與陷阱

1. @stmt 與 @params 必須為 NVARCHAR (N'...')：SQL 2005 SP3+ 強制，VARCHAR 會報錯。
2. 參數名稱區分大小寫：定義與呼叫名稱需完全一致 (含 @)。
3. 資料型別精確對應：VARCHAR(20) vs NVARCHAR(20) 不匹配會隱含轉換或報錯。
4. 輸出參數必須在定義與呼叫均標示 OUTPUT。
5. 動態 SQL 內不可宣告變數：所有變數必須透過參數傳入。

進階：動態 IN 清單

無法直接參數化 IN (@list)，常見替代方案：

• 字串分割函數 + JOIN (SQL 2016+ STRING_SPLIT)。
• Table-Valued Parameter (TVP) 傳遞資料表變數。
• XML/JSON 參數解析。

結語

sp_executesql 是撰寫安全、高效能動態 SQL 的標準作法：**參數化 → 計畫重用 → 防注入**。務必養成 N'...'、型別精確、OUTPUT 雙向標示的習慣。大型動態查詢建議搭配 OPTION (RECOMPILE) 處理參數探查問題。