sp_executesql (Transact-SQL)sp_executesql (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

執行可重複使用許多次或已動態建立的 Transact-SQLTransact-SQL 陳述式或批次。Executes a Transact-SQLTransact-SQL statement or batch that can be reused many times, or one that has been built dynamically. Transact-SQLTransact-SQL 陳述式或批次可包含內嵌參數。The Transact-SQLTransact-SQL statement or batch can contain embedded parameters.

重要

執行階段編譯的 Transact-SQLTransact-SQL 陳述式可能會讓應用程式面臨惡意攻擊的威脅。Run time-compiled Transact-SQLTransact-SQL statements can expose applications to malicious attacks.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  

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

引數Arguments

[ @stmt =]陳述式[ @stmt= ] statement
是 Unicode 字串,包含 Transact-SQLTransact-SQL陳述式或批次。Is a Unicode string that contains a Transact-SQLTransact-SQL statement or batch. @陳述式必須是 Unicode 常數或 Unicode 變數。@stmt must be either a Unicode constant or a Unicode variable. 不允許使用比較複雜的 Unicode 運算式,如用 + 運算子來串連兩個字串。More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. 不允許使用字元常數。Character constants are not allowed. 如果指定了 Unicode 常數,它必須在前面加上N。比方說,Unicode 常數N'SP_WHO ' 有效,但字元常數 'sp_who' 不是。If a Unicode constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. 字串大小只受到可用資料庫伺服器記憶體的限制。The size of the string is limited only by available database server memory. 在 64 位元伺服器上字串的大小會限制為 2 GB,最大的大小nvarchar (max)On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

注意

@陳述式可以包含參數擁有相同的格式,做為變數的名稱,例如: N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'@stmt can contain parameters having the same form as a variable name, for example: N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

包含在每個參數@陳述式必須有對應的項目中都@params 參數定義清單和參數值清單。Each parameter included in @stmt must have a corresponding entry in both the @params parameter definition list and the parameter values list.

[ @params =] N'@parameter_name * * data_type [,...n ] '[ @params= ] N'@parameter_name**data_type [ ,... n ] '
是一個字串,其中包含之所有參數的已內嵌在定義@陳述式。此字串必須是 Unicode 常數或 Unicode 變數。Is one string that contains the definitions of all parameters that have been embedded in @stmt. The string must be either a Unicode constant or a Unicode variable. 每個參數定義都由參數名稱和資料類型組成。Each parameter definition consists of a parameter name and a data type. n是指出其他參數定義的預留位置。n is a placeholder that indicates additional parameter definitions. 每個指定的參數@stmtmust 定義在@params。Every parameter specified in @stmtmust be defined in @params. 如果 Transact-SQLTransact-SQL陳述式或批次都在@陳述式不包含參數, @params 並非必要。If the Transact-SQLTransact-SQL statement or batch in @stmt does not contain parameters, @params is not required. 這個參數的預設值是 NULL。The default value for this parameter is NULL.

[ @param1 =] 'value1'[ @param1= ] 'value1'
這是參數字串所定義的第一個參數的值。Is a value for the first parameter that is defined in the parameter string. 這個值可以是 Unicode 常數或 Unicode 變數。The value can be a Unicode constant or a Unicode variable. 必須提供每個參數中包含的參數值@陳述式。值不是必要的時機 Transact-SQLTransact-SQL陳述式或批次都在@陳述式沒有任何參數。There must be a parameter value supplied for every parameter included in @stmt. The values are not required when the Transact-SQLTransact-SQL statement or batch in @stmt has no parameters.

[ OUT | OUTPUT ][ OUT | OUTPUT ]
指出這個參數是輸出參數。Indicates that the parameter is an output parameter. 文字ntext,以及映像參數可用來當做輸出參數,除非此程序是 common language runtime (CLR) 程序。text, ntext, and image parameters can be used as OUTPUT parameters, unless the procedure is a common language runtime (CLR) procedure. 除非此程序是一個 CLR 程序,否則使用 OUTPUT 關鍵字的輸出參數可以是資料指標預留位置。An output parameter that uses the OUTPUT keyword can be a cursor placeholder, unless the procedure is a CLR procedure.

nn
這是其他參數值的預留位置。Is a placeholder for the values of additional parameters. 這些值只能是常數或變數。Values can only be constants or variables. 這些值不能是比較複雜的運算式,如函數或利用運算子來建立的運算式。Values cannot be more complex expressions such as functions, or expressions built by using operators.

傳回碼值Return Code Values

0 (成功) 或非零 (失敗)0 (success) or non-zero (failure)

結果集Result Sets

從內建在 SQL 字串內的所有 SQL 陳述式中傳回結果集。Returns the result sets from all the SQL statements built into the SQL string.

備註Remarks

稍早在本主題中 < 語法 > 一節中所述,必須以特定順序輸入 sp_executesql 參數。sp_executesql parameters must be entered in the specific order as described in the "Syntax" section earlier in this topic. 如果未按順序輸入參數,就會出現錯誤訊息。If the parameters are entered out of order, an error message will occur.

關於批次、名稱範圍和資料庫內容,sp_executesql 的行為和 EXECUTE 相同。sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. Transact-SQLTransact-SQL陳述式或批次都在 sp_executesql @stmt 參數就不會等到執行 sp_executesq 陳述式編譯。The Transact-SQLTransact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. 內容@stmt 再編譯和執行為獨立於稱為 sp_executesql 的批次的執行計畫的執行計畫。The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. sp_executesql 批次無法參考稱為 sp_executesql 的批次中所宣告的變數。The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. 稱為 sp_executesql 的批次看不到 sp_executesql 批次中的本機資料指標或變數。Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. 資料庫內容中的變更會持續到 sp_executesql 陳述式結束。Changes in database context last only to the end of the sp_executesql statement.

當陳述式參數值的變更是唯一的變數時,您可以利用 sp_executesql 取代預存程序來重複執行 Transact-SQLTransact-SQL 陳述式。sp_executesql can be used instead of stored procedures to execute a Transact-SQLTransact-SQL statement many times when the change in parameter values to the statement is the only variation. 由於 Transact-SQLTransact-SQL 陳述式本身維持不變,只有參數值改變,因此, [SQL Server]SQL Server 查詢最佳化工具可能會重複使用它針對第一次執行所產生的執行計畫。Because the Transact-SQLTransact-SQL statement itself remains constant and only the parameter values change, the [SQL Server]SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

注意

若要提升效能,請在陳述式字串中使用完整物件名稱。To improve performance use fully qualified object names in the statement string.

如下列範例所示,sp_executesql 支援在 Transact-SQLTransact-SQL 字串之外個別設定參數值。sp_executesql supports the setting of parameter values separately from the Transact-SQLTransact-SQL string as shown in the following example.

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 AdventureWorks2012.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 使用。Output parameters can also be used with sp_executesql. 下列範例會從 AdventureWorks2012.HumanResources.Employee 資料表中擷取一個職稱,並在輸出參數 @max_title 中將它傳回。The following example retrieves a job title from the AdventureWorks2012.HumanResources.Employee table and returns it in the output parameter @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 AdventureWorks2012.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 陳述式來執行字串帶來下列好處:Being able to substitute parameters in sp_executesql offers the following advantages to using the EXECUTE statement to execute a string:

  • 由於在各次執行之間,sp_executesql 字串中 Transact-SQLTransact-SQL 陳述式的實際文字不會改變,因此,查詢最佳化工具可能會符合第二次執行的 Transact-SQLTransact-SQL 陳述式與針對第一次執行所產生的執行計畫。Because the actual text of the Transact-SQLTransact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQLTransact-SQL statement in the second execution with the execution plan generated for the first execution. 因此, [SQL Server]SQL Server 不需要編譯第二個陳述式。Therefore, [SQL Server]SQL Server does not have to compile the second statement.

  • Transact-SQLTransact-SQL 字串只建立一次。The Transact-SQLTransact-SQL string is built only one time.

  • 整數參數用原生格式來指定。The integer parameter is specified in its native format. 不需要轉換成 Unicode。Casting to Unicode is not required.

PermissionsPermissions

需要 public 角色中的成員資格。Requires membership in the public role.

範例Examples

A.A. 執行簡單的 SELECT 陳述式Executing a simple SELECT statement

下列範例會建立和執行包含名稱為 SELECT 的內嵌參數之簡單 @level 陳述式。The following example creates and executes a simple SELECT statement that contains an embedded parameter named @level.

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

B.B. 執行動態建立的字串Executing a dynamically built string

下列範例會示範如何利用 sp_executesql 來執行動態建立的字串。The following example shows using sp_executesql to execute a dynamically built string. 這個範例預存程序會將資料插入一組用來分割年度銷售資料的資料表中。The example stored procedure is used to insert data into a set of tables that are used to partition sales data for a year. 年度每個月份都有一份資料表,格式如下:There is one table for each month of the year that has the following format:

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 陳述式,以便將新的訂單插入正確的資料表中。This sample stored procedure dynamically builds and executes an INSERT statement to insert new orders into the correct table. 這個範例利用訂購日期來建立應該包含資料之資料表的名稱,再將這個名稱納入 INSERT 陳述式中。The example uses the order date to build the name of the table that should contain the data, and then incorporates that name into an INSERT statement.

注意

這是 sp_executesql 的簡單範例。This is a simple example for sp_executesql. 這個範例不包含錯誤檢查,也不包括商務規則的檢查,如保證訂單號碼在資料表之間不重複。The example does not contain error checking and does not include checks for business rules, such as guaranteeing that order numbers are not duplicated between tables.

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 來執行字串有效。Using sp_executesql in this procedure is more efficient than using EXECUTE to execute a string. 當使用 sp_executesql 時,只會產生 12 個 INSERT 字串版本,每月資料表各一個。When sp_executesql is used, there are only 12 versions of the INSERT string that are generated, one for each monthly table. 當使用 EXECUTE 時,每個 INSERT 字串都是唯一的,因為參數值不同。With EXECUTE, each INSERT string is unique because the parameter values are different. 雖然這兩個方法會產生相同的批次數目,但 sp_executesql 所產生之 INSERT 字串的類似性,使得查詢最佳化工具更可能重複使用執行計畫。Although both methods generate the same number of batches, the similarity of the INSERT strings generated by sp_executesql makes it more likely that the query optimizer will reuse execution plans.

C.C. 使用 OUTPUT 參數Using the OUTPUT Parameter

下列範例會使用OUTPUT來儲存所產生的結果集的參數SELECT中的陳述式@SQLString參數。兩個SELECT陳述式接著會執行所使用的值,OUTPUT參數。The following example uses an OUTPUT parameter to store the result set generated by the SELECT statement in the @SQLString parameter.Two SELECT statements are then executed that use the value of the OUTPUT parameter.

USE AdventureWorks2012;  
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 SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

D.D. 執行簡單的 SELECT 陳述式Executing a simple SELECT statement

下列範例會建立和執行包含名稱為 SELECT 的內嵌參數之簡單 @level 陳述式。The following example creates and executes a simple SELECT statement that contains an embedded parameter named @level.

-- Uses AdventureWorks  

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

如需其他範例,請參閱 < sp_executesql & Amp;#40;transact-SQL&#41;For additional examples, see sp_executesql (Transact-SQL).

另請參閱See Also

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