Execute Dynamic SQL commands in SQL Server

 

Problem

In some applications, having hard coded SQL statements is not appealing because of the dynamic nature of the T-SQL queries being issued against the Microsoft SQL Server DBMS. 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, PHP, Java 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 SELECT statement or SQL Query with SQL variables
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 Attacks 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 at runtime.

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 in Microsoft SQL Server. Let's say we have a simple example where need to find all records from the customers table where City = 'London'. This can be done easily as the following example shows.

DECLARE @city varchar(75)

SET @city = 'London'

SELECT * FROM Person.Address WHERE City = @city

Here is the result set:

We can turn the above SQL query into a stored procedure with the following syntax:

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 SQL Server stored proc development (parameter values, output parameters, code reuse, etc.) check out this Transact-SQL tutorial.

Dynamic SQL commands using EXEC Statement

With the Execute Statement 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 Dynamic SQL query 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)

Here is the result set:

Dynamic SQL commands using sp_executesql

With the EXEC sp_executesql 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, which are SQL strings in this example:

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

Here is the result set:

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 functionality in your T-SQL code: