問題情境
DECLARE @tssj NVARCHAR(100);
DECLARE @Sql_Sql NVARCHAR(100);
SET @Sql_Sql = N'SELECT @tssj = jpk FROM b WHERE bzmc = ''中國''';
EXEC @Sql_Sql; -- 錯誤：必須宣告標量變數 @tssj
EXEC 直接執行字串時，內部變數 @tssj 屬於動態 SQL 範圍，外部變數不可見，故報錯。
正確做法：sp_executesql 參數化
DECLARE @tssj NVARCHAR(100);
DECLARE @Sql_Sql NVARCHAR(100);
SET @Sql_Sql = N'SELECT @tssj = jpk FROM b WHERE bzmc = ''中國''';
EXEC sp_executesql
@Sql_Sql,
N'@tssj NVARCHAR(100) OUTPUT', -- 參數定義：型別 + OUTPUT
@tssj OUTPUT; -- 參數繫結：外部變數 + OUTPUT
SELECT @tssj; -- 成功取得結果
關鍵點解析
| 項目 | 說明 |
|---|
| N'@tssj NVARCHAR(100) OUTPUT' | 參數宣告字串：名稱、型別、方向 (INPUT/OUTPUT)。多參數以逗號分隔。 |
| @tssj OUTPUT | 實際繫結的外部變數，方向需與宣告一致。 |
| 動態 SQL 內變數前綴 @ | 必須與參數宣告名稱完全一致 (不區分大小寫)。 |
| 防 SQL 注入 | 參數化讓資料與程式碼分離，絕不拼接字串。 |
| 查詢計畫重用 | 相同參數化模板可重用計畫，降低編譯開銷。 |
完整範例：輸入/輸出/雙向參數
DECLARE @minPrice MONEY = 100, @maxPrice MONEY = 500, @cnt INT;
DECLARE @sql NVARCHAR(MAX) = N'
SELECT @cnt = COUNT(*)
FROM Products
WHERE UnitPrice BETWEEN @minP AND @maxP;
';
EXEC sp_executesql
@sql,
N'@minP MONEY, @maxP MONEY, @cnt INT OUTPUT',
@minP = @minPrice, @maxP = @maxPrice, @cnt = @cnt OUTPUT;
PRINT @cnt;
常見錯誤與修正
| 錯誤寫法 | 問題 | 修正 |
|---|
| EXEC(@sql) | 無參數化，變數不可見、注入風險 | 改用 sp_executesql |
| 參數定義缺少 OUTPUT | 輸出參數無法回傳值 | 加上 OUTPUT 關鍵字 |
| 型別不匹配 (如 INT vs BIGINT) | 轉換溢位或截斷 | 嚴格對應資料型別 |
| 字串拼接 WHERE 欄位名 | 無法參數化識別碼 | 識別碼需動態拼接 (白名單驗證)，數值用參數 |
最佳實務
- 永遠使用
sp_executesql 取代 EXEC('...')。
- 所有使用者輸入值 全部參數化。
- 動態識別碼 (資料表、欄位、排序欄位) 採白名單驗證後拼接。
- 複雜動態 SQL 考慮改為靜態預存程序 + 選擇性條件 (
WHERE (@param IS NULL OR col = @param))。
