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

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

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 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. Происходит неявное приведение типов, т.к. параметры передаются в виде строки.

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).

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

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