Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Execute Dynamic SQL commands in SQL Server

By:   |   Read Comments (44)   |   Related Tips: 1 | 2 | 3 | 4 | More > Dynamic SQL

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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?


Click here for video version!

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

  1. Writing a query with parameters
  2. Using EXEC
  3. 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

sql dynamic query using parameters

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)

sql server dynamic sql output

3. sp_executesql

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

sql dynamic query restult 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.

Watch Video Version of Tip
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.

Last Update:

signup button

next tip button

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

SQL tips:

*Enter Code refresh code     

Monday, November 06, 2017 - 9:31:52 PM - Joh Back To Top

 thank you ..

I was afraid of using 'sp_executesql'.

But now I love that.


Thursday, October 27, 2016 - 8:47:28 AM - lily Back To Top

 thank you :)


Thursday, September 15, 2016 - 3:19:01 PM - jthorvy Back To Top

 Just what I needed, thank you!


Tuesday, May 10, 2016 - 6:42:09 AM - rupesh Back To Top


  your solution is very simpe and useful...I like ir so much. its great thanks to you for providing such as text

Tuesday, May 03, 2016 - 10:54:58 AM - Greg Robidoux Back To Top

Thanks Tim

I agree this is not the best method for writing code and should only be used as a last resort and SQL injection should always be a concern regardless of what methods are used.


Tuesday, May 03, 2016 - 10:38:15 AM - Tim Cartwright Back To Top

 I know it wasnt the purpose of this article, but ways 2 and 3 are open to sql injection if any of those variables are user supplied. You really should mention that in more significant detail than just the next steps. That could easily be missed. I actually wrote a function to go through a string column list like your example, and apply quotes [] to the names to block sql injection.

Also, one of the main benefits to using sp_executesql over EXEC is that sql injection will be blocked for the parameters.

Wednesday, February 24, 2016 - 2:40:02 AM - Anubhuti Back To Top

 Hi Greg,

i want to count the number of records but while executing found some error.Please help

Declare @TableName Varchar(100)

Set @TableName = 'TableName'
Declare @Count int
Declare @SqlString Nvarchar(1000)

Set @SqlString = 'Select @OutCount = Count(*) From ' +@TableName
Exec sp_Execute @SqlString, N'@OutCount Int Output', @OutCount = @Count Output


Monday, July 13, 2015 - 10:32:28 AM - Greg Robidoux Back To Top


Just different ways of executing a dynamic statement.  When using sp_exectesql, this could be a little more secure since you are passing in parameter values instead of stringing the entire dynamic SQL statement together.


Monday, July 13, 2015 - 9:23:02 AM - Kris Maly Back To Top

Both '

EXEC' and 'sp_exectesql' displays the same result.

Then what is the difference?

Monday, July 13, 2015 - 9:14:05 AM - Kris Maly Back To Top

You are awesome sir

Wednesday, September 24, 2014 - 11:09:07 AM - Greg Robidoux Back To Top

Hi Ritesh,

take a look at this tip about how to create tables to see if this helps: http://www.mssqltips.com/sqlservertip/1050/simple-way-to-create-tables-in-sql-server-using-excel/


Sunday, September 21, 2014 - 11:33:02 PM - RITESH KUMAR Back To Top

how to write a sql statement and i do not know to make table plz give me detail regarding this sql statement. thank u

Monday, May 19, 2014 - 2:31:03 PM - Greg Robidoux Back To Top

Hi Raghu Iyer, you can use a WHILE loop to process through multiple items.




Monday, May 19, 2014 - 9:56:15 AM - Raghu Iyer Back To Top


Is there a way to 'continue' the execution of a query/program after generating an output through SELECT statement.


I have one procedure that accepts one parameter 'BP_Code' (Customer Code) & generates an output (statement) as a text file for that 'BP_Code'.

now, I would like to call that procedure multiple times for all the BP_Code in a list.

But, as we know, the execution stops after the output is generated by the 'SELECT' statement in the procedure, so, it generates the statement only once for the first BP_Code.

is there anyway to put the procedure in a loop ?

Thanks & Regards,


Tuesday, February 04, 2014 - 8:41:27 AM - david Back To Top

Hello Greg,

 I must develop a stored procedure in a dynamic way. But the operand of the "where" clause must be a parameter. A priori I don't know what kind of comparission will be submited (for example, amount = 1000 in a execution and amount > 250 in another).

I think that Dynamic SQL is the solution, but we consider this one not enough "elegant" (and the Sql injection issue  too...)

