萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 動態 SQL 執行正確姿勢:EXECUTE vs sp_executesql 深度對比與計畫複用實測

動態 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、生產環境標準作法**

二、 關鍵細節實測驗證

1. Unicode 型別強制要求

EXECUTE(N'SELECT 1');      -- 成功
EXECUTE('SELECT 1');       -- 成功
EXEC sp_executesql N'SELECT 1'; -- 成功
EXEC sp_executesql 'SELECT 1';  -- **失敗** : 必須為 N'' 前綴或 NVARCHAR 變數

2. 字串拼接 vs 參數化

-- 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; -- 正確、安全、可複用計畫

3. 計畫複用實測 (核心優勢)

-- 清理計畫快取 (僅測試環境!)
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 飆升、記憶體膨脹。

三、 安全性:SQL 注入防禦鐵律

  1. 永遠不拼接使用者輸入 進 SQL 字串。
  2. 永遠使用 `sp_executesql` + 參數化** 傳遞使用者輸入值。
  3. 識別碼 (表名/欄位名) 無法參數化** → 嚴格**白名單驗證** (`IF @TableName NOT IN ('T1','T2') THROW...`) 或 `QUOTENAME(@Identifier)` 包裹後拼接 (僅限受信內部代碼)。
  4. 動態 SQL 權限** : 執行上下文為呼叫者 (非擁有者),需確保呼叫者有底層物件權限。或用 `WITH EXECUTE AS OWNER` / 模組簽署 (簽署憑證) 提權。

四、 進階技巧

  • OUTPUT 參數回傳值:
    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;
  • 動態執行 DDL (建索引、截斷表):`sp_executesql` 同樣適用,參數化物件名需 `QUOTENAME`。
  • 嵌套動態 SQL** : 內層 `sp_executesql` 可見外層參數嗎?**不可見**。需層層透傳參數定義字串與值。
  • 除錯技巧** : `PRINT @Sql` 查看最終語句;`SET STATISTICS XML ON` 捕獲實際執行計畫;`Query Store` 追蹤歷史計畫。

五、 總結:動態 SQL 黃金法則

  1. 預設選 `sp_executesql`** —— 參數化、計畫複用、Unicode、安全性四重保障。
  2. 僅在必須用 `VARCHAR` 動態字串、或極簡單臨時 DDL 時用 `EXECUTE`**。
  3. 所有使用者輸入走參數**,識別碼走白名單 + `QUOTENAME`。
  4. 動態 SQL 文本常量化** (參數化後文本固定),利於計畫複用與審計。
  5. 嚴控權限** : `EXECUTE AS CALLER` (預設) + 模組簽署授予精確權限,拒絕 `EXECUTE AS OWNER`/`sa` 濫用。

掌握 `sp_executesql` 參數化模式,是從「寫出能跑的 SQL」進化為「寫出安全、高效、可維護的企業級 T-SQL」的關鍵門檻。

copyright © 萬盛學電腦網 all rights reserved