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

PRINT 'Today is ' + DATENAME(DW,GETDATE())

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.

DECLARE @DayName AS VARCHAR(9)

SET @DayName = DATENAME(DW,GETDATE())

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:

DECLARE @TheNumber AS INT

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:

DECLARE @TheNumber AS INT

SET @TheNumber = 10

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

If you prefer you can use the CONVERT function instead:

DECLARE @TheNumber AS INT

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

(

@Day AS VARCHAR(9)

)

AS

BEGIN

PRINT 'Today is ' + @Day

END

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 1 thread Add post
05 Jun 19 at 18:29

Hi Team,

Thank you for the very detailed articles and videos. I'm very much impressed with your content and these are very much useful to me. I am going through your other articles on SQL as well on this blog and below is one other solution may be with reference to the liner, "You can use the CAST or CONVERT function to change one data type into another" (also mentioned in Youtube video: https://www.youtube.com/watch?v=MiAwOoelu9k).

From knowledge obtained from your other articles, I think we can also use the QUOTENAME function which also prevents SQL injection from happening dring concatenation and avoids any mess with placing single quotes for concatenation. Kindly confirm my understanding which may be useful to other readers as well.

 

05 Jun 19 at 21:24

Hi Phani, I'm repeating the answer I gave to your question on YouTube, I hope you don't mind!  I suppose that you could use the QUOTENAME function for the part of the video that you reference as long as you use parentheses as the delimiter character. So, for example:

SET @SQLString = 'SELECT TOP ' + QUOTENAME(@Number,'(') + ' * FROM Film ORDER BY ReleaseDate'

Would return a string which appears like so:

SELECT TOP (10) * FROM Film ORDER BY ReleaseDate

This would indeed work if you passed it to the sp_executesql stored procedure. I may have missed the point of your question but I'm not sure that it's much better than using CAST or CONVERT in this case. Please let me know if I've misunderstood what you're saying!

Thanks for the suggestion!

06 Jun 19 at 16:25

Hi Andrew, I posted here and on Youtube just to ensure I get a response at anyone of them. As always, Thank you very much for your detailed explanation and all of your Teams work. 

Yes, I actually meant in current case QUOTENAME function would help and do not intend to say that its replacement for CAST / CONVERT. My bad, could not be specific as I was almost late midnight while typing my earlier comment. I understand that QUOTENAME is no replacement for CAST or CONVERT, but just wanted to share another technique which I learnt from your other tutorials (as many of us do not realize the beauty of excellent function QUOTENAME). May be while concatenation with dynamic SQL we could also use QUOTENAME with ' ' ' ' as well to avoid any mistakes with single quote placing.

Here is the snippet of SP along with its usage:
CREATE PROC sp_Top_N_FromTable
(
@TableName NVARCHAR (128),
@Number INT
)
AS
BEGIN
DECLARE @SQLString NVARCHAR(MAX)
SET @SQLString = N'SELECT TOP ' + QUOTENAME(@Number, '(') + ' * FROM ' + @TableName

EXEC sp_executesql @SQLString
END

--------------------------------------------
EXEC sp_Top_N_FromTable 'tblActors', 10
EXEC sp_Top_N_FromTable 'tblFilms', 5

 

Once again, thank you very much! :-)

Andrew G  
07 Jun 19 at 07:15

Thanks Phani, we appreciate your input!