Execute Dynamic SQL commands in SQL Server

By: Greg Robidoux   |   Updated: 2018-11-16   |   Comments (57)   |   Related: 1 | 2 | 3 | 4 | More > Dynamic SQL

Problem

In some applications, having hard coded SQL statements is not appealing because of the dynamic nature of the queries being issued against the database server. Because of this sometimes there is a need to dynamically create a SQL statement on the fly and then run that command. This can be done quite simply from the application perspective where the SQL statement is built on the fly whether you are using ASP.NET, ColdFusion or any other programming language. But how do you do this from within a SQL Server stored procedure?

Solution

How to build dynamic SQL statement in SQL Server

SQL Server offers a few ways of running a dynamically built SQL statement. Here are a few options:

1. Writing a query with parameters
2. Using EXEC
3. Using sp_executesql

We will use the AdventureWorks database for the below examples.

Things to Note

Although generating SQL code on the fly is an easy way to dynamically build statements, it does have some drawbacks. 

One issue is the potential for SQL Injection where malicious code is inserted into the command that is being built.  The examples below are very simple to get you started, but you should be aware of SQL Injection and ways to prevent it by making sure your code is robust to check for any issues before executing the statement that is being built.

Another issue is the possible performance issues by generating the code on the fly.  You don't really know how a user may use the code and therefore there is a potential for a query to do something you did not expect and therefore become a performance issue.  So once again, you should make sure your code checks for any potential problems before just executing the generated code.

Dynamic SQL by writing a query with parameters

This first approach is pretty straight forward if you only need to pass parameters into your WHERE clause of your SQL statement. Let's say we need to find all records from the customers table where City = 'London'. This can be done easily such as the following example shows.

DECLARE @city varchar(75)

SET @city = 'London'

SELECT * FROM Person.Address WHERE City = @city

We can turn the above into a stored procedure as follows:

CREATE PROCEDURE dbo.uspGetCustomers @city varchar(75)
AS
BEGIN
   SELECT * FROM Person.Address WHERE City = @city
END
GO

This can then be executed as follows:

dbo.uspGetCustomers @city = 'London'

To learn more about stored procedure development check out this tutorial.

Dynamic SQL commands using EXEC

With this approach you are building the SQL statement on the fly and can pretty much do whatever you need to in order to construct the statement. Let's say we want to be able to pass in the column list along with the city.

For this example we want to get columns AddressID, AddressLine1 and City where City = 'London'.

As you can see from this example handling the @city value is not at straight forward, because you also need to define the extra quotes in order to pass a character value into the query. These extra quotes could also be done within the statement, but either way you need to specify the extra single quotes in order for the query to be built correctly and therefore run.

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)

SET @columnList = 'AddressID, AddressLine1, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM Person.Address WHERE City = ' + @city

EXEC (@sqlCommand)

Dynamic SQL commands using sp_executesql

With this approach you have the ability to still dynamically build the query, but you are also able to use parameters as you could in example 1. This saves the need to have to deal with the extra quotes to get the query to build correctly. In addition, using this approach you can ensure that the data values being passed into the query are the correct datatypes.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)

SET @columnList = 'AddressID, AddressLine1, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM Person.Address WHERE City = @city'

EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

So here are three different ways of writing dynamic queries. In addition to the above, here are some other articles that give you other perspectives on setting up and using dynamic SQL.

• Protecting Yourself from SQL Injection in SQL Server - Part 1
• Protecting Yourself from SQL Injection in SQL Server - Part 2
• More SQL Injection Articles

Watch Video Version of Tip

• Click Here

Next Steps

• If at all possible look at avoiding the use of dynamic SQL especially where you start to manipulate the overall query string. This could potentially open up other areas of concern such as SQL Injection and performance issues.
• Look into using dynamic SQL in your stored procedures by employing one of the three techniques above instead having the code generated from your front end application.

About the author

Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

Related Resources

• Execute Dynamic SQL commands in SQL Server...
• Dynamic SQL execution on remote SQL Server using E...
• Run a Dynamic Query against SQL Server without Dyn...
• Using the CASE expression instead of dynamic SQL i...
• More Database Developer Tips...