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

Статья
05/23/2023

Применимо к:SQL Server Azure SQL Database Azure Sql Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)SQL Endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

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

Внимание

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

Соглашения о синтаксисе 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.

Замечания

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 AdventureWorks2022.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. В следующем примере извлекается название задания из HumanResources.Employee таблицы в AdventureWorks2022 примере базы данных и возвращается в выходном параметре @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 AdventureWorks2022.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.

Примеры

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

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

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

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

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

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

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

USE AdventureWorks2022;  
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)

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

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

См. также

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

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

Синтаксис

Аргументы

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

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

Замечания

Разрешения

Примеры

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

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

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

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

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

См. также

Дополнительные ресурсы

Дополнительные ресурсы