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

Статья
05/09/2024

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Управляемый экземпляр SQL Azure Конечная точка аналитики аналитики Synapse Analytics Analytics (PDW)SQL Analyticsв 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 ] ]
]

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 пример базы данных, которую можно скачать на домашней странице примеров и проектов сообщества 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 INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition 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 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

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

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

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

Синтаксис

Аргументы

[ @stmt = ] N'statement'

[ @params = ] N'@parameter_name data_type [ ,... n ]'

[ @param1 = ] 'value1'

{ OUT | OUTPUT }

[ ... n ]

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

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

Замечания

Разрешения

Примеры

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

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

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

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

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

Обратная связь

Обратная связь

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

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

Синтаксис

Аргументы

[ @stmt = ] N'statement'

[ @params = ] N'@parameter_name data_type [ ,... n ]'

[ @param1 = ] 'value1'

{ OUT | OUTPUT }

[ ... n ]

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

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

Замечания

Разрешения

Примеры

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

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

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

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

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

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

Обратная связь

Обратная связь

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