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

Область применения:yesSQL Server (все поддерживаемые версии) YesБаза данных SQL Azure YesУправляемый экземпляр SQL Azure yesAzure Synapse Analytics yesAnalytics Platform System (PDW)

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

Важно!

Скомпилированные во время выполнения инструкции Transact-SQL могут предоставлять приложения вредоносным атакам.

Topic link iconСинтаксические обозначения в 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= ] Заявление
Строка Юникода, содержащая инструкцию или пакет Transact-SQL. @stmt должна быть либо константой Юникода, либо переменной Юникода. Более сложные выражения Юникода, например объединение двух строк с помощью оператора +, недопустимы. Символьные константы недопустимы. Если указана константа Юникода, она должна иметь префикс N. Например, константа Юникода N'sp_who является допустимой, но символьная константа "sp_who" не является. Размер строки ограничивается только доступной серверу баз данных памятью. На 64-разрядных серверах размер строки ограничен 2 ГБ, максимальный размер nvarchar(max).

Примечание

@stmt может содержать параметры с той же формой, что и имя переменной, например: N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

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

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

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

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

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

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

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

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

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

Remarks

sp_executesql параметры должны быть введены в определенном порядке, как описано в разделе "Синтаксис" выше в этом разделе. Если параметры вводятся не в этом порядке, будет выдано сообщение об ошибке.

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

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

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

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

Разрешения

Требуется членство в роли public.

Примеры

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

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

EXECUTE sp_executesql   
          N'SELECT * FROM AdventureWorks2012.HumanResources.Employee   
          WHERE BusinessEntityID = @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 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 Synapse Analytics и Система платформы аналитики (PDW)

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

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

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

См. также:

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