Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Execute Dynamic SQL commands in SQL Server

MSSQLTips author Greg Robidoux By:   |   Read Comments (34)   |   Related Tips: 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 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

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_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

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: 10/15/2012


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, September 24, 2014 - 11:09:07 AM - Greg Robidoux Read The Tip

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/

Thanks
Greg


Sunday, September 21, 2014 - 11:33:02 PM - RITESH KUMAR Read The Tip

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 Read The Tip

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

 

http://technet.microsoft.com/en-us/library/ms178642.aspx

 


Monday, May 19, 2014 - 9:56:15 AM - Raghu Iyer Read The Tip

Hi,

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

e.g.

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,

Kr


Tuesday, February 04, 2014 - 8:41:27 AM - david Read The Tip

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 Read The Tip

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 Read The Tip

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

Sorry, i dont speak inglish...

 

Tks,


Friday, June 28, 2013 - 12:08:27 PM - Scott Coleman Read The Tip

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. 

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

:SETVAR SRV MyServer
: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 SOURCE SERVERA
:SETVAR DESTINATION SERVERB
:SETVAR DB MyDatabase
:SETVAR BakUrl \\fileserv\share\migration\temp.bak
:CONNECT $(SOURCE)
BACKUP DATABASE $(DB) TO DISK='$(BakUrl)' WITH CHECKSUM, STATS=10
GO
:CONNECT $(DESTINATION)
RESTORE DATABASE $(DB) FROM DISK='$(BakUrl)' WITH CHECKSUM, REPLACE, STATS=10
-- 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 Read The Tip

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

 

Thanks,

Richard


Friday, March 29, 2013 - 8:19:54 AM - Greg Robidoux Read The Tip

@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 Read The Tip

@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'
else
NULL
 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 Read The Tip
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 Read The Tip

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'
else
NULL
 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 Read The Tip

Hi,

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 Read The Tip

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 Read The Tip

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:

EXECUTE(N'SELECT Mox FROM dbo.MoYrTmp')

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 Read The Tip

@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)

DECLARE db_cursor CURSOR FOR 
SELECT sqlcommand FROM #temp ORDER BY 1

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @sqlcommand  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       PRINT @sqlcommand
       EXEC (@sqlcommand)
      
       FETCH NEXT FROM db_cursor INTO @sqlcommand 
      
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

 


Tuesday, February 12, 2013 - 2:53:31 AM - Manish Kumar Read The Tip

Hi,

 

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 Read The Tip

 

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 Read The Tip

Pratibha,

Have you tried this tool?

http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, January 25, 2013 - 2:14:32 PM - Pratibha Gaur Read The Tip

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 Read The Tip

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 Read The Tip

@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:

CREATE TABLE #temp (
  [name] [sysname] NOT NULL,
  [object_id] [int] NOT NULL  )

EXEC ('INSERT INTO #temp
SELECT name, object_id FROM sys.objects')

EXEC ('SELECT * FROM #temp')

SELECT * FROM #temp

 


Friday, September 28, 2012 - 5:50:09 PM - Roberto Iglesias Read The Tip

Here is my contribution:

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

EXEC 'SELECT * INTO #TMP FROM USERS'

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

EXEC 'SELECT * INTO ##TMP FROM USERS'

That's it. =)


Friday, August 31, 2012 - 11:19:20 AM - smit Read The Tip

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.

Thanks.


Friday, August 24, 2012 - 8:22:27 AM - Greg Robidoux Read The Tip

@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 Read The Tip

Hi

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 Read The Tip

Hey Lillian,

Why don't you just create a subquery

e.g.

DECLARE @PostalCode varchar(20)

SELECT Last_Name, FirstName

FROM (SELECT Last_Name, First_Name FROM HAMMOND.dbo.PERSON

UNION ALL

SELECT Last_Name, First_Name FROM RIDGEMOUNT.dbo.PERSON

UNION ALL

SELECT Last_Name, First_Name FROM ROCKVILLE.dbo.PERSON

) DRV WHERE POSTAL_CODE = @PostalCode


 


 


Wednesday, August 01, 2012 - 9:20:19 AM - Lillian Read The Tip

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 ... 

SELECT LAST_NAME, FIRST_NAME FROM HAMMOND.dbo.PERSON WHERE POSTAL_CODE = '12345'

For user in ROCKVILLE ...

SELECT LAST_NAME, FIRST_NAME FROM ROCKVILLE.dbo.PERSON WHERE POSTAL_CODE = '98765'

I wrote:

DECLARE @DBName varchar(10)

DECLARE @POSTALCODE varchar(20)

DECLARE @SQL varchar(max)

SET @SQL = '

SELECT LAST_NAME

, FIRST_NAME

FROM '+@DBName+'.dbo.PEROSN

WHERE POSTAL_CODE = '''+@POSTALCODE+''''

EXEC (@SQL)

 


Tuesday, July 24, 2012 - 8:21:46 PM - Angelo01 Read The Tip

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.

 

BIIIIIIIIIIG THANKS once again.

 

 

looks better now


Friday, February 17, 2012 - 5:30:12 PM - Miguel Pena Read The Tip
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 Read The Tip

Hi,

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)

 


exec(@script)

AND

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)

exec(@script)

 


Friday, May 09, 2008 - 2:47:59 PM - admin Read The Tip

Mazharuddin,

Thank you for the contribution.

Thank you,
The MSSQLTips.com Team


Sunday, April 20, 2008 - 12:24:52 AM - Mazharuddin Read The Tip
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,

Mazharuddin




 
Sponsor Information







 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Top Ten

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.