sp_executesql (Transact-SQL)

發行項
11/02/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 的 SQL 端點分析 Microsoft Fabric 的倉儲

執行可重複使用多次的 Transact-SQL 語句或批次，或動態建置的語句。 Transact-SQL 語句或批次可以包含內嵌參數。

警告

運行時間編譯的 Transact-SQL 語句可能會向惡意攻擊公開應用程式。使用時 sp_executesql，您應該將查詢參數化。如需詳細資訊，請參閱 SQL 插入。

Transact-SQL 語法慣例

Syntax

SQL Server、Azure SQL 資料庫、Azure SQL 受控執行個體、Azure Synapse Analytics 和 Analytics Platform System （PDW）的語法。

sp_executesql [ @stmt = ] N'statement'
[
    [ , [ @params = ] N'@parameter_name data_type [ { OUT | OUTPUT } ] [ , ...n ]' ]
    [ , [ @param1 = ] 'value1' [ , ...n ] ]
]

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

引數

[ @stmt = ] N'statement'

是一個 Unicode 字串，其中包含 Transact-SQL 陳述式或批次。 @stmt 必須是 Unicode 常數或 Unicode 變數。不允許使用比較複雜的 Unicode 運算式，如用 + 運算子來串連兩個字串。不允許字元常數。 Unicode 常數前面必須加上 N。例如，Unicode 常數 N'sp_who' 有效，但字元常數 'sp_who' 無效。字串大小只受到可用資料庫伺服器記憶體的限制。在 64 位元伺服器中，字串大小限制為 2 GB，大小上限是由 nvarchar(max) 設定。

@stmt可以包含與變數名稱具有相同格式的參數。例如：

N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter';

在 @params 參數定義清單和參數值清單中，@stmt 所包含的每個參數都必須有對應的項目。

[ @params = ] N'@parameter_name data_type [ ， ...n ]'

字串，包含內嵌在 @stmt 中之所有參數的定義。字串必須是 Unicode 常數或 Unicode 變數。每個參數定義都由參數名稱和資料類型組成。 n 是一個預留位置，表示其他參數定義。 @stmt 所指定的每個參數都必須在 @params 中定義。如果 @stmt 中的 Transact-SQL 語句或批次不包含參數，則不需要@params。 此參數的預設值為 NULL。

[ @param1 = ] 'value1'

這是參數字串所定義的第一個參數的值。這個值可以是 Unicode 常數或 Unicode 變數。必須為 @stmt 所含的每個參數提供參數值。當 Transact-SQL 陳述式或 @stmt 中的批次沒有參數時，就不需要這些值。

{ OUT |OUTPUT }

指出這個參數是輸出參數。 除非程式是 Common Language Runtime （CLR）程式，否則 text、 ntext 和 image 參數可以當做 OUTPUT 參數使用。除非程式是 CLR 程式，否則使用關鍵詞的 OUTPUT 輸出參數可以是數據指標佔位元元。

[ ...n ]

這是其他參數值的預留位置。這些值只能是常數或變數。這些值不能是比較複雜的運算式，如函數或利用運算子來建立的運算式。

傳回碼值

0 (成功) 或非零 (失敗)

結果集

從 SQL 字串內建的所有 SQL 語句傳回結果集。

備註

sp_executesql 參數必須以特定順序輸入，如本文稍早的語法一節所述。如果未按順序輸入參數，就會出現錯誤訊息。

sp_executesql 具有與批次、名稱和資料庫內容相同的行為 EXECUTE 。 @stmt 參數中的 sp_executesql Transact-SQL 語句或批次在執行語句之前sp_executesql，不會編譯。接著，@stmt的內容會編譯和執行為與呼叫 sp_executesql之批次的執行計劃分開的執行計劃。批次 sp_executesql 無法參考在呼叫 sp_executesql的批次中宣告的變數。呼叫的批次看不到批次sp_executesql中的sp_executesql本機數據指標或變數。資料庫內容中的變更只會持續到 sp_executesql 陳述式結束。

sp_executesql 當參數值變更為語句的唯一變化時，可以使用而不是預存程式來執行 Transact-SQL 語句多次。由於 Transact-SQL 語句本身會維持不變，而且只有參數值變更，所以 SQL Server 查詢優化器可能會重複使用它為第一次執行所產生的執行計劃。在此案例中，效能相當於預存程式的效能。

注意

