SQL 動態 SQL 技巧：使用變數替換特殊參數（欄位名、資料表名等）

問題場景

在預存程序或批次中，若需動態指定欄位名稱、資料表名稱或運算子等無法直接參數化的識別碼，必須構建動態 SQL 字串並以 EXEC 或 sp_executesql 執行。

範例：動態更新每週計數欄位

DECLARE @tempStr VARCHAR(350)
DECLARE @week INT = 3
DECLARE @user VARCHAR(50) = 'user''name'  -- 含單引號測試

-- 建構動態 SQL：欄位名 [3] 來自變數
SELECT @tempStr = 'UPDATE weekcount SET [' + CONVERT(VARCHAR, @week) + '] = [' 
    + CONVERT(VARCHAR, @week) + '] + 1 WHERE userid = ''' 
    + REPLACE(@user, '''', '''''') + ''''

EXEC(@tempStr)

關鍵處理細節

1. 單引號轉義：字串值中含單引號時，需用 REPLACE(@val, '''', '''''') 雙倍處理。
2. 識別碼括號：欄位名用 [] 包裹，防止保留字衝突或空白字元。
3. 型別轉換：數值/日期變數串接前必須 CONVERT 或 CAST 為字串。

重要限制

EXEC 執行的動態 SQL 為獨立批次，無法直接存取外層區域變數，也無法將變數值「傳回」外層。若需輸出參數，請改用 sp_executesql 搭配參數定義。

sp_executesql 參數化範例（建議作法）

DECLARE @sql NVARCHAR(MAX) = N'UPDATE weekcount SET [' + @colName + N'] = [' + @colName + N'] + 1 WHERE userid = @uid'
EXEC sp_executesql @sql, N'@uid VARCHAR(50)', @uid = @user

優點：防止 SQL 注入、重複使用執行計畫、支援輸出參數。