SQL Server 動態 SQL 實戰：sp_executesql 與 EXECUTE 選型、參數化、輸出參數完整指南

動態 SQL 在運行時構建語句，適用於查詢條件、欄位列表、表名等不可預知的場景。但它引入 SQL 注入風險、除錯困難、計畫快取汙染等問題。本文通過實例演示如何安全、高效地使用動態 SQL。

核心場景：將 WHERE 條件傳遞給存儲過程

錯誤嘗試：

DECLARE @WhereClause NVARCHAR(2000) = 'Product=''Computer'''
SELECT * FROM SalesHistory WHERE @WhereClause  -- 語法錯誤，無法直接變數作條件

正確模式：構建完整語句字符串 + sp_executesql

CREATE PROCEDURE usp_GetSalesHistory
    @WhereClause NVARCHAR(2000) = NULL
AS
BEGIN
    DECLARE @SelectStmt NVARCHAR(2000) = 'SELECT TOP 5 * FROM SalesHistory'
    DECLARE @FullStmt NVARCHAR(4000) = @SelectStmt + ISNULL(@WhereClause, '')
    PRINT @FullStmt  -- 除錯用
    EXECUTE sp_executesql @FullStmt
END

sp_executesql vs EXECUTE()：為何首選前者？

進階：獲取動態查詢的輸出參數（如總行數）

CREATE PROCEDURE usp_GetSalesHistory
    @WhereClause NVARCHAR(2000) = NULL,
    @TotalRowsReturned INT OUTPUT
AS
BEGIN
    DECLARE @SelectStmt NVARCHAR(2000) = 'SELECT @TotalRowsReturned = COUNT(*) FROM SalesHistory'
    DECLARE @FullStmt NVARCHAR(4000) = @SelectStmt + ISNULL(@WhereClause, '')
    DECLARE @ParamList NVARCHAR(500) = '@TotalRowsReturned INT OUTPUT'

    PRINT @FullStmt
    EXECUTE sp_executesql @FullStmt, @ParamList, @TotalRowsReturned = @TotalRowsReturned OUTPUT
END

-- 調用示例
DECLARE @Where NVARCHAR(2000) = 'WHERE Product = ''Computer'''
DECLARE @Total INT
EXEC usp_GetSalesHistory @WhereClause = @Where, @TotalRowsReturned = @Total OUTPUT
SELECT @Total

安全與效能黃金法則

1. 永不 直接拼接用戶輸入，必須使用參數化（sp_executesql @stmt, @params, @p1=@v1...）。
2. 動態 SQL 僅用於「結構動態」（表名、欄位名、運算子），「值動態」一律走參數。
3. 對識別符（表/欄位名）使用 QUOTENAME() 包裹。
4. 嚴格控制權限：執行動態 SQL 的模組簽名憑證/非對稱金鑰，授予最小權限。
5. 監控計畫快取：SELECT objtype, usecounts, text FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) WHERE objtype = 'Prepared' OR objtype = 'Adhoc' 觀察 Adhoc 佔比。

調試技巧

• 開發階段保留 PRINT @FullStmt，上線前註釋或改為條件編譯。
• 使用 TRY...CATCH 包裹 sp_executesql 捕獲運行時錯誤並記錄完整語句。

1. 上一篇文章： SQL Server 字符串函數完全手冊：轉換、截取、比較、操作、類型轉換、日期函數一網打盡
2. 下一篇文章： 深入解析 SQL Server 執行語句時的記憶體消耗與 I/O 行為

關於SqlServer

MS SQL 基礎教學：SQL 資料表刪除完整指南 (Enterprise Manager 圖形化與 DROP TABLE T-SQL、相依性檢查)

刪除資料表需謹慎處理相依物件。本文示範兩種方法：1) Ent

SQL Server 視圖實戰：解決跨資料庫複雜查詢關聯

場景痛點 教務系統需查詢單一老師與全院老師工作量，涉及 本資

SQL Server 2008 審計 SELECT 語句：T-SQL 實戰指南

SQL Server 2008 將審計功能提升為一級伺服器物

WebDynpro 命名規範參考指南

WebDynpro 應用開發需遵循統一命名規範，確保程式碼可