SQL Server 動態 SQL 執行最佳實務：EXECUTE vs sp_executesql 深度比較

動態 SQL 依使用者輸入或外部條件組裝陳述式，雖靈活卻帶來效能與安全風險。SQL Server 提供 EXECUTE 與 sp_executesql 兩種執行方式，本文深度比較其語法限制、參數化能力、計畫重用機制與安全性，助您做出正確選擇。

核心差異速覽

語法與行為實測

1. 字串字面值型別

EXECUTE (N'SELECT * FROM Groups');  -- 成功
EXECUTE ('SELECT * FROM Groups');    -- 成功

EXEC sp_executesql N'SELECT * FROM Groups';  -- 成功
EXEC sp_executesql 'SELECT * FROM Groups';   -- 錯誤：必須為 N'...'

2. 字串運算與函數呼叫

DECLARE @GroupName VARCHAR(50) = 'SuperAdmin';

-- EXECUTE：括號內只能是字串常數/變數/串接，不能直接呼叫函數
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);  -- 成功

3. 參數化與計畫重用實證

-- 清除計畫快取
DBCC FREEPROCCACHE;

-- EXECUTE：三次不同參數產生三個 Adhoc 計畫
DECLARE @Sql VARCHAR(200), @GroupName VARCHAR(50);
SET @GroupName = 'SuperAdmin';
SET @Sql = 'SELECT * FROM Groups WHERE GroupName = ' + QUOTENAME(@GroupName, '''');
EXECUTE (@Sql);
-- 重複兩次並更換 @GroupName 值...
-- 查詢 sys.dm_exec_cached_plans 會看到 3 筆 Compiled Plan，usecounts = 1

-- sp_executesql：同一參數化樣板重用單一計畫
DBCC FREEPROCCACHE;
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;
-- 更換參數值再執行兩次...
-- 僅 1 筆 Prepared Plan，usecounts = 3

最佳實務建議

1. 優先使用 sp_executesql：參數化、計畫重用、防注入三大優勢。
2. 動態 SQL 字串務必使用 NVARCHAR，避免隱含轉換失敗。
3. 參數定義需明確指定長度/精度（如 NVARCHAR(50)），否則可能導致計畫不匹配或截斷。
4. 僅當字串為 VARCHAR 且無法轉換時，才退而使用 EXECUTE。
5. 嚴禁直接串接使用者輸入；若必須串接識別碼（資料表/欄位名），請用 QUOTENAME 並驗證白名單。
6. 大量動態 SQL 場景（如動態搜尋、分頁），考慮 OPTION (RECOMPILE) 或參數化存儲過程替代。

安全性補充

sp_executesql 雖大幅降低注入風險，但不等於免疫：若動態 SQL 內部再以字串串接建構子查詢、或將參數當作識別碼執行，仍可能被注入。纏繞原則：資料參數化、識別碼白名單、最小權限執行。

掌握 sp_executesql 參數化模式，是撰寫高效能、安全動態 SQL 的關鍵技能。