若要改善效能，請在語句字串中使用完整物件名稱。

sp_executesql 支援與 Transact-SQL 字串分開的參數值設定，如下列範例所示。

DECLARE @IntVariable AS INT;
DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);

/* Build the SQL string once */
SET @SQLString = N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2022.HumanResources.Employee
       WHERE BusinessEntityID = @BusinessEntityID';

SET @ParmDefinition = N'@BusinessEntityID tinyint';

/* Execute the string with the first parameter value. */
SET @IntVariable = 197;

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @BusinessEntityID = @IntVariable;

/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @BusinessEntityID = @IntVariable;

輸出參數也可以搭配 sp_executesql使用。下列範例會從 HumanResources.Employee 範例資料庫中的 AdventureWorks2022 數據表擷取職稱，並在輸出參數 @max_title中傳回它。

DECLARE @IntVariable AS INT;

DECLARE @SQLString AS NVARCHAR (500);

DECLARE @ParmDefinition AS NVARCHAR (500);

DECLARE @max_title AS VARCHAR (30);

SET @IntVariable = 197;

SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)
   FROM AdventureWorks2022.HumanResources.Employee
   WHERE BusinessEntityID = @level';

SET @ParmDefinition = N'@level TINYINT, @max_titleOUT VARCHAR(30) OUTPUT';

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @level = @IntVariable,
    @max_titleOUT = @max_title OUTPUT;

SELECT @max_title;

能夠替代中的 sp_executesql 參數，比使用 EXECUTE 語句來執行字串具有下列優點：

由於字串中 sp_executesql Transact-SQL 語句的實際文字不會在執行之間變更，因此查詢優化器可能會比對第二次執行中的 Transact-SQL 語句，以及針對第一次執行產生的執行計劃。因此，SQL Server 不需要編譯第二個語句。
Transact-SQL 字串只會建置一次。
整數參數是以原生格式指定。不需要轉換成 Unicode。

OPTIMIZED_SP_EXECUTESQL

適用於：Azure SQL Database

啟用資料庫範圍設定OPTIMIZED_SP_EXECUTESQL時，使用 sp_executesql 所提交的批次編譯行為會與目前採用之預存程式和觸發程序等物件的串行化編譯行為相同。

當批次相同時（不包括任何參數差異），選項 OPTIMIZED_SP_EXECUTESQL 會嘗試取得編譯鎖定做為強制機制，以確保編譯程式已串行化。此鎖定可確保如果多個會話同時叫 sp_executesql 用，這些會話會在嘗試在第一個會話啟動編譯程序之後取得獨佔編譯鎖定時等候。編譯的第 sp_executesql 一次執行，並將其編譯的計劃插入計劃快取中。其他會話會中止等候編譯鎖定，並在計劃可供使用後重複使用。

OPTIMIZED_SP_EXECUTESQL如果沒有選項，透過平行編譯執行sp_executesql之相同批次的多個叫用，並將編譯計劃本身的複本放入計劃快取中，在某些情況下會取代或重複計劃快取專案。

注意

啟用OPTIMIZED_SP_EXECUTESQL資料庫範圍設定之前，如果已啟用自動更新統計數據，則也應該使用 ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY資料庫範圍設定選項來啟用自動更新統計數據異步選項。啟用這兩個選項可大幅降低效能問題與長時間編譯時間相關的機率，以及過度鎖定管理員獨佔鎖定（LCK_M_X）和 WAIT_ON_SYNC_STATISTICS_REFRESH 等候。

OPTIMIZED_SP_EXECUTESQL 默認為 off。若要在資料庫層級啟用 OPTIMIZED_SP_EXECUTESQL ，請使用下列 Transact-SQL 語句：

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

權限

需要 public 角色的成員資格。

範例

A. 執行 SELECT 語句

下列範例會建立並執行 SELECT 語句，其中包含名為的 @level內嵌參數。

EXECUTE sp_executesql
    N'SELECT * FROM AdventureWorks2022.HumanResources.Employee
    WHERE BusinessEntityID = @level',
    N'@level TINYINT',
    @level = 109;

B. 執行動態建置的字串

下列範例示範如何使用 sp_executesql 來執行動態建置的字串。範例預存程式是用來將數據插入一組數據表，用來分割銷售數據一年。每年每個月都有一個數據表，其格式如下：

