sp_executesql (Transact-SQL)

發行項
05/09/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範例資料庫，您可以從 Microsoft SQL Server 範例和社群專案首頁下載。

引數

[ @stmt = ] N'statement'

包含 Transact-SQL 語句或批次的 Unicode 字串。 @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';

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

[ @params = ] N'@parameter_namedata_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 。執行語句之前sp_executesql，不會編譯@stmt參數中的 sp_executesqlTransact-SQL 語句或批次。接著，@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 INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition 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 INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @max_title 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。

權限

需要 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 NVARCHAR(500);
DECLARE @OrderMonth 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 來執行字串更有效率。使用時 sp_executesql ，每個月數據表只會產生12個 INSERT 版本的字串。使用 EXECUTE時，每個 INSERT 字串都是唯一的，因為參數值不同。雖然這兩種方法都會產生相同的批次數目，但所產生的sp_executesql字串相似度INSERT使得查詢優化器更有可能重複使用執行計劃。

C. 使用 OUTPUT 參數

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

USE AdventureWorks2022;
GO

DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @SalesOrderNumber NVARCHAR(25);
DECLARE @IntVariable 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_namedata_type [ ,... n ]'

[ @param1 = ] 'value1'

{ OUT |OUTPUT }

[ ...n ]

傳回碼值

結果集

備註

權限

範例

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_namedata_type [ ,... n ]'

[ @param1 = ] 'value1'

{ OUT |OUTPUT }

[ ...n ]

傳回碼值

結果集

備註

權限

範例

A. 執行 SELECT 語句

B. 執行動態建置的字串

C. 使用 OUTPUT 參數

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

D. 執行 SELECT 語句

相關內容

意見反應

意見反應

其他資源