Dynamic SQL
Part two of a four-part series of blogs

Dynamic SQL is a technique for building valid SQL statements from separate pieces of text. You can use this technique to create remarkably flexible and useful queries, as long as you're aware of the potential danger of SQL injection attacks.

  1. Dynamic SQL
  2. Concatenation in SQL (this blog)
  3. Building Dynamic SQL Statements
  4. SQL Injection Attacks

This blog is part of our complete SQL tutorial. You can also learn about this technique from our SQL Server training courses.

Posted by Andrew Gould on 22 April 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Concatenation in SQL

The key to dynamic SQL is concatenating strings to form valid SQL statements. This part of the series is designed to give you a brief primer on concatenation so feel free to skip to the next part if you already know how to do this!

Concatenation in SQL

To concatenate two strings of text in SQL you use the + symbol.  The example below prints the result of concatenating two literal strings:

PRINT 'Today is ' + 'Tuesday'

The fairly unremarkable result of executing the above code is shown below:

Concatenated string

The two strings are joined together into a single phrase.


The technique becomes more useful when one or more of the individual strings is provided by a variable component, such as a function:


The above code calculates the name of the current day of the week and concatenates it with a literal string. The result of executing the code depends on which day it is run.

Using Variables in Concatenation

When concatenating long strings it can be useful to use variables to store the various components of the phrase before joining them together.



PRINT 'Today is ' + @DayName

Converting Data Types to Text

When you're concatenating a phrase you must make sure that each bit of the sentence is a piece of text. The example below attempts to concatenate an integer with a literal string:


SET @TheNumber = 10

PRINT 'The number is ' + @TheNumber

Attempting to execute this code results in the following error message:

Error concatenating

SQL has attempted to convert our literal string into a number - what we actually want to do is convert the number into a string.

You can use the CAST or CONVERT function to change one data type into another. The example below uses CAST:


SET @TheNumber = 10

PRINT 'The number is ' + CAST(@TheNumber AS VARCHAR(2))

If you prefer you can use the CONVERT function instead:


SET @TheNumber = 10

PRINT 'The number is ' + CONVERT(VARCHAR(2),@TheNumber)

Using Stored Procedures and Parameters

You're most likely to build dynamic SQL statements using a stored procedure into which you pass parameters. The code below creates a simple stored procedure which accepts a single parameter whose value is concatenated into a string:

CREATE PROC spTodayMessage






PRINT 'Today is ' + @Day


After the code is executed to create the stored procedure it can be used to generate a message as shown below:

EXEC spTodayMessage 'Wednesday'

Creating Useful Stored Procedures

Now that you know the basics of concatenation in SQL the next part of this series will show you how to create useful stored procedures which will construct and execute dynamic SQL statements.

This blog has 0 threads Add post