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

Чем может быть полезен динамический 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)

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'

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

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

set @city = '''DROP TABLE customers--'''

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

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

Что изменится при использовании 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

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

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

В отличие от EXECUTE при использовании sp_executesql, не нужно никакое приведение типов, если мы используем типизированные параметры sp_executesql.
Это решает проблему с дополнительными «’».
Решается проблема безопасности — 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 – это возможность возвращать значение через 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

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,''))

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, мы можем решать на первый взгляд нетривиальные задачи тривиальными способами. Для этого порой требуется посмотреть на проблему с другой стороны.

sqlcom.ru

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

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

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

С использование ключевого слова EXEC/EXECUTE;
C использование хранимой процедуры sp_executesql

Данные способы отличаются между собой кардинально. На небольшом примере мы постараемся пояснить, чем они отличаются.Пример кода с EXEC/EXECUTEDECLARE @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' Что же тут плохого? — Запрос отработает, и все будут довольны. Но все же, есть несколько причин, почему так делать не стоит:

При написании команды очень легко ошибиться с количеством «’», т.к. необходимо указывать дополнительные «’», чтобы передать текстовое значение в запрос.
При таком запросе возможны Sql инъекции (SQL Injection). Например, стоит задать значение для @city вроде такого set @city = '''DROP TABLE customers--''' — и результат будет печальный, т.к. операция select выполнится успешно, как и операция DROP TABLE customers.
Возможна ситуация, когда у вас будет несколько переменных, содержащих коды ваших команд. Что-то типа такой EXEC(@sql1 + @sql2 + @sql3). Какие трудности могут возникнуть тут? Нужно помнить, что каждая команда отработает отдельно, хотя на первый взгляд, может показаться, что будет выполнена операция конкатенации (@sql1 + @sql2 + @sql3), а затем выполнится общая команда. Также нужно помнить, что накладывается общее ограничение на параметр команды EXEC в 4000 символов.
Происходит неявное приведение типов, т.к. параметры передаются в виде строки.

Что изменится при использовании sp_executesql? – Разработчику проще писать код и его отлаживать, т.к. код будет написан практически как обычный Sql запрос.Пример кода с sp_executesqlDECLARE @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 Что же изменилось?

В отличие от EXECUTE при использовании sp_executesql, не нужно никакое приведение типов, если мы используем типизированные параметры sp_executesql.
Это решает проблему с дополнительными «’».
Решается проблема безопасности — 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.

Использовать систему отчетности, например, 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, мы можем решать на первый взгляд нетривиальные задачи тривиальными способами. Для этого порой требуется посмотреть на проблему с другой стороны.

habr.com

Работаем с Dynamic SQL | INOSTUDIO — Студийные записи

Для чего нужен dynamic sql?

Каждый решает для себя.

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

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

С использование ключевого слова EXEC/EXECUTE.
C использование хранимой процедуры sp_executesql.

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

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

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

Что же тут плохого?

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

При написании команды очень легко ошибиться с количеством «’», т.к. необходимо указывать дополнительные «’», чтобы передать текстовое значение в запрос.
При таком запросе возможны Sql инъекции (SQL Injection). Например, стоит задать значение для @city вроде такого set @city = '''DROP TABLE customers--''' — и результат будет печальный, т.к. операция select выполнится успешно, как и операция DROP TABLE customers.
Возможна ситуация, когда у вас будет несколько переменных, содержащих коды ваших команд. Что-то типа такой EXEC(@sql1 + @sql2 + @sql3). — Здесь каждая команда отработает отдельно, хотя на первый взгляд, может показаться, что будет выполнена операция конкатенации (@sql1 + @sql2 + @sql3), а затем выполнится общая команда. Также нужно помнить, что накладывается общее ограничение на параметр команды EXEC в 4000 символов.
Происходит неявное приведение типов, т.к. параметры передаются в виде строки.

Что изменится при использовании sp_executesql?

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

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

Изменения

В отличие от EXECUTE при использовании sp_executesql, не нужно никакое приведение типов, если мы используем типизированные параметры sp_executesql.
Это решает проблему с дополнительными «’».
Решается проблема безопасности — 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%' Рисунок 1. План запроса при использовании ExecРисунок 2. План запроса при использовании 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…

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

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

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

declare @CategoryOfProductsId int = 2 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 = 2
Далее нам нужно получить список колонок для нашей категории товаров, но при этом они должны быть заключены в “[]” скобки и перечислены через “,” как этого требует синтаксис функции Pivot —

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

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

Рисунок 3. Код запроса для построения отчета

Коротко о главном

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

Оригинальная статья размещена на Хабрахабр.

inostudio.com

НОУ ИНТУИТ | Лекция | Динамический SQL

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

Создание операторов динамического SQL

Операторы динамического SQL - в отличие от операторов встроенного SQL - формируются не на этапе компиляции, а на этапе выполнения приложения. Динамический SQL может применяться совместно с ODBC API или в рамках SQL/CLI, представляющего собой расширенный уровень соответствия стандарта SQL-99.

Поддержка динамического SQL на начальном уровне соответствия стандарту SQL-92 не требуется.

Операторы динамического SQL формируются как текстовые переменные.

Например:

Stmt1:='SELECT * FROM tbl1';

Для динамического формирования оператора можно выполнять последовательное объединение строк.

Операторы динамического SQL можно использовать:

