轉貼自微軟MSDN：建議執行動態SQL時，使用sp_executesql 預存程序而不要使用EXECUTE 語句

http://msdn.microsoft.com/zh-cn/library/ms175170.aspx

 

建議您在執行字串時，使用 sp_executesql 預存程序而不要使用 EXECUTE 語句。由於此預存程序支援參數替換，因此 sp_executesql 比 EXECUTE 的功能更多；由於 SQL Server 更可能重用 sp_executesql 產生的執行計畫，因此 sp_executesql 比 EXECUTE 更有效。

自包含批處理

當 sp_executesql 或 EXECUTE 語句執行字串時，字串將作為它的自包含批處理執行。SQL Server 會將字串中的一個或多個 Transact-SQL 陳述式編譯為獨立於批處理（包含 sp_executesql 或 EXECUTE 語句）執行計畫的執行計畫。下列規則適用於自包含批處理：

• 在執行 sp_executesql 或 EXECUTE 語句之前，不會將 sp_executesql 或 EXECUTE 字串中的 Transact-SQL 陳述式編譯到執行計畫中。執行字串之前，不會分析或檢查其錯誤。執行時才對字串中引用的名稱進行解析。

• 已執行的字串中的 Transact-SQL 陳述式不能訪問包含 sp_executesql 或 EXECUTE 語句的批處理中聲明的任何變數。包含 sp_executesql 或 EXECUTE 語句的批處理不能訪問已執行字串中定義的變數或局部遊標。

• 如果已執行字串包含一個更改資料庫內容相關的 USE 語句，則對資料庫上下文所做的更改將僅持續到 sp_executesql 或 EXECUTE 語句運行結束。

運行下列兩個批處理說明了這些方面：

複製

/*Show not having access to variables from the calling batch. */DECLARE @CharVariable CHAR(3);SET @CharVariable = 'abc';/* sp_executesql fails because @CharVariable has gone out of scope. */EXECUTE sp_executesql N'PRINT @CharVariable';GO/* Show database context resetting after sp_executesql finishes. */USE master;GOEXECUTE sp_executesql N'USE AdventureWorks2008R2;'GO/* This statement fails because the database context   has now returned to master. */SELECT * FROM Sales.Store;GO

替換參數值

sp_executesql 支援替換 Transact-SQL 字串中指定的任何參數值，但 EXECUTE 語句不支援。因此，由 sp_executesql 產生的 Transact-SQL 字串比那些由 EXECUTE 語句產生的字串更加相似。SQL Server 查詢最佳化工具可能將 sp_executesql 的 Transact-SQL 陳述式與以前所執行的語句的執行計畫相匹配，從而節省編譯新的執行計畫的開銷。

使用 EXECUTE 語句，所有參數值都必須轉換為字元或 Unicode，並成為 Transact-SQL 字串的一部分。

如果重複執行語句，則即使只有提供的參數值不同，每次執行時也必鬚生成全新的 Transact-SQL 字串。這樣就會以下列方式產生額外的開銷：

• 不斷更改字串文本（特別是複雜 Transact-SQL 陳述式）中的參數值，會影響 SQL Server 查詢最佳化工具將新的 Transact-SQL 字串與現有執行計畫相匹配的功能。

• 每次執行時均必須重建整個字串。

• 每次執行時必須將參數值（非字元或 Unicode 值）轉換為字元或 Unicode 格式。

sp_executesql 可以單獨使用 Transact-SQL 字串來設定參數值：

複製

DECLARE @IntVariable INT;DECLARE @SQLString NVARCHAR(500);DECLARE @ParmDefinition NVARCHAR(500);/* Build the SQL string one time. */SET @SQLString =     N'SELECT * FROM AdventureWorks2008R2.Sales.Store WHERE SalesPersonID = @SalesID';/* Specify the parameter format one time. */SET @ParmDefinition = N'@SalesID int';/* Execute the string with the first parameter value. */SET @IntVariable = 275;EXECUTE sp_executesql @SQLString, @ParmDefinition,                      @SalesID = @IntVariable;/* Execute the same string with the second parameter value. */SET @IntVariable = 276;EXECUTE sp_executesql @SQLString, @ParmDefinition,                      @SalesID = @IntVariable;

sp_executesql 還另有以下優點：

• 因為 Transact-SQL 陳述式的實際文本在兩次執行之間未更改，所以查詢最佳化工具能夠將第二次執行中的 Transact-SQL 陳述式與第一次執行時產生的執行計畫相匹配。因此，SQL Server 不必編譯第二條語句。

• Transact-SQL 字串只產生一次。

• 整型參數按其本身格式指定。不需要轉換為 Unicode。

  注意
  語句字串中的對象名稱必須完全符合 SQL Server 重用執行計畫的要求。

重用執行計畫

在 SQL Server 早期版本中，唯一可以重用執行計畫的方式是將 Transact-SQL 陳述式定義為預存程序並讓應用程式執行該預存程序。這會產生管理應用程式的額外開銷。使用 sp_executesql 可有助於減少此開銷，並允許 SQL Server 仍重用執行計畫。當提供給 Transact-SQL 陳述式的參數值只有一個變數時，如果要多次執行 Transact-SQL 陳述式，則可以使用 sp_executesql 而不要使用其他預存程序。因為 Transact-SQL 陳述式本身保持不變，僅參數值發生更改，所以 SQL Server 查詢最佳化工具可能會重用第一次執行時產生的執行計畫。

下面的樣本為伺服器上的每個資料庫（四個系統資料庫除外）產生並執行 DBCC CHECKDB 語句。

複製

USE master;GOSET NOCOUNT ON;GODECLARE AllDatabases CURSOR FORSELECT name FROM sys.databases WHERE database_id > 4OPEN AllDatabases;DECLARE @DBNameVar NVARCHAR(128);DECLARE @Statement NVARCHAR(300);FETCH NEXT FROM AllDatabases INTO @DBNameVar;WHILE (@@FETCH_STATUS = 0)BEGIN   PRINT N'CHECKING DATABASE ' + @DBNameVar;   SET @Statement = N'USE ' + @DBNameVar + CHAR(13)      + N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY';   EXEC sp_executesql @Statement;   PRINT CHAR(13) + CHAR(13);   FETCH NEXT FROM AllDatabases INTO @DBNameVar;END;CLOSE AllDatabases;DEALLOCATE AllDatabases;GOSET NOCOUNT OFF;GO

要執行的 Transact-SQL 陳述式包含綁定的參數標記時，SQL Server ODBC 驅動程式使用 sp_executesql 來實施 SQLExecDirect。這樣，可以將 sp_executesql 提供的優勢擴充到使用 ODBC 或通過 ODBC 定義的 API（例如 RDO）的所有應用程式。串連到 SQL Server 的現有 ODBC 應用程式無需重寫就可以自動獲得效能上的改進。但有一個例外，如果是執行時資料參數，則不能使用 sp_executesql。有關詳細資料，請參閱使用語句參數。

SQL Server Native Client ODBC 提供者也使用 sp_executesql 來直接執行帶有綁定參數的語句。使用 OLE DB 或 ADO 的應用程式無需重寫就可以利用 sp_executesql 具有的優點。

請參閱

參考DECLARE @local_variable (Transact-SQL) SELECT (Transact-SQL) sp_executesql (Transact-SQL) 概念SQL 注入