動態 SQL 賦予 T-SQL 極大靈活性,但也帶來 **效能 (計畫複用)**、 **安全 (SQL 注入)**、 **除錯/維護** 三大挑戰。本文深度對比 `EXECUTE()` 與 `sp_executesql` 兩大執行方式,從語法限制、參數化能力、計畫快取複用機制、Unicode 處理、實測數據五個維度,確立 **`sp_executesql` 為首選、 `EXECUTE` 為補充** 的最佳實踐。
| 特性 | EXECUTE (EXEC) | sp_executesql |
|---|---|---|
| 輸入字串型別 | `VARCHAR` / `NVARCHAR` 均可 | **僅 `NVARCHAR` / `NCHAR` / 隱式轉換為 `ntext`** (Unicode) |
| 參數化支援 | **不支援** (需字串拼接,極易注入) | **原生支援輸入/輸出參數** (強型別、長度、預設值) |
| 計畫快取複用 | **極差** : 字串內容異 = 不同計畫 (Ad-hoc 爆炸) | **優秀** : 參數化查詢文本固定,不同參數值**複用同一計畫** |
| 變數作用域 | 動態批次**不可見**外層區域變數 | 動態批次**不可見**外層區域變數 (同 EXEC),**但可透過參數傳遞** |
| 字串長度限制 | 隱式受限於 `VARCHAR(MAX)` / `NVARCHAR(MAX)` | 同 |
| 嵌套執行 | 支援 | 支援 |
| 適用場景 | 動態 DDL (建表/索引)、DDL 含非 Unicode 字串、極簡單臨時腳本 | **所有參數化動態 DML/DDL、生產環境標準作法** |
EXECUTE(N'SELECT 1'); -- 成功
EXECUTE('SELECT 1'); -- 成功
EXEC sp_executesql N'SELECT 1'; -- 成功
EXEC sp_executesql 'SELECT 1'; -- **失敗** : 必須為 N'' 前綴或 NVARCHAR 變數
-- EXECUTE: 必須拼接字串,無法直接引用變數運算結果
DECLARE @GroupName VARCHAR(50) = 'SuperAdmin';
EXECUTE('SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING(@GroupName,1,5) + ''''); -- 語法錯誤!括號內不允許函數呼叫
-- 必須先存變數
DECLARE @Sql VARCHAR(200) = 'SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING(@GroupName,1,5) + '''';
EXECUTE(@Sql); -- 正確但拼接注入風險極高
-- sp_executesql: 參數化傳遞,文本固定
DECLARE @Sql NVARCHAR(200) = N'SELECT * FROM Groups WHERE GroupName = @GroupName';
DECLARE @GroupName NVARCHAR(50) = N'SuperAdmin';
EXEC sp_executesql @Sql, N'@GroupName NVARCHAR(50)', @GroupName; -- 正確、安全、可複用計畫
-- 清理計畫快取 (僅測試環境!)
DBCC FREEPROCCACHE;
-- EXECUTE 模式:執行 3 次不同參數
DECLARE @Sql VARCHAR(200), @GroupName VARCHAR(50);
SET @GroupName = 'SuperAdmin'; SET @Sql = 'SELECT * FROM Groups WHERE GroupName=''' + @GroupName + ''''; EXECUTE(@Sql);
SET @GroupName = 'CommonUser'; SET @Sql = 'SELECT * FROM Groups WHERE GroupName=''' + @GroupName + ''''; EXECUTE(@Sql);
SET @GroupName = 'CommonAdmin'; SET @Sql = 'SELECT * FROM Groups WHERE GroupName=''' + @GroupName + ''''; EXECUTE(@Sql);
-- 查看快取
SELECT cacheobjtype, objtype, usecounts, text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text NOT LIKE '%cache%' AND st.text NOT LIKE '%sys.%';
-- 結果:產生 3 個獨立 Compiled Plan (usecounts=1),文本不同 (參數值內嵌)
-- sp_executesql 模式:執行 3 次不同參數
DBCC FREEPROCCACHE;
DECLARE @Sql NVARCHAR(200) = N'SELECT * FROM Groups WHERE GroupName = @GroupName';
DECLARE @GroupName NVARCHAR(50);
SET @GroupName = N'SuperAdmin'; EXEC sp_executesql @Sql, N'@GroupName NVARCHAR(50)', @GroupName;
SET @GroupName = N'CommonUser'; EXEC sp_executesql @Sql, N'@GroupName NVARCHAR(50)', @GroupName;
SET @GroupName = N'CommonAdmin'; EXEC sp_executesql @Sql, N'@GroupName NVARCHAR(50)', @GroupName;
-- 查看快取
-- 結果:產生 1 個 Compiled Plan (usecounts=3),**計畫複用 3 次**
結論:高頻動態 SQL **必須用 `sp_executesql` 參數化**,避免 Plan Cache 污染、編譯 CPU 飆升、記憶體膨脹。
DECLARE @Sql NVARCHAR(MAX) = N'SELECT @RowCount = COUNT(*) FROM BigTable WHERE Date > @Dt';
DECLARE @RowCount INT, @Dt DATETIME = '2023-01-01';
EXEC sp_executesql @Sql, N'@Dt DATETIME, @RowCount INT OUTPUT', @Dt, @RowCount OUTPUT;
SELECT @RowCount;
掌握 `sp_executesql` 參數化模式,是從「寫出能跑的 SQL」進化為「寫出安全、高效、可維護的企業級 T-SQL」的關鍵門檻。