sp_executesql (Transact-SQL)

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

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

重要

運行時間編譯的 Transact-SQL 語句可能會向惡意攻擊公開應用程式。

Transact-SQL 語法慣例

語法

-- Syntax for SQL Server, Azure SQL Database, Azure Synapse Analytics, Parallel Data Warehouse  
  
sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]  

引數

[ @stmt= ] 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 中定義。 如果 Transact-SQL 陳述式或 @stmt 中的批次不包含參數,則不需要 @params。 這個參數的預設值是 NULL。

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

[ OUT |OUTPUT ]
指出這個參數是輸出參數。 textntextimage 參數可以做為 OUTPUT 參數,除非程式是 Common Language Runtime (CLR) 程式。 除非程式是 CLR 程式,否則使用 OUTPUT 關鍵詞的輸出參數可以是數據指標佔位元元。

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

傳回碼值

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

結果集

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

備註

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

sp_executesql與 EXECUTE 在批次、名稱和資料庫內容方面的行為相同。 執行 sp_executesql 語句之前,不會編譯 sp_executesql @stmt 參數中的 Transact-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 one time.*/  
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內嵌參數。

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

另請參閱

EXECUTE (Transact-SQL)
系統預存程序 (Transact-SQL)