Any idea?

Thank you very much.


Thursday, January 30, 2014 - 7:46:31 AM - Dinesh Back To Top

Hi Manish, How do I get your sql command as a output to the other stored procedure

Monday, July 15, 2013 - 1:16:15 PM - Joćo Gonzales Back To Top

Fantastic Greg, congratulations. solution simple and efficient...

Sorry, i dont speak inglish...



Friday, June 28, 2013 - 12:08:27 PM - Scott Coleman Back To Top

You did not mention using :SETVAR in scripts running in SQLCMD mode.  It can't be used to create dynamic procedures (any CREATE PROCEDURE would have a static definition based on the :SETVAR values in effect originally), but it can be used for some very powerful dynamic scripts.
These variables can be used anywhere, in strings, as server, table, or database name, or even parts of names.
The variable definition is active for the entire script, even across GO. 

INSERT INTO dbo.$(TBL)_copy
SELECT * FROM dbo.$(TBL)_original

:SETVAR DB MyDatabase
SELECT * FROM $(SRV).$(DB).dbo.$(TBL)

You can write multi-server scripts, like a database copy.  (GO required before a second :CONNECT)

:SETVAR DB MyDatabase
:SETVAR BakUrl \\fileserv\share\migration\temp.bak
-- Simple example, in reality WITH MOVE clauses may be required 

To answer one of the previous questions:

Sandeep: Hi,

I want to store the result of a dynamic query into a variable, assuming the query returns only 1 value.

EXEC sp_executesql N'SELECT @var = somevalue
FROM sometable WHERE key = @key', N'@key INT, @var INT OUTPUT', @variable OUTPUT ;
print @variable

Obviously the dynamic query is going to be more complicated, in this example there is no reason to use sp_executesql.  But the point is that sp_executesql can handle OUTPUT parameters.

Thursday, May 02, 2013 - 9:24:52 PM - Richard Back To Top

Is there anyway to see the actual SQL state being created with the parameters actually substituted.


I'm not getting the results I expected and cant tell what the problem is




Friday, March 29, 2013 - 8:19:54 AM - Greg Robidoux Back To Top

@Mani - the reason that the @city variable is declared twice is because it is used outsite of the sp_executesql and also within the sp_executesql.  It is a little confusing that I used the same name twice.  Hopefully that helps answer your question.

Friday, March 29, 2013 - 8:17:48 AM - Greg Robidoux Back To Top

@Francisco - try something like this.  Not sure if this is exactly what you need to do or not.

Declare @Month Int = 1
Declare @test2 Nvarchar(255) =''

set @test2 = @Month
Select @test2 = (Case @test2
When 1 then 'December'
When 2 then 'January'
When 3 then 'February'
When 4 then 'March'
When 5 then 'April'
When 6 then 'May'
When 7 then 'June'
When 8 then 'July'
When 9 then 'August'
When 10 then 'September'
When 11 then 'October'
When 12 then 'November'
 end )
Declare @test1 Nvarchar(255) = @Test2+'_AvgNetP'
Declare @test3 Nvarchar(255) = @Test2+'_AvgROS'
Declare @Select nvarchar(1000)
Declare @Select2 nvarchar(1000)

Set @Select = 'Select Hdl_Nr,' + @test1 + ',' + @test3 + ' from [Table1] as T'
print @select

set @Select2 = 'update t2 set t2.ROS_S = t1.' + @test1 + ' from Table2 t2 inner join Table1 t1 on t1.Hdl_Nr = t2.Hdl_Nr'
print @select2
exec (@Select2)

Friday, March 29, 2013 - 7:22:22 AM - Mani Back To Top
Hi frnds,
i have a doubt in the below example:
initally u r declared datatype for @city, then why u are using the samething at EXECUTE statement like N'@city nvarchar(75)'? what is the purpose? plz expain..
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


Monday, March 04, 2013 - 7:09:27 AM - Francisco Back To Top

I have a Dynamic select, I want to choose dynamically the columns of table 2 who have names as a month but I dont want to use the complete name when I call them with SSRS, my question is how to save the results of this Dynamic Select in Table 2?I can not do it can someone help me.

Declare @Month Int = 1
Declare @test2 Nvarchar(255) =''

