dynamic sql

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 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 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
SQL Server offers a few ways of running a dynamically built SQL statement. These ways are:
Writing a query with parameters
Using EXEC
Using sp_executesql
1. 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 customers WHERE City = @city
2. 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 CustomerID, ContactName 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 = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)
3. sp_exectesql
With this approach you have the ability to still dynamically build the query, but you are also able to still 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, with 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 = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers 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.
The Curse and Blessings of Dynamic SQL
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 2)


Introduction to Dynamic SQL (Part 1)


One of the most common questions we get involves Dynamic SQL. We have some articles that cover it but none that really start with the basics. So Merkin sat down and wrote this introduction to dynamic SQL. Using dynamic SQL you can put a SQL statement inside a variable and execute that statement. It's what you have to do when you're trying to run Select * from @TableName. Thanks Merkin!
Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexable and powerful tool. You can use dynamic sql to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.
In part one of this two part series I will introduce you to dynamic SQL and give you some simple examples. In part two I will explain some more advanced uses for it and answer a lot of the questions we get in the forums.
Dynamic SQL on the client
If you are an ASP developer you would be already familiar with the concept of dynamic SQL. How may times have you done something like this:
dim sql
sql = "Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = "
sql = sql & request.querystring("ArticleID")

set results = objConn.execute(sql)
or slightly more elaborate
dim sql
sql = "Insert into Users (UserName, FirstName, LastName, EMailAddress) "
sql = sql & "Values('" & request.form("UserName") & "', '" & request.form("FirstName")
sql = sql & "', '" & request.form("LastName") & "', '" & request.form("EmailAddress") & "')"

objConn.execute(sql)
Or for a generic table viewer
dim sql
sql = "Select * from " & request.querystring("TableName")
set results = objConn.execute(sql)
In each case, you are building your sql statement as a string, then executing that statement against an active database connection.
Dynamic SQL in a stored procedure
Once you move into the realm of stored procedures, you move away from this style of coding. Instead you would create a procedure with an input parameter.
Create Procedure GetArticle
@ArticleID int
AS

Select ArticleTitle, ArticleBody
FROM
Articles
WHERE
ArticleID = @ArticleID

GO
However, SQL Server doesn't like certain things being passed as parameters, object names are a good example. If you try the third example in a stored proc such as:
Create Procedure GenericTableSelect
@TableName VarChar(100)
AS
SELECT *
FROM @TableName

GO
You will get an error. To get around such restrictions we can use dynamic SQL. We will follow the same logic here, build a string, then execute it.
Create Procedure GenericTableSelect
@TableName VarChar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT * FROM '
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

GO
Try that. That should do it.
The downside of this method is twofold. Firstly, and most importantly, your stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a the performance boost that you usually gain with stored procedures.
The other downside, IMHO, is that you lose the nice formating you are able to achieve with stored procedures that you were not able to do when you were building queries in ASP.
The advantage is, of course, that you are able to achive a flexability in your code that you can not get with standard SQL.
That wraps up part one. Hopefully you now have an idea of what dynamic SQL is and why you would want to use it. In part two I will demonstrate some more complex and real world examples as well as some techniques for caching and speeding up dynamic queries.
Until then have fun.

Introduction to Dynamic SQL (Part 2)
By Damian Maclennen on 27 June 2001 | 8 Comments | Tags: Dynamic SQL

In a follow up to the Introduction to Dynamic SQL we take you through some of the tricks and pitfalls of this technique. We'll cover Create Table, sp_executesql, permissions, scope and more.
Welcome to Part 2 of my Introduction to Dynamic SQL.
In part one I explained what Dynamic SQL is and how to use it. In this article I will show some more useful applications for it and a few tricks.
The IN Clause
The IN clause is a good example of a use for Dynamic SQL. A lot of SQL Server developers use ASP or a similar web scripting language.
If in an asp page you have a Select list with multiple allowed values, the value of request.form("myList") on the processing page might look like this "1,3,4,6".
So we try to write a stored proc around this
Create Procedure Search
@strIDs VarChar(100)
AS