CREATE TABLE May1998Sales
(
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME NULL CHECK (DATEPART(yy, OrderDate) = 1998),
    OrderMonth INT CHECK (OrderMonth = 5),
    DeliveryDate DATETIME NULL,
    CHECK (DATEPART(mm, OrderDate) = OrderMonth)
);

這個範例預存程式會動態建置並執行 INSERT 語句，以將新訂單插入正確的數據表中。此範例會使用順序日期來建置應該包含數據的數據表名稱，然後將該名稱併入 INSERT 語句中。

注意

這是的基本 sp_executesql範例。此範例不包含錯誤檢查，而且不包含商務規則的檢查，例如保證數據表之間不會重複訂單號碼。

CREATE PROCEDURE InsertSales @PrmOrderID INT,
    @PrmCustomerID INT,
    @PrmOrderDate DATETIME,
    @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString AS NVARCHAR (500);
DECLARE @OrderMonth AS INT;
-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
    /* Build the name of the table. */
    SUBSTRING(DATENAME(mm, @PrmOrderDate), 1, 3) +
    CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4)) + 'Sales' +
    /* Build a VALUES clause. */
    ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
    ' @InsOrdMonth, @InsDelDate)';

/* Set the value to use for the order month because
   functions are not allowed in the sp_executesql parameter
   list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate);

EXEC sp_executesql @InsertString,
    N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
       @InsOrdMonth INT, @InsDelDate DATETIME',
    @PrmOrderID,
    @PrmCustomerID,
    @PrmOrderDate,
    @OrderMonth,
    @PrmDeliveryDate;
GO

在此程式中使用 sp_executesql 比使用 EXECUTE 來執行動態建置字串更有效率，因為它允許使用參數標記。參數標記會使資料庫引擎重複使用產生的查詢計劃的可能性更大，這有助於避免額外的查詢編譯。使用 EXECUTE時，每個 INSERT 字串都是唯一的，因為參數值不同，而且會附加至動態產生的字串結尾。執行時，查詢不會以鼓勵重複使用計劃的方式進行參數化，而且必須在執行每個 INSERT 語句之前進行編譯，這會在計劃快取中新增查詢的個別快取專案。

C. 使用 OUTPUT 參數

下列範例會使用 OUTPUT 參數，將語句所產生的 SELECT 結果集儲存在參數中 @SQLString 。接著會執行兩個 SELECT 語句，以使用參數的值 OUTPUT 。

USE AdventureWorks2022;
GO

DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);
DECLARE @SalesOrderNumber AS NVARCHAR (25);
DECLARE @IntVariable AS INT;

SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID';

SET @ParmDefinition = N'@CustomerID INT,
    @SalesOrderOUT NVARCHAR(25) OUTPUT';

SET @IntVariable = 22276;

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @CustomerID = @IntVariable,
    @SalesOrderOUT = @SalesOrderNumber OUTPUT;

-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;

-- This SELECT statement uses the value of the OUTPUT parameter in
-- the WHERE clause.
SELECT OrderDate,
       TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

D. 執行 SELECT 語句

下列範例會建立並執行 SELECT 語句，其中包含名為的 @level內嵌參數。

EXECUTE sp_executesql
    N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
    WHERE EmployeeKey = @level',
    N'@level TINYINT',
    @level = 109;

共用方式為

sp_executesql (Transact-SQL)

Syntax

引數

[ @stmt = ] N'statement'

[ @params = ] N'@parameter_name data_type [ ， ...n ]'

[ @param1 = ] 'value1'

{ OUT |OUTPUT }

[ ...n ]

傳回碼值

結果集

備註

OPTIMIZED_SP_EXECUTESQL

權限

範例

A. 執行 SELECT 語句

B. 執行動態建置的字串

C. 使用 OUTPUT 參數

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

D. 執行 SELECT 語句

意見反應

其他資源

共用方式為

sp_executesql (Transact-SQL)

Syntax

引數

[ @stmt = ] N'statement'

[ @params = ] N'@parameter_name data_type [ ， ...n ]'

[ @param1 = ] 'value1'

{ OUT |OUTPUT }

[ ...n ]

傳回碼值

結果集

備註

OPTIMIZED_SP_EXECUTESQL

權限

範例

A. 執行 SELECT 語句

B. 執行動態建置的字串

C. 使用 OUTPUT 參數

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

D. 執行 SELECT 語句

相關內容

意見反應

其他資源