SQL Server EXEC vs sp_executesql 差異深度解析：參數化、計畫重用、安全性

快速比較表

EXEC 範例與缺點

DECLARE @TableName VARCHAR(50) = 'Orders', @OrderID INT = 10251;
DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE OrderID = ' + CAST(@OrderID AS VARCHAR(10));
EXEC (@sql);  -- 每次 @OrderID 變動產生新計畫

缺點：

• 變數需拼接進字串 → 無法參數化 → 計畫無法重用。
• 無輸出參數支援，需 INSERT #t EXEC(@sql) 繞道。
• 字串拼接易導致 SQL 注入。

sp_executesql 範例與優勢

DECLARE @TableName VARCHAR(50) = 'Orders', @OrderID INT = 10251;
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE OrderID = @OID ORDER BY OrderID DESC';

EXEC sp_executesql
    @stmt = @sql,
    @params = N'@OID INT',
    @OID = @OrderID;  -- 參數化，計畫可重用

計畫重用實測

DBCC FREEPROCCACHE;
-- 執行 3 次，不同 @OrderID
EXEC sp_executesql ... @OID=10251;
EXEC sp_executesql ... @OID=10252;
EXEC sp_executesql ... @OID=10253;

SELECT cacheobjtype, objtype, usecounts, text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE text LIKE '%Orders%' AND text NOT LIKE '%cache%';
-- 結果：usecounts = 3，單一計畫被重用 3 次

輸出參數範例

DECLARE @sql NVARCHAR(100) = N'SET @p = 10', @i INT;
EXEC sp_executesql
    @stmt = @sql,
    @params = N'@p INT OUTPUT',
    @p = @i OUTPUT;
SELECT @i;  -- 10

何時仍需用 EXEC

• 極度動態 DDL (如 CREATE TABLE 欄位清單完全動態)。
• 舊版相容 (SQL 2000 前無 sp_executesql)。
• 簡單測試、一次性腳本。

最佳實務建議

1. 預設使用 sp_executesql，除非有強烈理由用 EXEC。
2. 所有動態 SQL 參數化，避免拼接。
3. 使用 QUOTENAME() 包裹物件名稱，防注入。
4. 動態 SQL 內部若再執行動態 SQL，層層參數化。
5. 定期監控 sys.dm_exec_cached_plans 確認計畫重用率。

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