SELECT *
FROM
Products
WHERE
ProductID in (@strIDs)

GO
Oooops! No Go.
This will work
Create Procedure Search
@strIDs VarChar(100)
AS

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')'

Exec ( @SQL)

GO
N.B. This can also be solved using a technique like this.
Aliases
Giving a table or column a dynamic alias is a use for dynamic SQL.
This will not work
Select UserName FROM Table as @Alias
This will
Exec('Select UserName FROM Table as ' @Alias)
DDL
A common question asked of SQL Team is "How do I write a stored procedure that will create a table/database. I want to pass in the name"
SQL Server will not allow this
Create Table @TableName (
ID int NOT NULL Primary Key,
FieldName VarChar(10)
)
Once again, dynamic SQL to the rescue
Declare @SQL VarChar(1000)

SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'

Exec (@SQL)
Similarly, the code to create a database would look like this:
Exec('Create Database ' + @myDBName)
sp_executesql
sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql.
This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.
An example
Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql
SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName'

Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'
The first parameter here is the SQL statement, then you must declare the parameters, after that you pass the in parameters as normal, comma separated.
sp_executesql is also useful when you want to execute code in another database as it will run code in the context of it's database, rather than the one it was called from.
Try this from a database that is not Pubs
Create View pubs.dbo.Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)
You will get this error: 'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.
So you build the dynamic sql, then run it in Pub's copy of sp_executesql
I.E.
Declare @SQL nVarChar(1000)

Select @SQL = 'Create View Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)'

Execute pubs.dbo.sp_executesql @sql
Permissions
When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems.
Scope
When you run dynamic sql, it runs in it's own scope.
This
exec('set rowcount 3')

Select * from Authors

exec('set rowcount 0')
Will have no effect on the result set returned from Authors. This is because by the rowcount statements have gone out of scope by the time the Select occurs.
This would be solved by this
exec('set rowcount 3 Select * from Authors Set rowcount 0')
Declaring variables inside a dynamic SQL batch will also not be available outside the batch and vice versa. As a result, this would also not work.
declare @i int
Exec ('Select @i = 1')
Temp tables can be used to interact between batches of standard SQL and dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes, however a temp table created before the batch will be available to it.
Create Table #tempauth(
au_id VarChar(100),
au_fname VarChar(100),
au_lname VarChar(100)

)

declare @SQL VarChar(1000)
Select @SQL = 'Insert into #tempauth Select au_id, au_fname, au_lname FROM Authors'
exec(@SQL)


Select * from #tempauth

drop table #tempauth

Problem
I am trying to pass a comma delimited list of values into a stored procedure to limit the result set. Whenever I use the variable in the IN clause I get an error message. Is there a way to do this without using Dynamic SQL?
Solution
There is a way to do this without using Dynamic SQL, but first lets explore the problem. I will be using the AdventureWorks Database in the following examples.
This will work great as long as you only have a single value.

Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3'

Select * from HumanResources.Employee
Where ManagerID IN (@ManagerIDs)
But as soon as you add the comma, the results will look something like this.

Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3,6'

Select * from HumanResources.Employee
Where ManagerID IN (@ManagerIDs)

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '3,6' to data type int.
This is because SQL Server knows that the ManagerID column is an integer and is trying to implicitly convert the @ManagerIDs variable.
In order to resolve the issue you can execute the statement using Dynamic SQL. This will allow you to build the entire query “dynamically” before executing it.

Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3,6'
Declare @SQL Varchar(1000)
Set @SQL =
'Select * from HumanResources.Employee
Where ManagerID IN (' + @ManagerIDs + ')'

EXEC (@SQL)
This will allow you to execute the query, but Dynamic SQL is a security risk and may not even be allowed in certain organizations.
So how do you execute the query without using Dynamic SQL? This can be accomplished using XML.
The first thing you need to do is create an xml string from the comma delimited string.

Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3,6'

