Создание динамического SQL в хранимой процедуре
Как построить динамический SQL в хранимой процедуре
После прочтения этой статьи вы поймете основы динамического SQL; как создавать инструкции на основе значений переменных и как выполнять эти сконструированные инструкции, используя sp_executesql и EXECUTE () из хранимой процедуры.
Все примеры этого урока основаны на Microsoft SQL Server Management и образцов баз данных, AdventureWorks и WideWorldImporters.
Создание динамического SQL в хранимой процедуре
Большинство SQL, которые мы пишем, записываются непосредственно в хранимую процедуру. Это то, что называется статическим SQL. Он называется так потому, что он не меняется. Как только он записан, его значение задано и не подлежит изменению.
Ниже приведен пример статического SQL:
SELECT JobTitle, Count(BusinessEntityID)
FROM HumanResources.Employee
WHERE Year(BirthDate) = 1970
GROUP BY JobTitle
SELECT JobTitle, Count(BusinessEntityID)
FROM HumanResources.Employee
WHERE Year(BirthDate) = 1971
GROUP BY JobTitle
Обратите внимание, что есть две инструкции, каждая из которых возвращает резюме JobTitles для конкретного года рождения сотрудника. Если мы хотим добавить больше лет рождения, нам нужно добавить больше инструкций. Что нужно сделать, чтобы написать инструкцию единожды и изменять год на лету?
Именно здесь вступает в игру динамический SQL.
Динамический SQL - это SQL, который создается и выполняется во время исполнения. Это звучит сложно, но на самом деле это не так. Вместо того чтобы иметь инструкции, введенные непосредственно в хранимую процедуру, инструкции SQL сначала выстраиваются и определяются в переменных.
Затем в этих переменных выполняется код. Продолжая наш пример, вот тот же код с использованием динамического SQL:
DECLARE @birthYear int = 1970
DECLARE @statement NVARCHAR(4000)
WHILE @birthYear <= 1971
BEGIN
SET @statement = '
SELECT JobTitle, Count(BusinessEntityID)
FROM HumanResources.Employee
WHERE Year(BirthDate) = ' + CAST(@birthYear as NVARCHAR) +
' GROUP BY JobTitle'
EXECUTE sp_executesql @statement
SET @birthYear = @birthYear + 1
END
Динамический SQL выделен жирным шрифтом. Это SQL, который построен для каждого @birthYear. По мере создания SQL он сохраняется в @statement. Затем он выполняется с использованием sp_executesql, который мы объясним ниже.
Введение в sp_executesql
Вы можете использовать sp_executeslq для выполнения транзакционного SQL, хранящегося в переменной. Форма инструкции:
EXECUTE sp_executesql @statement.
Если вам интересно, sp_executesql - это системная хранимая процедура. Системные хранимые процедуры расширяют язык и предоставляют больше возможностей для использования.
Вот простой пример:
DECLARE @statement NVARCHAR(4000)
SET @statement = N'SELECT getdate()'
EXECUTE sp_executesql @statement
Если вы запустите это в окне запроса, вы получите подобный результат:
2018-01-24 18:49:30.143
Теперь, когда вы поняли, как работает sp_executeslq, давайте перейдем к практике. Предположим, вас попросили написать хранимую процедуру, которая возвращает либо среднее значение LineTotal, либо сумму LineTotal по ProductID для продуктов, отправленных в 2011 году.
Ваше руководство хотело бы, чтобы это было написано как хранимая процедура. Хранимая процедура должна принимать один параметр @ReturnAverage. Если это истинно, то вы вернете среднее значение, в противном случае сумму.
Конечно, вы могли бы написать это в виде двух отдельных запросов, как показано в следующей хранимой процедуре, но это было бы не очень весело, поскольку это предполагало бы слишком много ручного ввода и возможных ошибок.
CREATE PROCEDURE uspCalcuateSalesSummaryStatic
@returnAverage bit
AS
IF (@returnAverage = 1)
BEGIN
SELECT SOD.ProductID,
AVG(SOD.LineTotal) as ResultAvg
FROM Sales.SalesOrderDetail SOD
INNER JOIN Sales.SalesOrderHEader SOH
ON SOH.SalesOrderID = SOD.SalesOrderID
WHERE YEAR(SOH.ShipDate) = 2011
GROUP BY SOD.ProductID
END
ELSE
BEGIN
SELECT SOD.ProductID,
SUM(SOD.LineTotal) as ResultSum
FROM Sales.SalesOrderDetail SOD
INNER JOIN Sales.SalesOrderHEader SOH
ON SOH.SalesOrderID = SOD.SalesOrderID
WHERE YEAR(SOH.ShipDate) = 2011
GROUP BY SOD.ProductID
END
Что здесь является слабым местом, так это много дублированного кода, который я выделил жирным шрифтом. Существует не так много уникального кода, но имеющийся выделен курсивом.
При всей этой избыточности у нас есть прекрасная возможность продемонстрировать некоторый динамический SQL. Давайте сделаем это!
CREATE PROCEDURE uspCalcuateSalesSummaryDynamic
@returnAverage bit
AS
DECLARE @statement NVARCHAR(4000),
@function NVARCHAR(10)
IF (@returnAverage = 1) SET @function = 'Avg'
ELSE SET @function = 'Sum'
SET @statement =
'SELECT SOD.ProductID,' +
@function + + '(SOD.LineTotal) as Result' + @function + '
FROM Sales.SalesOrderDetail SOD
INNER JOIN Sales.SalesOrderHEader SOH
ON SOH.SalesOrderID = SOD.SalesOrderID
WHERE YEAR(SOH.ShipDate) = 2011
GROUP BY SOD.ProductID'
EXECUTE sp_executesql @statement
Здесь вместо двух полных версий SQL, один для AVG, другой для SUM, мы создаем запрошенную версию «на лету».
SQL построен и сохраняется в переменной @statement. Эта переменная построена на основе значения параметра @returnAverage. Если установлено значение 1, то @function представляет Среднее; в противном случае - Суммирование.
Отладка динамического SQL
Возможно, вам интересно, как выглядит SQL во время выполнения. Вы можете легко проверить код с помощью отладчика:
Запустите хранимую процедуру с помощью команды запуска отладчика, а затем введите код.
Продолжайте до тех пор, пока вы не прочитаете инструкцию Execute, выделенную ниже.
Использование отладчика
Как только вы достигнете этой инструкции, наведите указатель мыши на @statement, и когда появится подсказка инструмента, выберите текстовый визуализатор.
Отладчик является мощным и заслуживающим понимания. Я бы очень хотел, чтобы вы узнали об этом здесь.
Использование sp_executesql с параметрами
Вы можете использовать sp_executesql для задания параметров в вашей инструкции. Это в конечном итоге делает ваш код более легким для чтения и предоставляет некоторые преимущества оптимизации, поскольку оператор может быть скомпилирован один раз и повторно использован многократно.
Инструкция принимает форму:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
Итак, давайте поясним детали.
- @statement - это SQL, который мы хотим выполнить.
- @parameterDefinition - это строка, содержащая определение всех параметров, указанных в @statement. Перечислен каждый параметр и тип, найденный @statement. Имя и тип разделяются пробелом. Несколько параметров разделяются запятой.
Затем мы устанавливаем значения параметров, задавая параметры и желаемое значение. Параметры перечислены в порядке, определенном в строке @parameterDefinition.
- @ parm1 - это первый параметр, определенный в строке @parameterDefinition. Value - это значение, которое вы хотите установить.
- @ parm2 - это второй параметр, если он определен, как указано в параметре @parameterDefinition.
- и так далее…
Вот простой пример, который добавляет два числа, чтобы попробовать:
DECLARE @statement NVARCHAR(4000)
DECLARE @parameterDefinition NVARCHAR(4000)
SET @statement = N'SELECT @a + @b'
SET @parameterDefinition = N'@a int, @b int'
EXECUTE sp_executesql @statement, @parameterDefinition, @a=10, @b=5
Выделены различные части инструкции:
- @statement (жирный шрифт) - обратите внимание, что он включает в себя 2 параметра: @a и @b. Также обратите внимание, что они не заявлены в TSQL. Скорее, они установлены в определении параметра.
- @parameterDefinition (курсив) - каждый указанный параметр определяется как тип int.
Значения параметров (жирный шрифт+курсив) - здесь мы устанавливаем значение параметра.
Для этого в этом примере у нас есть динамически исполняемый оператор SQL, который добавляет два параметра.
Эти параметры определяются как целые числа. Значение каждого параметра устанавливается в команде sp_executesql.
Пример использования sp_executesql с параметрами
Давайте рассмотрим наш предыдущий пример и расширим его. Вместо того, чтобы жестко кодировать shipDate в запросе, как мы это сделали, давайте введем это как параметр. Это делает запрос более гибким и работает с годами, кроме 2011 года.
Чтобы внести это изменение, мы добавим параметр в нашу хранимую процедуру, а также в динамический запрос. Мы будем использовать команду sp_executesql для вызова динамического запроса с использованием этих параметров.
Обновленная хранимая процедура с изменениями показана ниже.
CREATE PROCEDURE uspCalcuateSalesSummaryDynamic2
@returnAverage bit,
@shipDate int
AS
DECLARE @statement NVARCHAR(4000),
@parameterDefinition NVARCHAR(4000),
@function NVARCHAR(10)
IF (@returnAverage = 1) SET @function = 'Avg'
ELSE SET @function = 'Sum'
SET @parameterDefinition = '@shipDateYear int'
SET @statement =
'SELECT SOD.ProductID,' +
@function + + '(SOD.LineTotal) as Result' + @function + '
FROM Sales.SalesOrderDetail SOD
INNER JOIN Sales.SalesOrderHEader SOH
ON SOH.SalesOrderID = SOD.SalesOrderID
WHERE YEAR(SOH.ShipDate) = @shipDateYear
GROUP BY SOD.ProductID'
EXECUTE sp_executesql @statement, @parameterDefinition, @shipDateYear=@shipDate
Чтобы запустить это, просто вызовите procpackSalesSummaryDynamic2 proc из окна запросов, используя следующую команду:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Если вы это сделаете, вы увидите следующие результаты.
Результаты запроса
Позвольте мне показать вам одно прекрасное упрощение, давайте объединим @shipDateYear и @shipDate в один параметр. Мы исключим @shipDateYear из нашего кода. Это облегчит отслеживание и чтение:
CREATE PROCEDURE uspCalcuateSalesSummaryDynamic2
@returnAverage bit,
<span style="color: #339966;"> @shipDate int
AS
DECLARE @statement NVARCHAR(4000),
@parameterDefinition NVARCHAR(4000),
@function NVARCHAR(10)
IF (@returnAverage = 1) SET @function = 'Avg'
ELSE SET @function = 'Sum'
SET @parameterDefinition = '@shipDate int'
SET @statement =
'SELECT SOD.ProductID,' +
@function + + '(SOD.LineTotal) as Result' + @function + '
FROM Sales.SalesOrderDetail SOD
INNER JOIN Sales.SalesOrderHEader SOH
ON SOH.SalesOrderID = SOD.SalesOrderID
WHERE YEAR(SOH.ShipDate) = @shipDate
GROUP BY SOD.ProductID'
EXECUTE sp_executesql @statement, @parameterDefinition, @shipDate
Обратите внимание, что инструкция EXECUTE намного проще, нет необходимости назначать параметр инструкции SQL @shipDateYear параметру хранимой процедуры parameter @ shipDate.
Это делает инструкцию более компактной и более легкой для чтения. Поток кажется лучше читаемым, поскольку вам не нужно мысленно устанавливать связи между параметрами хранимой процедуры и параметрами SQL.
Запуск динамического SQL с помощью EXECUTE ()
Вы также можете использовать команду EXEC или EXECUTE для запуска динамического SQL. Формат этой команды:
EXECUTE (@statement)
Вот простой пример:
DECLARE @statement NVARCHAR(4000)
SET @statement = N'SELECT getdate()'
EXECUTE (@statement)
Важно заключить @statement в круглые скобки. Если вы этого не сделаете, инструкция EXECUTE принимает @statement, и вместо запуска динамического SQL она решит, что значение переменной является именем хранимой процедуры. Вы получите следующую ошибку:
Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'SELECT getdate()'.
Конечно, это дает отличную подсказку! Если хотите, можете использовать переменные, чтобы указать, какие хранимые процедуры вызывать.
sp_executesql против EXECUTE
Возможно, вам интересно, зачем использовать sp_executesql в сравнении с EXECUTE. Каковы различия между ними?
Вот несколько причин, по которым Microsoft рекомендует использовать sp_executesql для запуска динамического SQL:
- С помощью EXECUTE все параметры могут быть преобразованы из своего исходного типа в Unicode. Это затрудняет способность оптимизатора сопоставлять динамически построенный SQL с уже существующим планом.
- Используя sp_executesql, оптимизатор распознает параметры в динамическом SQL, что упрощает оптимизатор для соответствия планам.
- Легче читать параметризованные запросы, чем читать кучу объединяющего их текста.
- Параметрированные запросы менее подвержены атакам SQL-инъекций.