This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Dynamic SQL Concatenation in SQL (this blog) Building Dynamic SQL Statements SQL Injection Attacks This blog is part of our complete SQL tutorial. You can also learn about this technique from our SQL training courses.

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.

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

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:

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:

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)

)

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.

Parts of this blog
Dynamic SQL Concatenation in SQL (this blog) Building Dynamic SQL Statements SQL Injection Attacks

Some other pages relevant to the above blogs include:

This blog has 1 thread

Add a new post

From:	Phani459
When:	05 Jun 19 at 18:29

Reply to Phani459

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.

From:	Andrew G
When:	05 Jun 19 at 21:24

Reply to Andrew G

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!

From:	Phani459
When:	06 Jun 19 at 16:25

Reply to Phani459

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

From:	Andrew G
When:	07 Jun 19 at 07:15

Reply to Andrew G

Thanks Phani, we appreciate your input!

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Concatenation in SQL

Concatenation in SQL

Using Variables in Concatenation

Converting Data Types to Text

Using Stored Procedures and Parameters

Creating Useful Stored Procedures

Head office

London

Manchester