DECLARE @XmlStr XML
SET @XmlStr =
--Start Tag
'<ManagerID>' +
--Replace all commas with an ending tag and start a new tag
REPLACE( @ManagerIDs, ',', '</ManagerID><ManagerID>') +
--End Tag
'</ManagerID>'
Selecting the xml value will display the following.

Select @XmlStr  

Now that you have an xml string we can query it and display the results as rows.

SELECT x.ManagerID.value('.', 'INT') AS A
FROM @XmlStr.nodes('//ManagerID') x(ManagerID)  

Now you can use the previous query to limit the results.

SELECT *
FROM HumanResources.Employee
WHERE ManagerID IN(
SELECT x.ManagerID.value('.', 'INT') AS A
FROM @XmlStr.nodes('//ManagerID') x(ManagerID)
)
Or you can limit the results by using an Inner Join.

SELECT *
FROM HumanResources.Employee AS A
INNER JOIN
(SELECT x.ManagerID.value('.', 'INT') AS ManagerID
FROM @XmlStr.nodes('//ManagerID') x(ManagerID)) B
ON A.ManagerID = B.ManagerID
Next Steps
I seem to be integrating XML more and more into routine tasks. I recommend at least having a basic knowledge of XML as it seems to be a more powerful tool with each release of SQL Server.
Check out more MSSQLTips related to XML.
Dynamic SQL statements
A dynamic SQL statement is constructed at execution time, for which different conditions generate different SQL statements. It can be useful to construct these statements dynamically when you need to decide at run time what fields to bring back from SELECT statements; the different criteria for your queries; and perhaps different tables to query based on different conditions.
These SQL strings are not parsed for errors because they are generated at execution time, and they may introduce security vulnerabilities into your database. Also, SQL strings can be a nightmare to debug, which is why I have never been a big fan of dynamically built SQL statements; however, sometimes they are perfect for certain scenarios.
A dynamic example
The question I answer most often is, "How can I pass my WHERE statement into a stored procedure?" I usually see scenarios similar to the following, which is not valid TSQL syntax:

DECLARE @WhereClause NVARCHAR(2000)
 SET @WhereClause = ' Prouct = ''Computer'''

 SELECT * FROM SalesHistory WHERE @WhereClause

In a perfect world, it would make much more sense to do the following:

DECLARE @Product VARCHAR(20)
 SET @Product = 'Computer'    

 SELECT * FROM SalesHistory WHERE Product = @Product
It isn't always this easy. In some scenarios, additional criteria is needed, and as tables grow wider, more and more criteria is often needed. This can typically be solved by writing different stored procedures for the different criteria, but sometimes the criteria is so different for each execution that covering all of the possibilities in a stored procedure is burdensome. While these stored procedures can be made to take into account every WHERE statement possible depending on different parameters, this often leads to a degradation in query performance because of so many conditions in the WHERE clause.
Let's take a look at how to build a simple dynamic query. First, I need a table and some data to query. The script below creates my SalesHistory table and loads data into it:

CREATE TABLE [dbo].[SalesHistory]
  (      
        [SaleID] [int] IDENTITY(1,1),  
        [Product] [varchar](10) NULL,        
        [SaleDate] [datetime] NULL,            
        [SalePrice] [money] NULL
  )
  GO

  SET NOCOUNT ON      

  DECLARE @i INT
  SET @i = 1      
  WHILE (@i <=5000)
  BEGIN                                  

         INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)                  
         VALUES ('Computer', DATEADD(ww, @i, '3/11/1919'),
         DATEPART(ms, GETDATE()) + (@i + 57))                        

         INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)  
         VALUES('BigScreen', DATEADD(ww, @i, '3/11/1927'),
         DATEPART(ms, GETDATE()) + (@i + 13))                            

         INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)    
         VALUES('PoolTable', DATEADD(ww, @i, '3/11/1908'),
         DATEPART(ms, GETDATE()) + (@i + 29))                                        

       SET @i = @i + 1    

  END

