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

Выполняет инструкцию Transact-SQL или пакет инструкций, которые могут выполняться много раз или создаваться динамически. Инструкция Transact-SQL или пакет инструкций могут содержать параметры.

Примечание по безопасности
Компиляция инструкций Transact-SQL во время выполнения открывает путь для атак злоумышленников, например методом SQL injection.

Синтаксические обозначения языка Transact-SQL

Синтаксис

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

Аргументы

• [ @statement = ] statement
  Строка в Юникоде, содержащая инструкцию или пакет Transact-SQL. Аргумент statement должен представлять собой константу в Юникоде или переменную в этом же формате. Более сложные выражения Юникода, например объединение двух строк с помощью оператора +, недопустимы. Символьные константы недопустимы. Если задана константа в Юникоде, она должна начинаться с префикса N. Например, константа Юникода N'sp_who' допустима, а символьная константа 'sp_who' — нет. Размер строки ограничивается только доступной серверу баз данных памятью. В 64-разрядных версиях сервера размер строки ограничен 2 ГБ — максимальным размером типа nvarchar(max).

  Примечание
  Текст аргумента stmt может содержать параметры, называющиеся аналогично именам переменных, например: N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

  Каждый параметр, включенный в аргумент stmt, должен иметь соответствующую запись в списке определений параметров @params и в списке значений параметров.

• [ @params = ] N'@parameter_namedata_type [ ,... n ] '
  Строка, содержащая определения всех параметров, использующихся в тексте аргумента stmt. Строка должна представлять собой константу в Юникоде либо переменную в этом же формате. Каждое определение параметра состоит из имени параметра и типа данных. n — заполнитель, означающий определения дополнительных параметров. Каждый параметр, указанный в аргументе statement, должен быть определен в аргументе @params. Если инструкция Transact-SQL или пакет в stmt не содержат параметров, список @params может отсутствовать. Этот аргумент по умолчанию принимает значение NULL.

• [ @param1 = ] 'value1'
  Значение для первого параметра, определенного в строке параметров. Это значение может быть константой или переменной в Юникоде. Каждому параметру, указанному в stmt, должно соответствовать значение. Если инструкция или пакет инструкций Transact-SQL в stmt не содержат параметров, список значений может отсутствовать.

• [ OUT | OUTPUT ]
  Показывает, что аргумент процедуры является выходным. Аргументы типов text, ntext и image могут быть выходными, если процедура не является процедурой CLR. Выходным параметром с ключевым словом OUTPUT может быть заполнитель курсора, если процедура не является процедурой CRL.

• n
  Заполнитель для значений дополнительных параметров. Значения могут быть только константами и переменными. Значения не могут представлять собой сложные выражения, такие как функции или выражения, построенные с помощью операторов.

Значения кодов возврата

0 (успешное завершение) или ненулевое значение (неуспешное завершение)

Результирующие наборы

Возвращает результирующие наборы всех заданных инструкций SQL.

Замечания

Относительно пакетов инструкций, области имен и контекста базы данных процедура sp_executesql ведет себя аналогично инструкции EXECUTE. Инструкция Transact-SQL или пакет в параметре sp_executesql stmt не компилируется до начала выполнения инструкции sp_executesql. Содержимое 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 EmployeeID, NationalIDNumber, Title, ManagerID
       FROM AdventureWorks.HumanResources.Employee 
       WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;

Выходные параметры также могут быть использованы sp_executesql. В следующем примере название задания получается из таблицы AdventureWorks.HumanResources.Employee и возвращается в выходном параметре @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(Title) 
   FROM AdventureWorks.HumanResources.Employee
   WHERE ManagerID = @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.

• Так как собственно текст инструкции Transact-SQL в строке sp_executesql не меняется между выполнениями, оптимизатор запросов, вероятнее всего, сопоставит инструкцию Transact-SQL во время второго выполнения плану выполнения, сформированному во время первого. Следовательно, компиляция второй инструкции SQL Server необязательна.

• Строка Transact-SQL строится только один раз.

• Целочисленный параметр определен в собственном формате. Приведение к Юникоду не требуется.

Разрешения

Необходимо членство в роли public.

Примеры

А. Выполнение простой инструкции SELECT

В следующем примере создается и выполняется простая инструкция SELECT, содержащая параметр с именем @level.

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

Б. Выполнение динамически построенной строки

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

В. Использование параметра OUTPUT

В следующем примере используется параметр OUTPUT для хранения результирующего набора, созданного инструкцией SELECT в параметре @SQLString.Затем выполняются две инструкции SELECT, при этом в них используется значение параметра OUTPUT.

USE AdventureWorks;
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;

Дополнительные примеры см. в разделе Использование sp_executesql.

См. также

Справочник

EXECUTE (Transact-SQL)

Системные хранимые процедуры (Transact-SQL)

Основные понятия

Атака путем внедрения кода SQL

Пакеты

Построение инструкций в среде выполнения