Поделиться через

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

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW)в Microsoft FabricХранилище в Microsoft Fabric

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

Внимание

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис для SQL Server, База данных SQL Azure, Управляемый экземпляр SQL Azure, Azure Synapse Analytics и системы платформы аналитики (PDW).

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

Примеры кода в этой статье используют пример базы данных AdventureWorks2022 или AdventureWorksDW2022, которую можно скачать на домашней странице примерах Microsoft SQL Server и проектах сообщества.

Аргументы

Важный

Аргументы для расширенных хранимых процедур необходимо ввести в определенном порядке, как описано в разделе Синтаксис. Если параметры введены вне порядка, возникает сообщение об ошибке.

[ @stmt = ] N'statement'

Строка Юникода, содержащая инструкцию Или пакет 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 и изображения можно использовать в качестве OUTPUT параметров, если процедура не является процедурой 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 AS INT;
DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);

/* Build the SQL string once */
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 AS INT;

DECLARE @SQLString AS NVARCHAR (500);

DECLARE @ParmDefinition AS NVARCHAR (500);

DECLARE @max_title AS 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 создается только один раз.

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

OPTIMIZED_SP_EXECUTESQL

Область применения: База данных SQL Azure

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

Если пакеты идентичны (исключая различия параметров), OPTIMIZED_SP_EXECUTESQL параметр пытается получить блокировку компиляции в качестве механизма принудительного применения, чтобы гарантировать сериализацию процесса компиляции. Эта блокировка гарантирует, что при одновременном вызове sp_executesql нескольких сеансов эти сеансы будут ждать при попытке получить монопольную блокировку компиляции после запуска первого сеанса процесса компиляции. Первое выполнение компиляции sp_executesql и вставка его скомпилированного плана в кэш планов. Другие сеансы прерывают ожидание блокировки компиляции и повторно используют план после того, как он станет доступным.

OPTIMIZED_SP_EXECUTESQL Без параметра несколько вызовов идентичных пакетов, выполняемых sp_executesql путем параллельной компиляции и размещения собственных копий скомпилированного плана в кэш плана, который заменяет или дублирует записи кэша планов в некоторых случаях.

Примечание.

Прежде чем включить OPTIMIZED_SP_EXECUTESQL конфигурацию с областью базы данных, если включена статистика автоматического обновления, необходимо также включить параметр асинхронной статистики автоматического обновления с параметром конфигурации ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY базы данных с заданной областью. Включение этих двух вариантов может значительно снизить вероятность того, что проблемы с производительностью, связанные с длительным временем компиляции, а также чрезмерными, эксклюзивными блокировками диспетчера блокировки (LCK_M_X) и WAIT_ON_SYNC_STATISTICS_REFRESH ожиданиями.

OPTIMIZED_SP_EXECUTESQL по умолчанию отключен. Чтобы включить OPTIMIZED_SP_EXECUTESQL на уровне базы данных, используйте следующую инструкцию Transact-SQL:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

Разрешения

Необходимо быть членом роли 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 AS NVARCHAR (500);
DECLARE @OrderMonth AS 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 динамической сборки строки, так как позволяет использовать маркеры параметров. Маркеры параметров делают его более вероятным, что ядро СУБД повторно использует созданный план запроса, что помогает избежать дополнительных компиляций запросов. При этом EXECUTEкаждая INSERT строка уникальна, так как значения параметров отличаются и будут добавлены в конец динамически созданной строки. При выполнении запрос не будет параметризован таким образом, чтобы поощрять повторное использование плана и должен быть скомпилирован перед выполнением каждой INSERT инструкции, которая добавит отдельную кэшированную запись запроса в кэш плана.

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

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

USE AdventureWorks2022;
GO

DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);
DECLARE @SalesOrderNumber AS NVARCHAR (25);
DECLARE @IntVariable AS 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

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

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

Связанный контент