Now I will build my stored procedure that accepts a WHERE clause. For the purpose of this example, I will assume that the WHERE clause was built dynamically from the calling client application.
CREATE PROCEDURE usp_GetSalesHistory ( @WhereClause NVARCHAR(2000) = NULL ) AS
BEGIN
DECLARE @SelectStatement NVARCHAR(2000) DECLARE @FullStatement NVARCHAR(4000) SET @SelectStatement = 'SELECT TOP 5 * FROM SalesHistory ' SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'') PRINT @FullStatement EXECUTE sp_executesql @FullStatement /* --can also execute the same statement using
EXECUTE() EXECUTE (@FullStatement) */ END
I set the @WhereClause parameter to allow NULL values because we may not always want to pass a value in for the @WhereClause.
For every execution of this stored procedure, every field is returned for the TOP 5 rows from SalesHistory. If there is a value passed in for the @WhereClause parameter, the executing statement will append that string to the @SelectStatement string. Then I use the stored procedure sp_executesql to execute the dynamically built SQL string.
sp_executesql or EXECUTE()
There are two ways to execute dynamic SQL in SQL Server: use the sp_executesql system stored procedure or the EXECUTE() operator. Sometimes the two methods can produce the same result, but there are differences in how they behave.
The system stored procedure sp_executesql allows for parameters to be passed into and out of the dynamic SQL statement, whereas EXECUTE() does not. Because the SQL statement is passed into the sp_executesql stored procedure as a parameter, it is less suseptible to SQL injection attacks than EXECUTE(). Since sp_executesql is a stored procedure, passing SQL strings to it results in a higher chance that the SQL string will remain cached, which should lead to better performance when the same SQL statement is executed again. In my opinion, sp_executesql results in code that is a lot cleaner and easier to read and maintain. These reasons are why sp_executesql is the preferred way to execute dynamic SQL statements.
In my previous example, I looked at how you can build a simple SQL statement by passing a WHERE clause into a stored procedure. But what if I want to get a list of parameter values from my dynamically built SQL statement? I would have to use sp_executesql because it is the only one of my two options that allows for input and output parameters.
I am going to slightly modify my original stored procedure so that it will assign the total number of records returned from the SQL statement to an output parameter.

DROP PROCEDURE usp_GetSalesHistory
 GO
 CREATE PROCEDURE usp_GetSalesHistory
 (
         @WhereClause NVARCHAR(2000) = NULL,
         @TotalRowsReturned INT OUTPUT
 )
 AS
 BEGIN
         DECLARE @SelectStatement NVARCHAR(2000)
         DECLARE @FullStatement NVARCHAR(4000)
         DECLARE @ParameterList NVARCHAR(500)    

         SET @ParameterList = '@TotalRowsReturned INT OUTPUT'    

 SET @SelectStatement = 'SELECT @TotalRowsReturned = COUNT(*) FROM SalesHistory '
         SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')    

         PRINT @FullStatement
             EXECUTE sp_executesql @FullStatement, @ParameterList,
@TotalRowsReturned = @TotalRowsReturned OUTPUT
 END
 GO
In the above procedure, I need to declare a parameter list to pass into the sp_executesql stored procedure because a value is being assigned to the variable at run time. The only other change to the sp_executesql call is that I am assigning the output parameter from the call to the local @TotalRowsReturned parameter in my usp_GetSalesHistory stored procedure.
I can even call my usp_GetSalesHistory stored procedure similar to the way I did before, but with the addition of an output parameter to indicate the rows that were returned.

DECLARE @WhereClause NVARCHAR(2000), @TotalRowsReturned INT
 SET @WhereClause = 'WHERE Product = ''Computer'''    

 EXECUTE usp_GetSalesHistory
 @WhereClause = @WhereClause,
 @TotalRowsReturned = @TotalRowsReturned
OUTPUT

 SELECT @TotalRowsReturned
Caution
Although I am not a huge fan of using dynamic SQL statements, I believe it is a great option to have in your tool belt.
If you decide to incorporate dynamic SQL into your production level code, be careful. The code is not parsed until it is executed, and it can potentially introduce security vulnerabilities that you do not want.
If you are careful with your dynamic SQL statement, it can help you create solutions to some pretty tricky problems.