Dynamic T-SQL and how it can be useful

• With the use of the keyword EXEC/EXECUTE ;
• C using the stored procedure 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. When writing a command, it is very easy to make a mistake with the number "'", since additional “'” must be specified to pass the text value to the request.
2. With such a query, Sql injections are possible (SQL Injection). For example, it is worth setting a value for @city like this
   

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

   - and the result will be sad, because the select operation succeeds, as DROP TABLE customers operation.
3. It is possible that you will have several variables containing the codes of your commands. Something like EXEC(@sql1 + @sql2 + @sql3).
   What difficulties may arise here?
   It must be remembered that each command will work separately, although at first glance it may seem that the concatenation operation (@sql1 + @sql2 + @sql3) will be performed, and then the general command will be executed. You also need to remember that a general limitation is imposed on the EXEC command parameter of 4000 characters.
4. There is an implicit type conversion, since Parameters are passed as a string.

 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. Unlike EXECUTE when using sp_executesql , no type casting is necessary if we use typed parameters sp_executesql.
2. This solves the problem with the extra "'".
3. The security problem is solved - Sql injection (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%' 

Plan a query when using exec

Query plan when using sp_executesql

• Use a reporting system, for example, MS Sql Reporting Service . Create a matrix report, and as a request we will have a “simple” Select . Why we did not do that? There were not many reports in the project to implement SSRS there.
• Use the same “simple” select and on the server side already create the required “form” DataSet . Yes, the problem was solved initially, when there was very little data on the goods. As soon as there was a lot of data, the time for collecting the report became beyond the established timeout .
• Use Pivot in sql. Yes, a great solution when you know that you have only these attributes, and there will be no new ones. And what to do when the number of attributes often changes. And again, for each group of objects we have our own set of attributes, we will again return to creating a procedure for each group of objects. Not a very convenient solution, is it?
• And if you use Pivot, but add a little dynamic sql there ? - Yes, this is a decision that has the right to life. We will describe it as an example of using 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 

• Initializing a variable with the value of our product category - declare @CategoryOfProductsId int = 1
declare @CategoryOfProductsId int = 1

• Next, we need to get a list of columns for our product category, but they should be enclosed in “[]” brackets and listed in “,” as required by the syntax of the Pivot function -
  
  Getting a list of columns for the category of goods

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

  
  
  
  Well, then everything is simple: when executing the code, the list of columns for the Pivot function will be substituted from @PivotColumnHeaders
@PivotColumnHeaders

  If we execute select @PivotTableSQL , we will get the query that we would have to write manually without using dynamic sql.
  
  The result of this request will be a report of this type:
  
  
  
  In conclusion, it should be noted once again using dynamic sql , we can at first glance solve non-trivial tasks in trivial ways. To do this, sometimes you need to look at the problem from the other side.