Dynamic T-SQL and Benefits of its Usage

In our projects, we have to cope with different tasks. To solve some of them, we use dynamic T-SQL.

Why do we need dynamic T-SQL? Well, it is up to you.

In one of the projects, we have solved the task of building dynamic reports, and in others — data migration. Dynamic T-SQL is essential when you need to create, modify, get data or objects, but values or names come as parameters. For sure, it may seem unreasonable. Still, such tasks are possible. Later in the article, we will see several examples.

You can execute a dynamic command in several ways:

• Using the EXEC/EXECUTE keyword;
• Using the sp_executesql stored procedure.

These ways differ greatly. We will try to explain this difference on the particular example.

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)

As you can see, we create a dynamic command. If we execute select @sql, we will get the following result:

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

What is wrong here? The query works well and everyone should be satisfied. Still, there are some reasons why you should not do this:

1. When typing a command, it is very easy to make a mistake with a number of single quotes “ ’ ”, as it is necessary to specify additional single quotes to pass a text value into the query.
2. Because of SQL injections. For example, for @city you can specify the following:

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

   The result is going to be horrible, as soon as both SELECT and DROP TABLE customers may be successfully executed.

3. You may face the situation when you have several variables containing codes of your commands. For example, EXEC(@sql1 + @sql2 + @sql3).What difficulties may occur here?It is necessary to remember that each command works separately. However, it may seem that the concatenation operation (@sql1 + @sql2 + @sql3) will be completed, and then the general command will be executed. In addition, you need to remember EXEC parameter is restricted to 4000 symbols.
4. An implicit type casting takes place, as parameters are passed as strings.
   What will change if we use sp_executesql? It is easier for a developer to write and debug code, as it looks like a standard SQL query.

   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

What has changed?

1. Type casting is not required.
2. No problems with “ ‘ ”.
3. No problems with SQL Injections.

Query plans are cached for both methods, however, they have differences. To check these differences, refer to the picture 1 and picture 2.

The execution plan:

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%'

The execution plan using Exec:

The execution plan using sp_executesql:

In addition, one of the benefits of using sp_executesql is a possibility to return a value via the OUT parameter.

To move on, I am going to provide an example of how we solved one of the issues in the project using dynamic T-SQL.

Assume we have a product. Each object has its own set of properties (attributes), which distinguishes it. There may be a different amount of products and different product types.

A client required a report containing n rows and m columns, where m is a set of attributes. We gathered this report by the object group or for an individual object. However, the point is that each report contains a different amount of columns for each object group. As there was the initial connection between objects, then we decided to solve the issue without modifying the database architecture. Generally, there are several available solutions:

• To use a reporting system, for example, MS SQL Reporting Service. You can create a matrix report, and use the simple SELECT statement as a query. Why have not we done this? There were not too many reports in the project, so using SSRS was not a good idea.
• To use the simple SELECT statement and to create a required DataSet on the server side. We used this method initially when we had a small amount of information about products. As soon as the amount of the information increased, the time required to create a report exceeded the set
• To use Pivot in SQL. This is a great solution when you know that you will work only with these attributes. What to do if a number of attributes is going to change often? In addition, for each object group, we will have its own set of attributes. Thus, we will have to create a procedure for each object group, which is uncomfortable, isn’t it?
• To use Pivot with dynamic T-SQL. This is the most suitable solution. Therefore, I am going to describe it.

Reference to scripts for creating tables and the query.

The report will be based on a simple query:

• The main code for the report

  SELECT p.Id as ProductID, 
  		p.Name as [Name],
  		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

• The query code to create the report

  SELECT p.Id as ProductID, 
  		p.Name as [Name],
  		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
  
  The query code to build a report
  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 [Name],
  		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

Let’s check what we have created:

• Initialize a variable with the value of our product category – declare @CategoryOfProductsId int = 1
• Get a list of columns for our product category. In addition, we need to enclose categories in square brackets and list them with “,” as required by the Pivot function syntax.

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

When executing the code, the list of columns for the Pivot function will be substituted with the list from @ PivotColumnHeaders.

If we execute select @PivotTableSQL, we will get the query, which we would have to write manually if we did not use dynamic T-SQL.

The result of this query will be as follows:

In conclusion, it should be noted that using dynamic T-SQL, we can solve non-trivial tasks with simple methods.

Useful tool:

SQL Complete – write, beautify, refactor your code easily and boost your productivity.