Set @test2 = @Month
Select @test2 = (Case @test2
When 1 then 'December'
When 2 then 'January'
When 3 then 'February'
When 4 then 'March'
When 5 then 'April'
When 6 then 'May'
When 7 then 'June'
When 8 then 'July'
When 9 then 'August'
When 10 then 'September'
When 11 then 'October'
When 12 then 'November'
 end )
Declare @test1 Nvarchar(255) = @Test2+'_AvgNetP'
Declare @test3 Nvarchar(255) = @Test2+'_AvgROS'
Declare @Select nvarchar(1000) 

 Set @Select =
 'Select Hdl_Nr,' +@test1+','+@test3+
 ' from [Table1] as T
Update Table2
set Table2.ROS_S = (Select @test1 from @Select)
where Table2.Hdl_Nr = T.Hdl_Nr) '

exec SP_EXECUTESQL @Select



Wednesday, February 20, 2013 - 6:20:35 AM - sandeep Back To Top


I want to store the result of a dynamic query into a variable, assuming the query returns only 1 value.

Everywhere it tell me to store the result into a temp table and then query the temp table to store the value into a variable.

Can anybody please help me if there is any easier way to directly put the result into a variable, just like how mysql lets you with keyword into @variable in its dynamic query.

Tuesday, February 12, 2013 - 6:51:23 PM - Scott Keith Back To Top

I don't know how, but the Execute statement is now working. Please disregard my previous post.

Tuesday, February 12, 2013 - 6:19:51 PM - Scott Keith Back To Top

I can execute my dynamic SQL statement, but when I use it in a stored procedure, I can't get at the data. As a simple example, when I run the following in a query window, it returns a set of data:


But when I put the same statement in a stored procedure and try to return the set of data, calling the stored procedure just gives me:

Command(s) completed successfully.

How do I get the stored procedure to return the result set from the dynamic query?

Tuesday, February 12, 2013 - 7:58:45 AM - Greg Robidoux Back To Top

@Manish Kumar - here is simple code to do this:


create table #temp (sqlcommand varchar(500))
insert into #temp
select 'drop table  AccountID_55406' union all
select 'drop table  Accountid_70625'

DECLARE @sqlcommand varchar(500)

SELECT sqlcommand FROM #temp ORDER BY 1

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @sqlcommand  

       PRINT @sqlcommand
       EXEC (@sqlcommand)
       FETCH NEXT FROM db_cursor INTO @sqlcommand 

CLOSE db_cursor  
DEALLOCATE db_cursor


Tuesday, February 12, 2013 - 2:53:31 AM - Manish Kumar Back To Top



I have a table in ehich column having some dml commands.


Could please tell me how to execute these commands in sql server.


Please have look on snap shot attached


Column 1
drop table  AccountID_55406
drop table  Accountid_70625
drop table  Accountid_59234
drop table  AccountID_63715
drop table  AccountID_62836
drop table  AccountID_68989

Thursday, January 31, 2013 - 11:12:29 PM - Deepchand Back To Top


I usually write queries whose ouptput itself is a query.Is there a way to execute the ouptut of the query without copy pasting and runing it?

Friday, January 25, 2013 - 2:35:23 PM - Jeremy Kadlec Back To Top


Have you tried this tool?


Thank you,
Jeremy Kadlec
Community Co-Leader

Friday, January 25, 2013 - 2:14:32 PM - Pratibha Gaur Back To Top

lets say i have written a stored procedure.Later i realized that some of keywords within the stored proc are in upper case and some in lower case,now to give it a standard look i want to change all the lowercase keywords into uppercase.For that i need a query or stored proc.I was trying but couldn't find out how to get all the keywords used within a stored proc.Would be very thankfull if you could help me... :-)


Thursday, December 13, 2012 - 8:44:53 AM - Harish Back To Top

i want to execute this SQL command:select * from CountryName where countryName like 's%'

 using exec()


****Please tell me solution

Saturday, September 29, 2012 - 9:08:39 AM - Greg Robidoux Back To Top

@Roberto - this isn't exactly true.  If you create the Temp Table first and then select data into it using EXEC you can then use SELECT to read the data.

Here is an example:

  [name] [sysname] NOT NULL,
  [object_id] [int] NOT NULL  )

SELECT name, object_id FROM sys.objects')

EXEC ('SELECT * FROM #temp')



Friday, September 28, 2012 - 5:50:09 PM - Roberto Iglesias Back To Top

Here is my contribution:

Always remember that anything called by EXEC statement is executed in a separated session. So you can't use:


And then call SELECT * FROM #TMP. To do so, you must create a global temporary table:


