使用 sp_executesql

若要執行字串,建議您使用 sp_executesql 預存程序,而不使用 EXECUTE 陳述式。因為此預存程序支援參數替代,所以 sp_executesql 會比 EXECUTE 更具有多變性;同時由於 sp_executesql 所產生的執行計畫更能讓 SQL Server 重複使用,因此 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 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 字串。這會造成額外負擔,情況如下:

  • 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 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;
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 的現有 ODBC 應用程式會自動獲得較好的效能,無需重寫。其中一個例外是 sp_executesql 不會和資料執行中 (data-at-execution) 參數搭配使用。如需詳細資訊,請參閱<使用陳述式參數>。

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