動態 SQL 中使用變數替換特殊參數（EXEC 技巧）

情境

在 T-SQL 中，某些子句（如資料表名、欄位名、TOP 數值、IN 清單）無法直接使用變數，必須組成動態 SQL 字串再用 EXEC 或 sp_executesql 執行。

基本範例

DECLARE @tempStr VARCHAR(350),
        @week INT = 5,
        @user VARCHAR(50) = 'john''doe';

SELECT @tempStr = 'UPDATE weekcount SET [' + CAST(@week AS VARCHAR) + '] = [' + CAST(@week AS VARCHAR) + '] + 1 WHERE userid = ''' + REPLACE(@user, '''', '''''') + '''';
EXEC(@tempStr);

關鍵點

• REPLACE(@user, '''', '''''')：將單引號轉義為兩個單引號，防止 SQL 注入與語法錯誤。
• 動態 SQL 在獨立批次執行，**外層變數不可直接在動態 SQL 內部引用**（除非用 sp_executesql 參數化）。
• EXEC(@str) 無法直接回傳結果給外層變數（需用 OUTPUT 參數或暫存表）。

進階建議：使用 sp_executesql 參數化

DECLARE @sql NVARCHAR(MAX) = N'UPDATE weekcount SET [' + CAST(@week AS NVARCHAR) + '] = [' + CAST(@week AS NVARCHAR) + '] + 1 WHERE userid = @user';
EXEC sp_executesql @sql, N'@user NVARCHAR(50)', @user = @user;

優點：執行計畫重用、防注入、可直接使用變數、支援 OUTPUT 參數。

常見適用場景

• 動態資料表/欄位名（DDL、跨資料庫查詢）。
• IN 子句傳入逗號分隔字串（需拆分或用 CHARINDEX 技巧）。
• TOP (@n)、ORDER BY @col 等不支援變數的子句。