Ms sql sp executesql: | sp_executesql (Transact-SQL) Документация Майкрософт — SQL Server

Dynamic T-SQL и как он может быть полезен / Хабр

В наших проектах нам приходится решать различные задачи. Для решения некоторых из них мы используем dynamic T-Sql (далее по тексту dynamic sql).

Для чего нужен dynamic sql? Каждый решает для себя. В одном из проектов с помощью dynamic sql мы решили задачи построения динамичных отчетов, в других — миграцию данных. Также dynamic sql незаменим в случаях, когда требуется создать/изменить/получить данные или объекты, но значения/названия приходят в качестве параметров. Да, это может показаться абсурдом, но есть и такие задачи.


Дальше мы покажем несколько примеров, как это можно реализовать с помощью dynamic sql.

Выполнить динамическую команду можно несколькими способами:

  • С использование ключевого слова EXEC/EXECUTE;

  • C использование хранимой процедуры sp_executesql


Данные способы отличаются между собой кардинально. На небольшом примере мы постараемся пояснить, чем они отличаются.

Пример кода с EXEC/EXECUTE

DECLARE @sql varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SELECT @sql = ' SELECT CustomerID, ContactName, City ' +
              ' FROM dbo.customers WHERE 1 = 1 '
   SELECT @sql = @sql + ' AND City LIKE ''' + @city + ''''
EXEC (@sql)


Как видно из запроса выше, мы формируем динамическую команду. Если выполнить select @sql, то результат будет следующий:

SELECT CustomerID, ContactName, City FROM customers WHERE City = 'London'


Что же тут плохого? — Запрос отработает, и все будут довольны. Но все же, есть несколько причин, почему так делать не стоит:

  1. При написании команды очень легко ошибиться с количеством «’», т.к. необходимо указывать дополнительные «’», чтобы передать текстовое значение в запрос.

  2. При таком запросе возможны Sql инъекции (SQL Injection). Например, стоит задать значение для @city вроде такого
    set @city = '''DROP TABLE customers--'''

    — и результат будет печальный, т.к. операция select выполнится успешно, как и операция DROP TABLE customers.

  3. Возможна ситуация, когда у вас будет несколько переменных, содержащих коды ваших команд. Что-то типа такой EXEC(@sql1 + @sql2 + @sql3).
    Какие трудности могут возникнуть тут?

    Нужно помнить, что каждая команда отработает отдельно, хотя на первый взгляд, может показаться, что будет выполнена операция конкатенации (@sql1 + @sql2 + @sql3), а затем выполнится общая команда. Также нужно помнить, что накладывается общее ограничение на параметр команды EXEC в 4000 символов.

  4. Происходит неявное приведение типов, т.к. параметры передаются в виде строки.


Что изменится при использовании sp_executesql? – Разработчику проще писать код и его отлаживать, т. к. код будет написан практически как обычный Sql запрос.

Пример кода с sp_executesql

DECLARE @sqlCommand varchar (1000)
DECLARE @columnList varchar (75)
DECLARE @city varchar (75)
SET @city = 'London'
SET @sqlCommand = 'SELECT CustomerID, ContactName, City FROM customers WHERE City =  @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city


Что же изменилось?

  1. В отличие от EXECUTE при использовании sp_executesql, не нужно никакое приведение типов, если мы используем типизированные параметры sp_executesql.

  2. Это решает проблему с дополнительными «’».

  3. Решается проблема безопасности — Sql инъекции (SQL Injection).


Для обоих подходов планы запросов кэшируются, но они отличаются. Эти отличия приведены на рисунке 1 и рисунке 2.

Получение плана запроса

SELECT q. TEXT,cp.usecounts,cp.objtype,p.*, q.*, cp.plan_handle
FROM
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
CROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE
q.TEXT  NOT LIKE '%sys.dm_exec_cached_plans %'
and cp.cacheobjtype = 'Compiled Plan' 
AND q.TEXT  LIKE '%customers%'

План запрос при использование Exec

План запроса при использовании sp_executesql


Также одно из преимуществ использования sp_executesql – это возможность возвращать значение через OUT параметр.

Далее приведем пример, как мы решили одну из проблем в проекте с использованием dynamic sql.

Допустим, у нас есть товар (да неважно, собственно, что это: товар, анкета на должность, персональная анкета). Смысл в том, что каждый объект имеет свой набор свойств (атрибутов), который его характеризует, а их может быть разное количество, и они будут разного типа. Как хранить в БД – это проблема архитектуры.

Для клиента нужен был отчет, который из себя представлял n строк на m столбцов. Где m и был наш набор атрибутов. Отчет собирался по группе объектов или для какого-то объекта из группы. Но смысл остается все тот же: каждый отчет содержит разное количество столбцов для каждой группы объектов.

Поскольку изначально существовала связь между объектами, то решение проблемы выбрали без изменения архитектуры БД. На наш взгляд, решений данной проблемы может быть несколько:

  • Использовать систему отчетности, например, MS Sql Reporting Service. Создать матричный отчет, а в качестве запроса у нас будет «простой» Select. Почему мы так не сделали? В проекте не так много было отчетов, чтобы внедрять туда SSRS.

  • Использовать тот же «простой» select и на серверной стороне уже создавать DataSet необходимой «формы». Да, так задача была решена изначально, когда данных о товарах было очень мало. Как только данных стало достаточно много, то время сбора отчета стало выходит за установленный timeout.

  • Использовать Pivot в sql. Да, отличное решение, когда вы знаете, что у вас только эти атрибуты, и новых не будет. А что делать, когда количество атрибутов часто меняется. И опять же, для каждой группы объектов у нас свой набор атрибутов, мы снова вернемся к созданию процедуры для каждой группы объектов. Не очень удобное решение, не правда ли?

  • А если использовать Pivot, но добавить туда немного dynamic sql? – Да, это решение, которое имеет право на жизнь. Его мы и опишем, как пример использования dynamic sql…

Ссылка на скрипты для создания таблиц и запроса.

В основе отчета будет лежать обычный запрос:

Основной код для отчета

SELECT p.Id as ProductID, 
		p.Name as [Наименование],
		pcp.Name as PropertiesName,
		vpp.Value as Value
	FROM dbo. Products p
	INNER JOIN dbo.PropertiesCategoryOfProducts  pcp ON pcp.CategoryOfProductsId = p.CategoryOfProductsId
	INNER JOIN dbo.ValueOfProductProperty vpp ON vpp.ProductID = p.Id
		and vpp.PropertiesCategoryOfProductsId = pcp.Id
	where p.CategoryOfProductsId = @CategoryOfProductsId

Код запроса для построения отчета

SELECT p.Id as ProductID, 
		p.Name as [Наименование],
		pcp.Name as PropertiesName,
		vpp.Value as Value
	FROM dbo.Products p
	INNER JOIN dbo.PropertiesCategoryOfProducts  pcp ON pcp.CategoryOfProductsId = p.CategoryOfProductsId
	INNER JOIN dbo.ValueOfProductProperty vpp ON vpp.ProductID = p.Id
		and vpp.PropertiesCategoryOfProductsId = pcp.Id
	where p.CategoryOfProductsId = @CategoryOfProductsId
Код запроса для построения отчета
declare @CategoryOfProductsId int = 1
declare @PivotColumnHeaders nvarchar(max)=
REVERSE(STUFF(REVERSE((select '[' + Name + ']' + ',' as 'data()'
 from dbo.PropertiesCategoryOfProducts t
 where t. CategoryOfProductsId = @CategoryOfProductsId
 FOR XML PATH('')
 )),1,1,''))
if(@PivotColumnHeaders>'')
declare @PivotTableSQL nvarchar(max)
BEGIN 
	SET @PivotTableSQL = N'
	SELECT * 
	from (SELECT p.Id as ProductID, 
		p.Name as [Наименование],
		pcp.Name as PropertiesName,
		vpp.Value as Value
	FROM dbo.Products p
	INNER JOIN dbo.PropertiesCategoryOfProducts  pcp ON pcp.CategoryOfProductsId = p.CategoryOfProductsId
	INNER JOIN dbo.ValueOfProductProperty vpp ON vpp.ProductID = p.Id
		and vpp.PropertiesCategoryOfProductsId = pcp.Id
	where p.CategoryOfProductsId = @CategoryOfProductsId
		) as Pivot_Data
	PIVOT (
			MIN(Value)
			FOR PropertiesName IN (
			  ' + @PivotColumnHeaders + '
			)
	) AS PivotTable
	'
	
	EXECUTE sp_executesql  @PivotTableSQL, N'@CategoryOfProductsId int', @CategoryOfProductsId = @CategoryOfProductsId;
END


Давайте рассмотрим, что же мы тут написали:

  • Инициализируем переменную со значением нашей категории товаров — declare @CategoryOfProductsId int = 1

  • Далее нам нужно получить список колонок для нашей категории товаров, но при этом они должны быть заключены в “[]” скобки и перечислены через “,” как этого требует синтаксис функции Pivot —

    Получение списка колонок для категории товаров

    declare @PivotColumnHeaders nvarchar(max)=
REVERSE(STUFF(REVERSE((select '[' + Name + ']' + ',' as 'data()'
 from dbo. PropertiesCategoryOfProducts t
 where t.CategoryOfProductsId = @CategoryOfProductsId
 FOR XML PATH('')
 )),1,1,''))


    Ну а дальше все просто: при выполнении кода список колонок для функции Pivot будет подставлен из @PivotColumnHeaders


    Если выполнить select @PivotTableSQL, то мы получим тот запрос, который без использования dynamic sql нам бы пришлось писать вручную.

    Результатом выполнения данного запроса будет отчет такого вида:

    В заключение стоит еще раз отметить используя dynamic sql, мы можем решать на первый взгляд нетривиальные задачи тривиальными способами. Для этого порой требуется посмотреть на проблему с другой стороны.

SPBDEV Blog — Создание динамического SQL в хранимой процедуре



Tags:
SQL


Sunday, April 8, 2018 4:21:31 PM

Как построить динамический 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>                @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-инъекций.








Введение в хранимую процедуру sp_executesql с примерами

sp_executesql — это встроенная хранимая процедура в SQL Server, которая позволяет выполнять динамически созданные операторы или пакеты SQL. Выполнение динамически созданных пакетов SQL — это метод, который иногда используется для решения различных проблем в программировании SQL. Например, когда мы хотим определить отображаемые столбцы в наших отчетах, эта процедура может быть для нас вариантом решения. Проще говоря, эта процедура принимает динамически созданный пакет SQL и другие параметры, затем выполняет его во время выполнения и, наконец, возвращает результат.

  • Примечание: В примерах этой статьи будет использоваться образец базы данных AdventureWorks.

синтаксис sp_executesql

Следующий код описывает синтаксис:

sp_executesql @stmt, N’@parametername1_datatype, @parametername2_datatype, @parameternameN_datatype’

, @parametername1=’Value1′, @parametername2=’Value2′, @parameternameN=’ValueN’

Параметр @stmt используется для указания динамически сгенерированного оператора SQL или пакета. Тип данных
этот параметр должен быть строкой Unicode, по этой причине мы должны добавить префикс N для прямого использования текста или иметь
использовать переменные с типом данных nvarchar или nchar .

@parameternameN_datatype определяет имя параметра и тип данных, которые использовались в
динамически созданные операторы SQL.

С помощью выражения @parameternameN=’ValueN’ , мы можем присвоить значение
определенные параметры, которые помещаются в оператор SQL. В следующих разделах статьи мы
изучить детали использования с примерами от простого к сложному.

пример sp_executesql

Целью этого примера является извлечение данных из Стол Person , принимающий участие под
та же схема в базе данных AdventureWorks :

Динамически созданный оператор SQL будет присвоен переменной @SqlStatment .
Переменная @ColName используется для указания имен столбцов, которые мы хотим отобразить в результирующем наборе
запрос. Наконец, мы будем фильтровать данные таблицы Person с помощью @PerType.
 параметр. Этот тип данных параметра будет nchar(2) и отфильтрует данные, чьи
Persontype выражения столбца, равные «EM». В качестве последнего шага мы выполним запрос и
добиться результата:

1

2

3

4

5

6

7

DECLARE  @SqlStatment AS NVARCHAR(1000)

DECLARE  @ColNames AS NVARCHAR(100)

    

SET @ColNames = N’Имя, Отчество, Фамилия’;

SET @SqlStatment = ‘SELECT ‘ + @ColNames + ‘ FROM Person.Person WHERE Persontype=@PerType’

    

EXECUTE sp_executesql @SqlStatment , N’@PerType nchar(2)’,@PerType=’EM’

Набор результатов запроса показывает только столбцы FirstName, MiddleName и LastName .
из-за присвоенного значения переменной @ColNames . В то же время мы можем
настройте отображаемые имена столбцов с помощью этого параметра. Например, следующий пример будет отображаться только
Имя столбец:

1

2

3

4

5

6

7

DECLARE  @SqlStatment AS NVARCHAR(1000)

DECLARE  @ColNames AS NVARCHAR(100)

    

SET @ColNames = N’FirstName’;

SET @SqlStatment = ‘SELECT ‘ + @ColNames + ‘ FROM Person. Person WHERE Persontype=@PerType’

    

EXECUTE sp_executesql @SqlStatment , N’@PerType nchar(2)’,@PerType=’EM’

Получение результата sp_executesql с выходным параметром

sp_executesql позволяет вернуть результат выполнения динамически созданного оператора SQL или пакета.
Параметр OUTPUT играет ключевую роль в разрешении этого случая. В этом примере мы будем считать
номер строки таблицы PersonPhone , а затем мы установим возвращаемое значение в переменную с
параметр ВЫХОД . Хитрость этого использования заключается в том, чтобы указать @RowNumber
как параметр OUTPUT , а затем мы присвоили это внутреннее значение параметра
Параметр @Result :

1

2

3

4

5

6

7

8

9

DECLARE  @SqlStatment AS NVARCHAR(1000)

DECLARE  @PhoneIdType AS INT

DECLARE  @Result AS INT

    

SET @SqlStatment=’SELECT @RowNumber= COUNT(PhoneNumber) from Person. PersonPhone WHERE PhoneNumberTypeID=@PhoneType ‘

НАБОР @PhoneIdType=1

EXEC sp_executesql @SqlStatment, N’@PhoneType INT,@RowNumber INT OUTPUT’, @PhoneType=@PhoneIdType, @RowNumber=@Result OUTPUT

    

SELECT @Result AS [TableRowNumber]

sp_executesql против оператора EXEC

Оператор EXEC — это еще один вариант выполнения динамических операторов SQL. Например, мы можем
выполните следующий динамически созданный оператор SQL с помощью оператора EXEC:

1

2

3

4

5

6

объявить @sqlstatment как nvarchar (1000)

объявить @colnames как nvarchar (100)

Объявление @persontype как nvarchar (2) = ’em’

set @colnames = n’firstname, middlename, lastname ‘;

    SET @SqlStatment = ‘SELECT ‘ + @ColNames + ‘ FROM Person.Person WHERE Persontype=  »’ + @Persontype + »»

    EXEC(@SqlStatment)

В предыдущем примере мы выполнили динамически созданный запрос с оператором EXEC, но нам нужно принять во внимание один момент. Мы не смогли параметризовать оператор EXEC, и это его главный недостаток.

sp_executesql имеет некоторые преимущества по сравнению с оператором EXEC. Теперь давайте взглянем на них:

  • sp_executesql имеет возможность повторно использовать кэшированные планы запросов.

Каждый запрос, выполняемый в SQL Server, компилируется перед выполнением. Этот процесс компиляции запроса создает выходные данные, которые называются планом запроса. Однако иногда этот процесс компиляции запросов может быть очень дорогим. По этой причине SQL Server желает повторно использовать кэшированные планы запросов, насколько это возможно, для одних и тех же запросов, чтобы снизить затраты на компиляцию запросов. Сейчас мы докажем эту мысль.

Сначала мы очистим все кэшированные планы с помощью FREEPROCCACHE. Однако не выполняйте эту команду в рабочей среде.
среды, поскольку это может повредить производительности SQL Server:

DBCC FREEPROCCACHE

На этом этапе мы выполним следующий запрос 3 раза со случайными параметрами.

1

2

3

4

5

6

7

8

DECLARE  @SqlStatment AS NVARCHAR(1000)

DECLARE  @PhoneIdType AS INT

DECLARE  @Result AS INT

    

SET @SqlStatment=’SELECT @RowNumber= COUNT(PhoneNumber) from Person.PersonPhone WHERE PhoneNumberTypeID=@PhoneType’

SET @PhoneIdType=ROUND(((20 — 1) * RAND( ) + 1), 0)

EXEC sp_executesql @SqlStatment, N’@PhoneType INT,@RowNumber INT OUTPUT’, @PhoneType=@PhoneIdType, @RowNumber=@Result OUTPUT

GO 3

Теперь проверим сгенерированные планы запросов в sys.dm_exec_cached_plans:

1

2

3

4

5

6

7

8

SELECT Stext.Text,

*

из SYS.DM_EXEC_CACHED_PLANS CACHEDPLANS

CRESS APPLY

SYS.DM_EXEC_SQL_TEXT

(CachedPlans. Plan_Handle) Stext

, где stext.text. ) из Person.PersonPhone, ГДЕ PhoneNumberTypeID=@PhoneType%’ И

   SText.text NOT LIKE ‘%sys.dm_exec_cached_plans%’;

Теперь мы повторим аналогичный тестовый сценарий для оператора EXEC:

DBCC FREEPROCCACHE

На этом шаге мы выполним динамически построенный запрос 3 раза для случайных параметров с помощью EXEC.
утверждение:

1

2

3

4

5

6

DECLARE @Param AS INT=ROUND(((20 — 1) * RAND() + 1), 0)

DECLARE @St AS NVARCHAR(1000) =’SELECT COUNT(PhoneNumber) AS Result from Person.PersonPhone ГДЕ PhoneNumberTypeID =  ‘

SET @St =CONCAT(@St,@Param)

PRINT @St

EXEC(@St)

GO 3

Сейчас мы перепроверим sys.dm_exec_cached_plans представление, чтобы увидеть, сколько планов запросов было создано:

1

2

3

4

5

6

7

8

SELECT Stext. Text,

*

из SYS.DM_EXEC_CACHED_PLANS CACHEDPLANS

CRESS APPLY

SYS.DM_EXEC_SQL_TEXT

(CachedPlans.Plan_HANDL от Person.PersonPhone ГДЕ PhoneNumberTypeID% ‘И

SText.text НЕ ТАК ‘%sys.dm_exec_cached_plans%’;

В результате sp_executesql сгенерировал один план запроса при первом выполнении запроса, а затем снова и снова использовал один и тот же план запроса. Несмотря на это, инструкция EXEC создавала новые планы запросов для каждого выполнения запроса.
Этот тип использования может потреблять ресурсы SQL Server и может быть вызван проблемами с производительностью.

  • Примечание. sp_executesql позволяет создавать параметризованные динамические запросы. Так что это более безопасно для атак SQL-инъекций. Оператор EXEC более уязвим с точки зрения SQL-инъекций.

Заключение

В этой статье мы подробно изучили процедуру sp_executesql и узнали методы ее использования.
Эта процедура очень полезна для решения проблем с динамическими запросами, однако мы должны учитывать проблемы с SQL-инъекциями, когда решаем использовать динамические запросы в SQL Server.

  • Автор
  • Последние сообщения

Esat Erkec

Esat Erkec — специалист по SQL Server, который начал свою карьеру более 8 лет назад в качестве разработчика программного обеспечения. Он является сертифицированным экспертом по решениям Microsoft для SQL Server.

Большая часть его карьеры была посвящена администрированию и разработке баз данных SQL Server. Его текущие интересы связаны с администрированием баз данных и бизнес-аналитикой. Вы можете найти его в LinkedIn.

Просмотреть все сообщения от Esat Erkec

Последние сообщения от Esat Erkec (посмотреть все)

Использование хранимой процедуры sp_executesql для выполнения динамических запросов SQL

Хранимая процедура sp_executesql используется для выполнения динамических запросов SQL в SQL Server. динамичный
SQL-запрос — это запрос в строковом формате. Существует несколько сценариев, в которых у вас есть запрос SQL в виде
нить.

Например, если пользователь хочет найти продукт по названию, он введет название продукта в поле поиска на веб-сайте. Имя продукта, представленное в виде строки, будет объединено с запросом SELECT, чтобы сформировать другую строку. Эти типы запросов должны выполняться динамически, потому что разные пользователи будут искать разные названия продуктов, поэтому запрос необходимо генерировать динамически в зависимости от названия продукта.

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

Давайте сначала создадим некоторые фиктивные данные, которые мы можем использовать для выполнения примеров в этой статье.

Создание фиктивных данных

Следующий сценарий создает фиктивную базу данных с именем BookStore с одной таблицей, т. е.
Книги . Таблица Books состоит из четырех столбцов: идентификатор , имя ,
категория и цена :

1

2

3

4

5

6

7

8

10 500025

СОЗДАТЬ базу данных BookStore;

GO

ИСПОЛЬЗОВАНИЕ Книжный магазин;

CREATE TABLE Books

(

id INT,

имя VARCHAR(50) NOT NULL,

категория VARCHAR(50) NOT NULL,

цена INT NOT NULL

)

Теперь добавим несколько фиктивных записей в таблицу Books:

1

2

3

4

5

6

7

8

10

110005

12

13

14

1

ИСПОЛЬЗОВАНИЕ Книжный магазин

Вставка в книги

Значения

(1, «Книга1», «Cat1», 1800),

(2, ‘Book2’, ‘Cat2’, 1500),

(3, ‘ Book3», «Cat3», 2000),

(4, «Book4», «Cat4», 1300),

(5, «Book5», «Cat5», 1500),

(6, «Book6» , ‘Cat6’, 5000),

(7, ‘Book7’, ‘Cat7’, 8000),

(8, ‘Book8’, ‘Cat8’, 5000),

(9, ‘Book9’, ‘ Cat9″, 5400),

(10, Book10, Cat10, 3200)

Приведенный выше скрипт добавляет 10 фиктивных записей в таблицу Books.

Работа с хранимой процедурой sp_executesql

Как я упоминал ранее, хранимая процедура sp_executesql используется для выполнения динамических запросов SQL в виде строки. Давайте посмотрим на это в действии.

Запустите следующий скрипт:

ОБЪЯВЛЕНИЕ @SQL_QUERY NVARCHAR(128)

SET @SQL_QUERY = N’SELECT id, name, price FROM Books WHERE price > 4000 ‘

EXECUTE sp_executesql @SQL_QUERY

В приведенном выше сценарии мы объявляем переменную @SQL_QUERY и инициализируем ее строковым запросом, который возвращает идентификатор, имя и цену из таблицы Books, где цена превышает 4000.

Затем мы выполняем хранимую процедуру sp_executesql с помощью команды EXECUTE. Чтобы выполнить динамический SQL-запрос в строковом формате, вам просто нужно передать строку, содержащую запрос, в запрос sp_executesql.

Важно отметить, что строка должна быть в формате Unicode, прежде чем хранимая процедура sp_executesql выполнит ее. По этой причине мы поставили «N» в начале строки, содержащей переменную @SQL_QUERY.
‘N’ преобразует строку запроса в формат строки Unicode. Вот вывод вышеуказанного скрипта:

В реальных запросах к базе данных фильтр или условие передаются пользователями. Например, пользователь может искать книги
в пределах определенного диапазона поиска. В этом случае запрос SELECT остается прежним, меняется только условие WHERE.
измененный. Удобно хранить предложение WHERE в отдельной строковой переменной, а затем объединять SELECT
условие с предложением WHERE для создания окончательного запроса. Это показано в следующем примере:

DECLARE @CONDITION NVARCHAR(128)

DECLARE @SQL_QUERY NVARCHAR (MAX)

SET @CONDITION = ‘ГДЕ цена > 5000’

ВЫПОЛНИТЬ sp_executesql @SQL_QUERY

Здесь, в приведенном выше скрипте, мы объявляем две переменные: @CONDITION и @SQL_QUERY. Переменная @CONDITION содержит
Предложение WHERE в строковом формате, тогда как @SQL_QUERY содержит запрос SELECT. Затем эти две переменные объединяются и передаются хранимой процедуре sp_executesql. Вот результат:

В выводе показаны все книги, цена которых превышает 5000.

Передача параметров хранимой процедуре sp_executesql

Вы также можете передать параметры хранимой процедуре sp_executesql. Это особенно удобно, когда вы не знаете
значения, используемые для фильтрации записей перед выполнением. Чтобы выполнить хранимую процедуру sp_executesql с параметрами, вы
необходимо выполнить следующие шаги:

  1. Во-первых, вам нужно создать переменную, в которой будет храниться список параметров.
  2. Далее в строке запроса нужно передать имена параметров
  3. Наконец, вам нужно передать запрос, переменную, содержащую список параметров, и фактические параметры.
    вместе со своими значениями в хранимую процедуру sp_executesql

Посмотрите на следующий пример:

1

2

3

4

5

6

7

объявление @condition nvarchar (128)

Declare @SQL_QUERY NVARCHAR (MAX)

DECLARE @Params NVARCHAR (1000)

SET @Condition = ‘, где цена> @LowerPrice и Price <@Higherprice'

SET @SQL_QUERY. = N’SELECT id, name, price FROM Books ‘+ @CONDITION

SET @PARAMS = ‘@LowerPrice INT, @HigherPrice INT’

ВЫПОЛНИТЬ sp_executesql @SQL_QUERY, @PARAMS, @LowerPrice = 3000, @HigherPrice = 6000

В приведенном выше сценарии мы создаем три переменные: @CONDITION, @SQL_QUERY и @PARAMS. Переменная @PARAMS — это
переменная, в которой хранится список параметров, которые мы будем использовать в формате строкового запроса.

Если вы посмотрите на значение переменной @CONDITION, то увидите, что оно содержит предложение WHERE с двумя параметрами: @LowerPrice и
@Высшая Цена. Чтобы указать параметр внутри строкового запроса, вам просто нужно добавить префикс оператора «@» перед именем параметра. Здесь параметр @LowerPrice используется для установки нижней границы цены книг, тогда как параметр @HigherPrice устанавливает верхнюю границу значения в столбце цены таблицы BookStore.

Затем при выполнении хранимой процедуры sp_executesql переменная @SQL_QUERY, содержащая строковый запрос, передается вместе с переменной @PARAMS, содержащей список параметров. Имена параметров, т. е. @LowerPrice и
@HigherPrice также передаются хранимой процедуре sp_executesql вместе со значениями 3000 и 6000.
соответственно. В выводе вы увидите записи, в которых цена находится в диапазоне от 3000 до 6000, как показано ниже:

Заключение

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