That's it. =)

Friday, August 31, 2012 - 11:19:20 AM - smit Back To Top

How to build an sql query in fly?

I have 4 textbox firstname, middlename, lastname and city. User will enter data in any of the four textbox during runtime. So the problem is, on submit I have to build an sql query during run time for my asp.net application to search for records in my Database only for the entries which the user has eneterd.


Friday, August 24, 2012 - 8:22:27 AM - Greg Robidoux Back To Top

@Vishal - what are you trying to do with this code? 

Did you try to change sp_execute with sp_executesql?

Friday, August 24, 2012 - 7:23:38 AM - Vishal Bhilare Back To Top


I wisht to fetch out the total record count from the Table. The following syntax gives me error. Can some one help me on the same.

Declare @TableName Varchar(100)

Set @TableName = 'TableName'
Declare @Count int
Declare @SqlString Nvarchar(1000)

Set @SqlString = 'Select @OutCount = Count(*) From ' +@TableName
Exec sp_Execute @SqlString, N'@OutCount Int Output', @OutCount = @Count Output



Wednesday, August 15, 2012 - 8:34:32 AM - whistler Back To Top

Hey Lillian,

Why don't you just create a subquery


DECLARE @PostalCode varchar(20)

SELECT Last_Name, FirstName









Wednesday, August 01, 2012 - 9:20:19 AM - Lillian Back To Top

I need to develop a "generic" statement that works in various databases.  Let's say there are three DBs for each of our branch offices, namely HAMMOND, ROCKVILLE, and RIDGEMOUNT.  Each DB has the same set of table names, e.g. dbo.PERSON and same field names, e.g. LAST_NAME, FIRST_NAME, POSTAL_CODE.  There is a fourth DB where all stored procedures are housed, e.g. HQIntegration.  My stored procedure has to allow user of the branch office to grab the data pertaining to the branch location

For user in Hammond ... 


For user in ROCKVILLE ...


I wrote:

DECLARE @DBName varchar(10)


DECLARE @SQL varchar(max)

SET @SQL = '



FROM '+@DBName+'.dbo.PEROSN




Tuesday, July 24, 2012 - 8:21:46 PM - Angelo01 Back To Top

oops...can't edit....



Thanks a LOT Greg,


YOU really SAVED my DAY!

Been working on an issue with an EXEC statement for hours now.

Could have turn into days if I havent found your Blog


Thank you for sharing the info.





looks better now

Friday, February 17, 2012 - 5:30:12 PM - Miguel Pena Back To Top
I'm trying to get a SQL formula result: DECLARE @Amount DECIMAL(12,2) DECLARE @Formula NVARCHAR(100) DECLARE @Result DECIMAL(12,2) SET @Amount = 1000 SET @Fomula = N'ROUND(@Amount/1.16,2)' EXEC @Result = sp_executesql @Formula but when i execute it i receive the followin error: Msg 137, Level 15, State 1, Line 6 Must declare the scalar variable "@Fomula". Help me Please, mp

Wednesday, November 17, 2010 - 5:44:45 AM - Harsha Back To Top


I have a question regarding dynamic sql.


What would be difference between the 2 query


declare @script nvarchar(1000),
             @companyid int,
             @area tinyint

select comapnyid = 1 , @area = 1

select @script = 'select contactname , address, etc'+
                    + 'from tbljcontactstable' + convert(varchar(4) , @companyid)
                    + 'WHERE contact_area = ' +convert(varchar(4) , @area)




declare @script nvarchar(1000),
             @companyid int,
             @area tinyint

select comapnyid = 1 , @area = 1

SELECT @script = ''
SELECT @script = @script + 'select contactname , address, etc'
select @script = @script +  'from tbljcontactstable<comapnyid>'
select @script = @script +  'WHERE contact_area = <area>'
SELECT @script = REPLACE(@script, '<comapnyid>' , @companyid)
SELECT @script = REPLACE(@script, '<area>', @area)



Friday, May 09, 2008 - 2:47:59 PM - admin Back To Top


Thank you for the contribution.

Thank you,
The MSSQLTips.com Team

Sunday, April 20, 2008 - 12:24:52 AM - Mazharuddin Back To Top
Hi,I just discovered another benefit of using sp_executesql to execute the dynamic SQL.The Exec fails to work in case if the  SQL statement is lengthy (it obviously has a limitation of length)The same SQL statement works with Exec sp_executesqlBest regards,


Learn more about SQL Server tools