SQL 動態 SQL 中使用變數替換物件名稱：EXEC 與 sp_executesql 實作比較

當資料表名、欄位名、資料庫名等識別碼需在執行期由變數決定時，必須使用動態 SQL。本文比較 EXEC (@SQL) 與 sp_executesql 兩種執行方式，說明變數作用域限制、參數化優勢與安全實作模式。

場景：動態決定 UPDATE 目標欄位

DECLARE @Week INT = 3;
DECLARE @User NVARCHAR(50) = N'User001';
DECLARE @SQL NVARCHAR(MAX);

-- 構建動態欄位名：[Week3]
SET @SQL = N'UPDATE WeekCount SET [' + QUOTENAME(N'Week' + CAST(@Week AS NVARCHAR(10))) + N'] = [' 
    + QUOTENAME(N'Week' + CAST(@Week AS NVARCHAR(10))) + N'] + 1 WHERE UserID = @User';

-- 執行並傳入參數
EXEC SP_EXECUTESQL @SQL, N'@User NVARCHAR(50)', @User = @User;

EXEC vs sp_executesql 差異對照

關鍵限制：EXEC 內無法直接存取外部區域變數

DECLARE @Val INT = 100;
EXEC ('PRINT @Val');  -- 錯誤：Must declare the scalar variable "@Val"

解法：改用 SP_EXECUTESQL 透過參數傳遞。

安全實作檢核清單

• ✅ 所有使用者輸入值走參數化，嚴禁字串拼接。
• ✅ 動態物件名稱（資料表、欄位、結構描述）使用 QUOTENAME() 包裹，防止識別碼注入。
• ✅ 動態資料庫名稱：QUOTENAME(@DBName) + N'.dbo.Table'。
• ✅ 限制動態 SQL 權限：僅授與 EXECUTE 權限於封裝預存程序，不直接授與底層物件權限。
• ✅ 記錄動態 SQL 文字至稽核表（含參數值），利於事後追蹤。

進階：動態 ORDER BY / WHERE 條件

DECLARE @SortCol SYSNAME = N'CreateDate';
DECLARE @SortDir VARCHAR(4) = N'DESC';
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Orders ORDER BY ' + QUOTENAME(@SortCol) + N' ' + @SortDir;
-- @SortDir 需驗證僅為 ASC/DESC，不可直接拼接使用者輸入
EXEC SP_EXECUTESQL @SQL;