Хранимая процедура sp_executesql (Transact-SQL)sp_executesql (Transact-SQL)

ОБЛАСТЬ ПРИМЕНЕНИЯ:даSQL Server (начиная с 2008)даБаза данных SQL AzureдаХранилище данных SQL AzureдаParallel Data WarehouseAPPLIES 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-SQLTopic 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
Строка в Юникоде, содержащий Transact-SQLTransact-SQL инструкции или пакета.Is a Unicode string that contains a Transact-SQLTransact-SQL statement or batch. @stmt должен быть константой или переменной в Юникоде.@stmt must be either a Unicode constant or a Unicode variable. Более сложные выражения Юникода, например объединение двух строк с помощью оператора +, недопустимы.More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Символьные константы недопустимы.Character constants are not allowed. Если указана константа Юникода, он должен начинаться с префикса N. Например, константа Юникода 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 ГБ, максимальный размер nvarchar(max).On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

Примечание

@stmt может содержать параметры, называющиеся аналогично как имя переменной, например: 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. Строка должна представлять собой константу в Юникоде либо переменную в этом же формате.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 не содержит параметров, @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 =] 'значение1"[ @param1= ] 'value1'
Значение для первого параметра, определенного в строке параметров.Is a value for the first parameter that is defined in the parameter string. Это значение может быть константой или переменной в Юникоде.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 @stmt не компилируются до выполнения инструкции sp_executesql.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:

  • Так как собственно текст инструкции Transact-SQLTransact-SQL в строке sp_executesql не меняется между выполнениями, оптимизатор запросов, вероятнее всего, сопоставит инструкцию 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. Приведение к Юникоду не требуется.Casting to Unicode is not required.

РазрешенияPermissions

Требуется членство в роли public.Requires membership in the public role.

ПримерыExamples

A.A. Выполнение простой инструкции SELECTExecuting 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. Выполнение динамически построенной строки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. И хотя с помощью обоих методов будет создано одинаковое число пакетов, подобие инструкций INSERT, сформированных sp_executesql, увеличивает вероятность того, что оптимизатор запросов повторно использует планы выполнения.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. Использование параметра OUTPUTUsing 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;  

Примеры: Хранилище данных SQL AzureAzure SQL Data Warehouse и Параллельное хранилище данныхParallel Data WarehouseExamples: Хранилище данных SQL AzureAzure SQL Data Warehouse and Параллельное хранилище данныхParallel Data Warehouse

Г.D. Выполнение простой инструкции SELECTExecuting 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)