SQL Server 動態 SQL 拼接陷阱：類型轉換與引號處理實戰教訓

問題場景

預存程序中根據變數 @TableName 動態更新不同表：

UPDATE Table1 SET Field1=Value1, Field2=Value2 WHERE Id=Id

錯誤寫法

DECLARE @TableName NVARCHAR(50), @Field1 INT, @Field2 NVARCHAR(50), @Id INT;
DECLARE @Sql NVARCHAR(MAX);
SET @Sql = 'UPDATE ' + @TableName + ' SET Field1=' + @Field1 + ',Field2=' + @Field2 + ' WHERE Id=' + @Id;
EXEC @Sql;

報錯： 無法將 NVARCHAR 轉換為 INT。原因：+ 運算子優先級導致隱性轉換失敗，且字串值未加引號。

正確寫法

SET @Sql = 'UPDATE ' + @TableName + 
    ' SET Field1=' + CAST(@Field1 AS VARCHAR) + 
    ',Field2=''' + @Field2 + '''' + 
    ' WHERE Id=' + CAST(@Id AS VARCHAR);
EXEC (@Sql);

關鍵規則

1. 所有非字串變數必須 CAST/CONVERT 為 VARCHAR 才能拼接
2. 字串值在 SQL 中必須用單引號包裹 → 動態 SQL 中寫成兩個單引號 ''
3. EXEC @Sql → 應為 EXEC (@Sql) 或 Sp_ExecuteSql

進階：Sp_ExecuteSql 參數化（推薦）

DECLARE @Sql NVARCHAR(MAX) = N'UPDATE ' + @TableName + 
    N' SET Field1=@Field1, Field2=@Field2 WHERE Id=@Id';
EXEC Sp_ExecuteSql @Sql,
    N'@Field1 INT, @Field2 NVARCHAR(50), @Id INT',
    @Field1, @Field2, @Id;

優點：避免拼接錯誤、防止 SQL 注入、計劃重用。缺點：資料表名無法參數化（需拼接）。

總結

動態 SQL 拼接核心陷阱：**類型轉換遺漏、字串引號缺失、EXEC 語法錯誤**。養成 CAST(... AS VARCHAR) + '''' 雙引號習慣，或優先使用 Sp_ExecuteSql 參數化。