共用方式為

使用 sp_executesql

  • 發行項

若要執行字串，建議您使用 sp_executesql 預存程序，而不使用 EXECUTE 陳述式。因為此預存程序支援參數替代，所以 sp_executesql 會比 EXECUTE 更具有多變性；同時由於 sp_executesql 所產生的執行計劃更能讓 SQL Server 2005 重複使用，因此 sp_executesql 也會比 EXECUTE 更有效率。

自成批次

當 sp_executesql 或 EXECUTE 陳述式執行字串時，該字串會以自成批次的方式執行。SQL Server 會將字串中的 Transact-SQL 陳述式編譯到執行計劃中，此編譯計劃與含有 sp_executesql 或 EXECUTE 陳述式之批次的執行計劃不同。下列規則適用於自成批次：

  • sp_executesql 或 EXECUTE 字串中的 Transact-SQL 陳述式必須等到 sp_executesql 或 EXECUTE 陳述式執行完畢，才會編譯到執行計劃中。執行字串後才會剖析它們，或檢查字串是否有誤。字串中參考的名稱也會在執行後才解析。
  • 執行字串中的 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;
GO
EXECUTE sp_executesql N'USE AdventureWorks;'
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 字串。這會造成額外負擔，情況如下：

  • SQL Server 查詢最佳化工具將新 Transact-SQL 字串與現有執行計劃進行比對的能力，會因為字串文字中的參數值一再變動而大打折扣，這種情況在複雜的 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 AdventureWorks.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。
    ms175170.note(zh-tw,SQL.90).gif附註：
    陳述式字串中的物件名稱必須完整，SQL Server 才能重複使用執行計劃。

重複使用執行計劃

若要在舊版的 SQL Server 中重複使用執行計劃，唯一的方法是將 Transact-SQL 陳述式定義為預存程序，然後讓應用程式執行該預存程序。這會造成應用程式的額外管理負擔。使用 sp_executesql 有助於降低此負擔，同時仍能讓 SQL Server 重複使用執行計劃。若要多次執行 Transact-SQL 陳述式，且只有提供給 Transact-SQL 陳述式的參數值不同時，即可使用 sp_executesql 來取代預存程序。由於 Transact-SQL 陳述式本身維持不變，而只有參數值會變更，因此 SQL Server 查詢最佳化工具可能會重複使用第一次執行時所產生的執行計劃。

下例會針對伺服器上的每個資料庫 (四個系統資料庫除外)，建立 DBCC CHECKDB 陳述式並加以執行：

USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN 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
GO
SET NOCOUNT OFF;
GO

若正在執行的 Transact-SQL 陳述式含有繫結參數標記，SQL Server ODBC 驅動程式就會使用 sp_executesql 來實作 SQLExecDirect。這會將 sp_executesql 所提供的好處延伸到使用 ODBC 或 API (透過 ODBC 所定義，例如 RDO) 的所有應用程式。連接到 SQL Server 2005 的現有 ODBC 應用程式會自動獲得較好的效能，無需重寫。一個例外是 sp_executesql 不會和資料執行中 (data-at-execution) 參數配合使用。如需詳細資訊，請參閱＜Using Statement Parameters＞。

SQL Native Client ODBC 提供者也可以使用 sp_executesql 來直接執行具有繫結參數的陳述式。使用 OLE DB 或 ADO 的應用程式可獲得 sp_executesql 帶來的好處，無需重寫。

請參閱

其他資源

SQL 資料隱碼
DECLARE @local\_variable (Transact-SQL)
SELECT (Transact-SQL)
sp_executesql (Transact-SQL)

說明及資訊

取得 SQL Server 2005 協助