Хранимая процедура sp_executesql (Transact-SQL)
Применимо к: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
В следующем примере создается и выполняется простая инструкция SELECT
, содержащая внедренный параметр с именем @level
.
-- Uses AdventureWorks2022
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
WHERE EmployeeKey = @level',
N'@level TINYINT',
@level = 109;
См. также
EXECUTE (Transact-SQL)
Системные хранимые процедуры (Transact-SQL)