動態 SQL 執行有兩大入口:EXEC (@sql) 與 sp_executesql。後者支援參數化,能大幅提升計畫重用率與效能,是現代 T-SQL 開發首選。
-- EXEC:字串拼接,無參數化
DECLARE @sql NVARCHAR(MAX) = N'SELECT COUNT(*) FROM Emp WHERE Id = ' + @Id;
EXEC (@sql); -- 每次 @Id 不同 → 不同 SQL 文本 → 重新編譯
-- sp_executesql:參數化
DECLARE @sql NVARCHAR(MAX) = N'SELECT @Cnt = COUNT(*) FROM Emp WHERE Id = @Id';
DECLARE @Cnt INT, @Id VARCHAR(20) = '1';
EXEC sp_executesql @sql,
N'@Cnt INT OUTPUT, @Id VARCHAR(20)',
@Cnt OUTPUT, @Id;
PRINT @Cnt;
| 維度 | EXEC (@sql) | sp_executesql |
|---|---|---|
| 參數化 | 不支援(需字串拼接) | 支援輸入/輸出參數 |
| 計畫重用 | 低(每組參數值產生新計畫) | 高(相同參數化 SQL 共用計畫) |
| 編譯成本 | 重複編譯 | 首次編譯後重用 |
| 注入風險 | 高(拼接易注入) | 低(參數強型別) |
| 型別要求 | VARCHAR/NVARCHAR | 參數清單與 SQL 必須為 NVARCHAR |
| 回傳結果集 | 直接回傳 | 直接回傳 + OUTPUT 參數 |
N'@p1 TYPE1 [OUTPUT], @p2 TYPE2 ...'。@p1=@v1, @p2=@v2)。OUTPUT。高頻呼叫(每秒數百次)的參數化查詢:
EXEC:Plan Cache 暴增(數萬單用計畫),CPU 高、記憶體壓力大。sp_executesql:單一參數化計畫重用,Plan Cache 穩定,CPU 降 70%+。NVARCHAR,VARCHAR 會報錯。OPTION (RECOMPILE) 或 OPTIMIZE FOR。EXEC。參數化動態 SQL 是 SQL Server 高效能、安全、可維護程式碼的基石。