Introduction

Quite often you do not know what values or parameters will be required, and writing many conditional clauses to cater for the possible variations is not only time consuming and difficult to maintain, but very inefficient as well. As the name suggests, Dynamic SQL allows you to write SQL code dynamically because certain sections of the code will only be known at run-time. The mere mention of Dynamic SQL makes some people a bit scared, but it is actually quite simple once you get your head around it. Dynamic SQL is essentially normal SQL written in such a way that you end up with a “customised” SQL script at run-time.

Basic Concatenation and Explicit Conversions

Before we get started we need to understand the basics of how strings are concatenated in SQL. Let’s look at the following script:

DECLARE @NoOfColumns AS SMALLINT
SET @NoOfColumns = 3
DECLARE @Result1 AS VARCHAR(100)
DECLARE @Result2 AS VARCHAR(100)
SELECT @Result1 = 'Total of ' + CAST(@NoOfColumns AS VARCHAR) + ' column(s)'
SELECT @Result1
SELECT @Result2 = CONCAT('Total of ', CAST(@NoOfColumns AS VARCHAR), ' column(s)')
SELECT @Result2

Result1:

Result2:

Both of the SELECT statements can be used to concatenate the separate parts and return the same result, by either using the + operator or the CONCAT function. Please note that the CAST function is used explicitly change the data type because you can’t implicitly concatenate a non-alphanumeric data type into an alphanumeric data type. You could also use the CONVERT function to do this.

If you run the following script and do not explicitly change the data type you will receive an error:

DECLARE @NoOfColumns AS SMALLINT
SET @NoOfColumns = 3
DECLARE @Result1 AS VARCHAR(100)
SELECT @Result1 = 'Total of ' + @NoOfColumns + ' column(s)'
SELECT @Result1

Result1:

For more on implicit and explicit conversions have a look at: https://www.brentozar.com/archive/2016/05/implicit-vs-explicit-conversion/

For the differences between CAST and CONVERT have a look at: https://blog.teamsql.io/cast-vs-convert-714583582566

Escaping a Single Quote in a String

Concatenating a string is very simple, but what happens if we want the result to include quote marks around the number? Let’s look at the following script:

DECLARE @NoOfColumns AS SMALLINT
SET @NoOfColumns = 3
DECLARE @Result AS VARCHAR(100)
SELECT @Result = 'Total of '' + CAST(@NoOfColumns AS VARCHAR) + '' column(s)'
SELECT @Result

Result:

As you can see, you can’t simply add the single quotes into your string because the new single quote escapes the second one, leaving your string declaration open until the very last single quote at the end. Everything in between is treated as text. A good clue that this is happening is when functional parts of your script change to the same colour as text.

The correct syntax would be to add two single quotes in your string as follows:

DECLARE @NoOfColumns AS SMALLINT
SET @NoOfColumns = 3
DECLARE @Result AS VARCHAR(100)
SELECT @Result = 'Total of ''' + CAST(@NoOfColumns AS VARCHAR) + ''' column(s)'
SELECT @Result

Result:

Looking only at the first section of the string ('Total of ''') for example:

• - The first quote opens the string declaration
• - The second quote tells SQL that the next quote should be included in the string
• - The third quote is that one that is included in the string
• - The fourth quote closes the string declaration

Writing and Executing Dynamic SQL

Now let’s combine what we’ve learnt and write some Dynamic SQL. For arguments sake, let’s say that we have a SQL instance which contains multiple databases that all have the exact same schema.

If you wanted to do a count of how many rows were inserted today in a particular table by only changing the database name, you could write something like this:

DECLARE @DBName AS VARCHAR(20)
SET @DBName = 'Branch1'
DECLARE @Date AS DATE
SET @Date = GETDATE()
DECLARE @SQL AS VARCHAR(2000)
SELECT @SQL =
'SELECT COUNT(1) ' +
'FROM [' + @DBName + '].[dbo].[CommonTable] ' +
'WHERE [InsertDate] = ''' + CAST(@Date AS VARCHAR) + ''''
PRINT @SQL
EXEC (@SQL)

The EXEC statement will execute the T-SQL code that you have generated dynamically.

This is obviously just a silly example but it shows the basic structure of how you dynamically create your T-SQL code and execute it. Realistically the database name would be passed as a parameter in a stored procedure, but please see the important note lower down.

Debugging Tip

As you will see in the code example above, before executing anything, make use of the PRINT statement when testing your code to see what the result of the concatenations looks like. If you’re using SQL Management Studio, select the “Results” tab at the bottom, copy the PRINT text result from there and paste it into a new query window. This way you will be able to clearly see if you are getting the correct syntax.

The PRINT above will output the following T-SQL:

A Very Important Note

When placing your Dynamic SQL code into production (typically in stored procedures), be careful about concatenating alphanumeric parameters directly because of SQL injection.

For more information on SQL injection have a look at: https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-injection

Conclusion

As you have seen Dynamic SQL isn’t so difficult as long as you keep in mind what you want your final script to look like. The PRINT statement is your friend and it is important to remember about SQL injection when working with parameters and Dynamic SQL.