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.

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

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

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:

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
  • The query code to create the report

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.

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.

Anatoliy Kotelevets

Software and DBA developer at INOSTUDIO Solutions
Anatoliy Kotelevets graduated from Taganrog State Radiotechnical University, now Southern Federal University, in 2011. He has been involved in software development since 2008. For the last 6 years, he has been actively working with database development for ERP systems. Currently, he is a Software and DBA developer at INOSTUDIO Solutions in Taganrog.
Anatoliy Kotelevets

Latest posts by Anatoliy Kotelevets (see all)