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

适用于: 是SQL Server(从 2008 开始)是Azure SQL 数据库是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= ] statement[ @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'

包含在每个参数@stmt 必须具有一个对应的条目,在这种@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 ] '
是一个字符串,它包含的定义中嵌入的所有参数@stmt。字符串必须是 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. 每个参数中指定@必须在定义 stmt @params。Every parameter specified in @stmt must be defined in @params. 如果Transact-SQLTransact-SQL语句或批处理在@stmt 不包含参数,@参数不是必需的。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. 必须为包含在每个参数提供参数值@stmt。值不需要Transact-SQLTransact-SQL语句或批处理在@stmt 不具有任何参数。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,和图像参数可以用作输出参数,除非是公共语言运行时 (CLR) 过程。text, ntext, and image parameters can be used as OUTPUT parameters, unless the procedure is a common language runtime (CLR) procedure. 使用 OUTPUT 关键字的输出参数可以为游标占位符,CLR 过程除外。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@直到执行 sp_executesql 语句 stmt 参数时才编译。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 ServerSQL Server 查询优化器可能重复使用首次执行时所生成的执行计划。Because the Transact-SQLTransact-SQL statement itself remains constant and only the parameter values change, the SQL ServerSQL 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 ServerSQL Server 不必编译第二条语句。Therefore, SQL ServerSQL 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.

权限Permissions

要求具有 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 字符串,每个月的表对应 1 个字符串。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 (TRANSACT-SQL)For additional examples, see sp_executesql (Transact-SQL).

请参阅See Also

EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)