однократно, производя за один шаг компиляцию и выполнение оператора. Будем называть такое применение одношаговым интерфейсом;
многократно, разделяя процесс компиляции оператора, на котором строится план выполнения, и процесс непосредственного выполнения оператора. Будем называть такое применение многошаговым интерфейсом.

Одношаговый интерфейс

одношаговый интерфейс реализуется SQL-оператором EXECUTE IMMEDIATE, который имеет в стандарте SQL-92 следующее формальное описание:

EXECUTE IMMEDIATE :variable;

На оператор, указываемый переменной (variable), накладываются следующие ограничения:

оператор не может использовать INTO-переменные;
оператор не может использовать переменные связи.

Следующий пример иллюстрирует применение динамического SQL с одношаговым интерфейсом:

stmt_str := 'INSERT INTO ' || table_name || ' values (:f1, :f2, :f3)'; EXEC SQL EXECUTE IMMEDIATE :stmt_str;

Многошаговый интерфейс

Оператор EXECUTE IMMEDIATE удобен для одноразового выполнения, но при необходимости неоднократного выполнения, например в цикле одного и того же оператора, но с различными параметрами, более эффективно использовать многошаговый интерфейс, реализуемый операторами PREPARE и EXECUTE.

При выполнении оператора PREPARE, указываемый им SQL-оператор передается в СУБД. Далее выполняется синтаксический разбор оператора и строится план выполнения. После этого при каждом выполнении оператора EXECUTE используется уже "откомпилированный" SQL-оператор, что значительно повышает производительность. Дополнительно при выполнении оператора EXECUTE на сервер передаются значения переменных связи (если они есть), используемые, в частности, для вычисления предиката фразы WHERE.

Оператор PREPARE имеет в стандарте SQL-92 следующее формальное описание:

PREPARE [ GLOBAL | LOCAL ] operator_sql FROM string_variable;

Параметр operator_sql определяет идентификатор SQL-оператора, указываемый далее для выполнения в операторе EXECUTE или для включения в курсор в операторах ALLOCATE CURSOR или DECLARE CURSOR .

Параметр string_variable указывает строку, содержащую динамически сформированный текст SQL-оператора.

Например:

stmt_str := 'INSERT INTO ' || table_name || ' values (:f1, :f2, :f3)'; EXEC SQL PREPARE GLOBAL stmt1 FROM :stmt_str;

Фразы GLOBAL и LOCAL определяют область видимости оператора: GLOBAL указывает, что оператор с данным идентификатором доступен всем процессам данного сеанса работы с СУБД, а LOCAL ограничивает доступ рамками данного выполняемого модуля (значение по умолчанию).

Если создаются два одноименных оператора, но один как GLOBAL, а другой - как LOCAL, то СУБД создает два отдельных плана выполнения как для разных операторов. В противном случае при компиляции оператора с уже существующим именем просто строится новый план выполнения оператора.

Для освобождения подготовленного SQL-оператора используется оператор DEALLOCATE PREPARE, который освобождает все ресурсы, занимаемые подготовленным SQL-оператором.

Например:

EXEC SQL DEALLOCATE PREPARE GLOBAL stmt1;

Для выполнения откомпилированного SQL-оператора используется оператор EXECUTE, который в стандарте SQL-92 имеет следующее формальное описание:

EXECUTE [ GLOBAL | LOCAL ] operator_sql [ INTO {variable .,:} | { SQL DESCRIPTOR [ GLOBAL | LOCAL ] descriptor_name } ] [ USING {variable .,:} | { SQL DESCRIPTOR [ GLOBAL | LOCAL ] descriptor_name } ]

Фраза INTO указывается в том случае, если выполняемый SQL-оператор представляет собой запрос, возвращающий одну строку.

Динамические параметры

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

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

динамические параметры задаются в тексте SQL-оператора символами "знак вопроса". Стандарт не определяет максимально допустимое число динамических параметров. Как правило, СУБД могут иметь ограничения только на размер вводимого SQL-оператора.

Например:

stmt_str :='INSERT INTO tbl1 VALUES (?, ?, ?)'; EXEC SQL PREPARE stmt2 FROM :stmt_str;

При выполнении данного откомпилированного оператора вместо динамических параметров значения будут подставляться в порядке, указанном в SQL-операторе EXECUTE или в области SQL-дескриптора.

Список значений для динамических параметров может быть указан:

фразой USING оператора EXECUTE - для динамических параметров, не указываемых фразой INTO откомпилированного оператора;
фразой INTO оператора EXECUTE - для динамических параметров, указанных во фразе INTO откомпилированного оператора.

Например:

stmt_str1 :='INSERT INTO tbl1 (f1,f2,f3) VALUES (?, ?, ?)'; EXEC SQL PREPARE stmt2 FROM :stmt_str1; EXEC SQL EXECUTE stmt2 USING :f1, :f2, :f3;

Значение переменных f1, f2 и f3 основного языка программирования будут переданы на сервер для выполнения откомпилированного оператора с идентификатором stmt2.

Возможен вариант, когда откомпилированный оператор содержит динамические параметры и во фразе INTO оператора SELECT, и в предикате.

Например:

stmt_str2 :='SELECT f1, f2, f3 FROM tbl1 INTO ?, ?, ? WHERE f2= ?'; EXEC SQL PREPARE stmt3 FROM :stmt_str2; EXEC SQL EXECUTE stmt3 INTO :f1, :f2, :f3 USING :f4;

Переменные f1, f2 и f3 основного языка программирования будут использованы как INTO-переменные, а значение переменной f4 будет передано на сервер для выполнения откомпилированного оператора с идентификатором stmt3.