Execute Dynamic SQL commands in SQL Server

By:   |   Updated: 2021-07-06   |   Comments (66)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Dynamic SQL


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:

sql dynamic query using parameters

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:

sql server dynamic sql output

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:

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

Watch Video Version of Tip
Next Steps
  • If at all possible, try to avoid 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.
  • Consider some static SQL DML (Data Manipulation Language) approaches including:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2021-07-06

Comments For This Article




Tuesday, November 26, 2024 - 4:44:26 PM - Sharon Warrren Back To Top (92652)
The problem is you can't use this stored procedure as a table in a query. I was looking for dynamic sql in a view, ussing pivot stuff function :(

Friday, September 22, 2023 - 5:28:16 PM - Greg Robidoux Back To Top (91588)
Thanks Jeff for the input.

Yes these are very simplistic examples and additional steps should be in place to check the incoming values.

Friday, September 22, 2023 - 5:15:18 PM - Jeff Moden Back To Top (91587)
I realize the author posted links to "SQL Injection" articles but, just in case you're not one of the ones to take such time, make sure that you "split" the column list and check the individual column names against sys.columns for the given object and make sure that the only punctuation contained in the column list variable are commas.

***** DO NOT USE THE CODE IN THIS ARTICLE ***** without such protective modifications.

Sunday, May 8, 2022 - 4:01:02 PM - Kevin Martin Back To Top (90068)
It is really hard to do dynamic SQL safely and performant. You can reverse engineer the stored procedure generated by sp_CRUDGen to get some dynamic SQL best practices.

sp_CRUDGen Links:
YouTube Playlist: https://www.youtube.com/pla...
GitHub Repository Project: https://github.com/kevinmar...
Blog Articles: http://spcrudgen.org

Saturday, February 27, 2021 - 8:54:33 AM - Greg Robidoux Back To Top (88311)
Try this. You had an extra ) in the code.

strQuery = "SELECT tblAppointments.AppID, tblAppointments.AppointDate, tblAppointments.AppointTime, Left([tblSchedule].[DoctorsName],5) AS Doctor, tblSchedule.DoctorsName FROM tblSchedule INNER JOIN tblAppointments ON tblSchedule.DoctorsID = tblAppointments.DoctorsID WHERE tblAppointments.AppointDate >= GETDATE();"

Saturday, February 27, 2021 - 6:39:47 AM - Engr. Matthew Ikechukw Back To Top (88310)
the string did not work

strQuery = "SELECT tblAppointments.AppID, tblAppointments.AppointDate, tblAppointments.AppointTime, Left([tblSchedule].[DoctorsName],5) AS Doctor, tblSchedule.DoctorsName FROM tblSchedule INNER JOIN tblAppointments ON tblSchedule.DoctorsID = tblAppointments.DoctorsID WHERE tblAppointments.AppointDate)>=GETDATE();"

is there a way to send to you the project itself for you to take a look at. I really want to learn what I did wrong in this matter.

thanks for your time

Friday, February 19, 2021 - 10:53:59 AM - Stop Naming Nuts Back To Top (88266)
I appreciate the ColdFusion mention. It's kooky, it's not popular and Adobe has never figured out to market it. But CF quietly onboards new related technologies (like microservices) and remains one of the most secure server-side platforms in the market.

Wednesday, February 3, 2021 - 9:37:32 AM - Greg Robidoux Back To Top (88143)
Hi Matthew, thy this.

strQuery = "SELECT tblAppointments.AppID, tblAppointments.AppointDate, tblAppointments.AppointTime, Left([tblSchedule].[DoctorsName],5) AS Doctor, tblSchedule.DoctorsName FROM tblSchedule INNER JOIN tblAppointments ON tblSchedule.DoctorsID = tblAppointments.DoctorsID WHERE tblAppointments.AppointDate)>=GETDATE();"

Wednesday, February 3, 2021 - 9:15:22 AM - Matthew Ikechukwu Back To Top (88142)
strQuery = "SELECT tblAppointments.AppID, tblAppointments.AppointDate, tblAppointments.AppointTime, Left([tblSchedule].[DoctorsName],5) AS Doctor, tblSchedule.DoctorsName FROM tblSchedule INNER JOIN tblAppointments ON tblSchedule.DoctorsID = tblAppointments.DoctorsID WHERE (((tblAppointments.AppointDate)>=Date()));"

this SQL String work very well in Access database after the Access Database is moved to SQL the query string is not work

Please can you help me to look into the string

Thursday, February 6, 2020 - 10:56:54 AM - Greg Robidoux Back To Top (84219)

Hi Erik,

looks like you cannot pass in a parameter that way for that clause.  You can try this.

declare @myparam int = 6;  select  @myparam
declare @sql varchar(max)
set @sql = 'select VDate, Value, AVG(MyValue) OVER (ORDER BY MyDate ROWS BETWEEN '  + convert(varchar(10),@myparam) + ' PRECEDING AND 0 FOLLOWING) myval from MyTable'
print @sql
exec (@sql)

Thursday, February 6, 2020 - 8:33:50 AM - Erik Søberg Back To Top (84213)

--The below code works fine hardcoding with a number like 6 to get the moving average(6), But I want to use the @myparam so I can reuse the same function to get moving average (3) or (12) ie. but my code below doeas not accept the parameter.

Any ideas?

declare @myparam int = 6;  select  @myparam

select VDate, Value,

AVG(MyValue) OVER (ORDER BY MyDate ROWS BETWEEN @myparam PRECEDING AND 0 FOLLOWING) myval

from  MyTable


Wednesday, December 4, 2019 - 2:01:16 PM - Elkin Back To Top (83287)

Hola Greg.

si estamos de acuerdo. En el SSMS funciona. Un ejemplo de la formula es : a.arpAncho-(2*L.apzCalibre)-1, donde cada campo , Ancho y Calibre son Medidas de una Pieza de madera rectangular, es una medida que se encuentra en una tabla.

Puede ser un error mio al colocar la instruccion. Pero estas estan bien construidas y validadas por el programa.

El problema es cuando este bloque de instrucciones se coloca en un proc almacenado en un segundo nivel, llamado por otro. Es ahi donde se queda en un proceso indefinido.

Tienes alguna idea de que puede estar pasando?

Gracias nuevamente.

Elkin


Wednesday, December 4, 2019 - 1:49:31 PM - Greg Robidoux Back To Top (83286)

Hi Elkin, I tried this and it works in SSMS, but I had to change the fomula as follows:

from: SET @Valor_OUT=983.14-2(15.5)+1

to: SET @Valor_OUT=983.14-2*(15.5)+1

DECLARE @ValorFrm NVARCHAR(500) = 'SET @Valor_OUT=983.14-2*(15.5)+1'

DECLARE @SqlString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @Valor_Tmp Numeric(12,2)
SET @SqlString=LTRIM(RTRIM(@ValorFrm))
SET @ParmDefinition = N'@Valor_OUT Numeric(12,2) OUTPUT'

EXECUTE sp_executesql @SqlString,@ParmDefinition,@Valor_OUT=@Valor_Tmp OUTPUT

SELECT @Valor_Tmp


Wednesday, December 4, 2019 - 12:33:37 PM - Elkin Back To Top (83284)

 Gracias Greg por responder.

Lo que busco es el total de esa operacion compuesta. Tengo una aplicacion con unas formulas generadas por el usuario. Que puede ser (a.arpLargo-2*(BS.apzCalibre)-1. He construido unos procedimientos almacenados en el motor que interpretan esta formula y la convierten a numeros quedando de la siguiente forma : 983.14 - 2*(15.5) +1

Quiero obtener el total de esa operacion mediante el procedimiento sp_executesql.

Pero este me funciona en el SSMS y no funciona en el procedimiento interno que es llamado por otro procedimiento el cual devuelve dicho total.

Ej El Proc A llama el Proc B. En el Proc B esta este bloque de instrucciones. El Proc B Devuelve el Total de esta operacion al Proc A. 

Espero ser claro. Mil Gracias por tu ayuda y abrazos desde medellin, colombia.

 

Elkin

*** NOTA *** - Si desea incluir código de SQL Server Management Studio (SSMS) en su publicación, copie el código de SSMS y péguelo en un editor de texto como NotePad antes de copiar el código a continuación para eliminar el Formateo SSMS.


Wednesday, December 4, 2019 - 12:20:38 PM - Greg Robidoux Back To Top (83282)

Hi Elkin,

can you give me an idea of what you are trying to do.  What values are you passing in and what values to you want to see output?


Wednesday, December 4, 2019 - 11:12:14 AM - Elkin Back To Top (83279)

Cordial Saludo.

Tengo el siguiente script

DECLARE @SqlString NVARCHAR(500)

DECLARE @ParmDefinition NVARCHAR(500)

DECLARE @Valor_Tmp Numeric(12,2)

SET @SqlString=LTRIM(RTRIM(@ValorFrm))

SET @ParmDefinition = N'@Valor_OUT Numeric(12,2) OUTPUT'

EXECUTE sp_executesql @SqlString,@ParmDefinition,@Valor_OUT=@Valor_Tmp OUTPUT

SET @Valor=@Valor_Tmp

La variable @ValorFrm='SET @Valor_OUT=983.14-2(15.5)+1' Es una interpretacion de unas variables convertidas a numero. Esto puede ser a+2(b)+c

El problema es que en el (SSMS) funciona. Pero mas adentro en un procedimiento secundario no funciona y se queda el equipo ejecutando la consulta indefinidamente. Este bloque se encuentra en el procedimiento 2 el cual es invocado por el procedimiento 1.

Que cuidados debo de tener en cuenta para que esto funcione correctamente a tan bajo nivel?

Muchas gracias por su ayuda.  / elkin / Medellin colombia


Tuesday, April 9, 2019 - 9:52:39 AM - Greg Robidoux Back To Top (79507)

Thanks Doug.  I agree I could further elaborate on some of this as well as provide pros and cons.  I will try to update this in the near future.  Also, I agree the first example isn't truly dynamic SQL, but it shows how to create a query that can be changed using parameters versus hardcoding items.  I think this is helpful to new people to show there is an easy way to do this without having to build a long query string and then executing the assembled string.

Thanks
Greg


Tuesday, April 9, 2019 - 9:27:16 AM - Doug Back To Top (79505)

Thanks for the tip. However, I think you've done a bit of disservice to the community for not going into the pros and cons of each. Also, I would be VERY hard-pressed to call the first example dynamic SQL. If that truly is dynamic SQL, then every stored procedure I've ever written is done using dynamic SQL (okay, maybe 95%, since perhaps I've written a few that don't have parameters.

Parameterized queries (especially if they've been made into stored procedures) are the safest and best way to go. As a stored procedure, they can take advantage of plan caching, which can result in faster execution times. Furthermore, they are not inherently subjected to SQL injection, which can reek havoc on a database.

I tend to shy away from EXEC like the plague, unless I am using it within the body of a stored procedure, using either no parameters, or parameters that I've derived from data generated within the procedure, but NEVER with passed parameters. This is the EASIEST way to invoke SQL injection which, if I didn't mention before, can reek havoc on a database.

If your code does need to be dynamic (i.e. the query itself is changing based on parameters that are being passed to it--such as the source table in the FROM clause changes based on whether you are pulling data from US or UK), then building the code in a stored procedure, and executing it using sp_executesql is by far the safest way of building and executing your code.


Tuesday, March 12, 2019 - 10:54:02 AM - Greg Robidoux Back To Top (79271)

Hi Fordraiders,

did you try to just add your INSERT into your dynamic query.

@SQL = 'INSERT INTO Work_Flow.dbo.Customer_Calendar (leavetype, leavereason) SELECT *


Tuesday, March 12, 2019 - 10:20:43 AM - fordraiders Back To Top (79269)

I have this Dynamic sql query working fine.

DECLARE @StartDate AS VARCHAR(10), @SQL NVARCHAR(MAX); SET @StartDate = '01-JAN-19'; SET @SQL = 'SELECT * FROM OPENQUERY(XREF_PROD, ''SELECT leavetype, leavereason FROM XREF.XREF_CALENDER WHERE createdon >= ''''' + @StartDate + ''''''')'; EXEC sp_executesql @SQL;

I need to take this result now and INSERT it into table on sql server.

Database name = Work_Flow

Table Name= Customer_Calendar

Fields = leavetype, leavereason

I have looked at kinds of examples on the internet..but gets confusing because most of the examples use a temp table.

Thanks fordraiders


Tuesday, November 28, 2017 - 9:15:10 AM - Greg Robidoux Back To Top (73334)

Hi Ahmad,

Try to use a ##temp (global) table instead of a #temp (local) table.

-Greg


Monday, November 27, 2017 - 3:37:44 AM - Ahmad Back To Top (73272)

Hello Sir,

Please assist me with this problem i seemed not knowing way forward!

I have a stored procedure using dynamic SQL to execute some commands at runtime, and use INSERT INTO statement to temporarily keep the output of parameterized executesql in a temporary table. 

I needed to modify some contents of the  temporary table and limit the content at some point. This works perfectly fine on the management studio.

The problem is, the same procedure is returning no data when it's called from a Java application. I'd appreciate any assistance from you. Thank you 

Below is my procedure:

CREATE PROCEDURE [dbo].[usp_calloverchanges_auditreport_Under_Perfection] 

@changeType varchar(50), @clientId_fromApp int, @startdate_fromApp date, @enddate_fromApp date, @requster varchar(50), @authoriser varchar(50),

@startHolding numeric(18, 0), @endHolding numeric(18, 0)

AS

declare @SQLString nvarchar(max);

declare @searchVariable int;

DECLARE @ParmDefinition nvarchar(500); 

 

Create table #finalrecord ( holder_id int, [Account Number] int, [Shareholder Name] varchar(500) 

, [Previous Mandate] varchar(500), [New Mandate] varchar(500) 

, [Current Holdings] numeric(18, 0), [Affected Register] varchar(200)

, [Requester] varchar(200), [Authoriser] varchar(200)

, [Change Type] varchar(50), [Change Date] date)

 

declare @clientId int = 0; 

declare @startdate date;

declare @enddate date;

 

BEGIN

SET NOCOUNT ON;

Declare @cols varchar(1000) = N'hc.holder_id, hc.h_comp_acct_id as [Account Number]

, h.last_name + '' '' + h.first_name + '' '' + h.middle_name as [Shareholder''s Name]

, isnull(hc.initial_form, ''N/A'') as [Previous Mandate]

, isnull(hc.current_form, ''N/A'') as [New Mandate]

, hca.total_share_units as [Current Holdings]

, isnull(account_affected, '''') as [Affected Register]

, ISNULL(change_initiator, ''N/A'') as [Requester], ISNULL(change_authoriser, ''N/A'') as [Authoriser] 

,  hct.change_type as [Change Type], hc.change_date as [Change Date]';

 

Declare @subquery varchar(500) = N' FROM HOLDER_CHANGES hc Join HOLDER_CHANGE_TYPE hct 

On hct.id = hc.change_type_id

Join HOLDER h

On hc.holder_id = h.id

Join HOLDER_COMPANY_ACCOUNT hca

On hca.id = hc.h_comp_acct_id

And hca.holder_id = hc.holder_id

Join CLIENT_COMPANY cc

On cc.id = hca.client_Company_Id WHERE';

 

-- check the audit trail: EDMMS

IF (@changeType = 'edmms')

BEGIN

If (@clientId_fromApp > 0)

Begin

-- if the enddate is set, this means user is searching by two dates, hence, there is no check for startdate here

if(@enddate_fromApp is not null) 

begin

if(@startdate_fromApp is not null)

begin

SET @SQLString = ('Select ' + @cols + ' '+ @subquery + ' ' + ' cc.id = @ccId' + ' AND ' + 'hc.change_type_id in (5, 6, 15, 16, 19)' + ' AND '

+ 'hc.change_date BETWEEN' + ' ' +'@StartDate_str ' + ' AND ' + ' @EndDate_str');

 

set @ParmDefinition = N'@ccId int, @StartDate_str DATE, @EndDate_str DATE';

set @clientId = @clientId_fromApp; 

set @startdate = @startdate_fromApp;

set @enddate = @enddate_fromApp;

 

INSERT INTO #finalrecord

EXEC sp_executesql @SQLString, @ParmDefinition

, @ccId = @clientId, @StartDate_str = @startdate, @EndDate_str = @enddate;

end

else

begin

Print 'THE START DATE ENTERED BY THE USER WHILE SEARCHING WITH DATE RANGE, IS EITHER NULL OR EMPTY , PLEASE CONTACT SYSTEM ADMINISTRATOR!!!';

end

end

else if (@enddate_fromApp is null And @startdate_fromApp is not null) -- once the enddate is not set, check if the start date is set and search by a date

begin

SET @SQLString = ('Select ' + @cols + ' '+ @subquery  + ' ' + 'cc.id = @ccId' + ' AND ' + 'hc.change_type_id in (5, 6, 15, 16, 19)' + ' AND '

+ 'hc.change_date = @StartDate_str');

 

set @ParmDefinition = N'@ccId int, @StartDate_str DATE';

set @clientId = @clientId_fromApp;

set @startdate = @startdate_fromApp;

 

INSERT INTO #finalrecord

EXEC sp_executesql @SQLString, @ParmDefinition, @ccId = @clientId, @StartDate_str = @startdate;

end

else-- filter the query search by only client company identifier

begin

SET @SQLString = ('Select ' + @cols + ' '+ @subquery + ' ' + 'cc.id = @ccId' + ' AND ' + 'hc.change_type_id in (5, 6, 15, 16, 19)');

Set @ParmDefinition = N'@ccId int';

Set @clientId = @clientId_fromApp;

 

INSERT INTO #finalrecord

EXECUTE sp_executesql @SQLString, @ParmDefinition, @ccId = @clientId

end  --end block of codes for client company identifier being set

End

Else-- else no client identifier is sent from application, hence use only date(s)

Begin

if(@enddate_fromApp is not null) 

begin

SET @SQLString = ('Select ' + @cols + ' '+ @subquery + ' ' + 'hc.change_type_id in (5, 6, 15, 16, 19)' + ' AND '

+ 'hc.change_date BETWEEN' + ' ' +'@StartDate_str ' + ' AND ' + ' @EndDate_str');

 

set @ParmDefinition = N'@StartDate_str DATE, @EndDate_str DATE'; 

set @startdate = @startdate_fromApp;

set @enddate = @enddate_fromApp;

 

INSERT INTO #finalrecord

EXEC sp_executesql @SQLString, @ParmDefinition, @StartDate_str = @startdate, @EndDate_str = @enddate;

end

else-- only the start date is sent from engine

begin

SET @SQLString = ('Select ' + @cols + ' '+ @subquery + ' ' + 'hc.change_type_id in (5, 6, 15, 16, 19)' + ' AND '

+ 'hc.change_date = @StartDate_str');

 

set @ParmDefinition = N'@StartDate_str DATE';

set @startdate = @startdate_fromApp;

 

INSERT INTO #finalrecord

EXEC sp_executesql @SQLString, @ParmDefinition, @StartDate_str = @startdate;

end

End

END --end report type for EDMMS

 

-- narrow down the report based on the requester or authoriser, or both

if((@requster is not null) and (@authoriser is null))

begin

Select [Account Number], [Shareholder Name] 

, [Previous Mandate], [New Mandate]

, [Current Holdings], [Affected Register]

, [Requester], [Authoriser]

, [Change Type], [Change Date]

from #finalrecord Where Requester like '%'+@requster+'%' 

order by [Change Date] asc, holder_id asc

end

else if ((@authoriser is not null)  and (@authoriser is null))

begin

Select [Account Number], [Shareholder Name] 

, [Previous Mandate], [New Mandate]

, [Current Holdings], [Affected Register]

, [Requester], [Authoriser]

, [Change Type], [Change Date]

from #finalrecord Where Authoriser like '%'+@authoriser+'%'

order by [Change Date] asc, holder_id asc

end

else if ((@requster is not null) and (@authoriser is not null))

begin

Select [Account Number], [Shareholder Name] 

, [Previous Mandate], [New Mandate]

, [Current Holdings], [Affected Register]

, [Requester], [Authoriser]

, [Change Type], [Change Date] 

from #finalrecord Where Requester like '%'+@requster+'%' and Authoriser like '%'+@authoriser+'%'

order by [Change Date] asc, holder_id asc

end

else

begin

Select [Account Number], [Shareholder Name] 

, [Previous Mandate], [New Mandate]

, [Current Holdings], [Affected Register]

, [Requester], [Authoriser]

, [Change Type], [Change Date] 

from #finalrecord order by [Change Date] asc, holder_id asc

end

 

IF(@changeType not in ('edmms', 'change of name', 'change of address', 'correction of name', 'correction of CHN')) 

BEGIN

Print 'THE SPECIFIED TYPE OF REPORT [' +@changeType+ '], BY THE USER IS INVALID, PLEASE CONTACT SYSTEM ADMINISTRATOR!!!';

END

END

 


Monday, November 6, 2017 - 9:31:52 PM - Joh Back To Top (69331)

 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 (43644)

 thank you :)

 


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

 Just what I needed, thank you!

 


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

hello,

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


Tuesday, May 3, 2016 - 10:54:58 AM - Greg Robidoux Back To Top (41392)

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.

-Greg


Tuesday, May 3, 2016 - 10:38:15 AM - Tim Cartwright Back To Top (41391)

 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 (40768)

 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 (38193)

Kris,

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.

-Greg


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

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 (38189)

You are awesome sir


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

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 Back To Top (34648)

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 (30841)

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 Back To Top (30840)

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 4, 2014 - 8:41:27 AM - david Back To Top (29326)

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 (29285)

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 (25840)

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

Sorry, i dont speak inglish...

 

Tks,


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

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 2, 2013 - 9:24:52 PM - Richard Back To Top (23695)

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 Back To Top (23072)

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

@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 Back To Top (23069)
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 4, 2013 - 7:09:27 AM - Francisco Back To Top (22545)

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 Back To Top (22299)

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 Back To Top (22084)

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 (22083)

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 Back To Top (22069)

@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 Back To Top (22055)

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 Back To Top (21855)

 

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 (21713)

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 Back To Top (21711)

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 (20932)

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 (19744)

@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 Back To Top (19743)

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 Back To Top (19345)

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 Back To Top (19209)

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

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 Back To Top (19044)

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 1, 2012 - 9:20:19 AM - Lillian Back To Top (18871)

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 Back To Top (18788)

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 Back To Top (16062)
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 (10370)

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 9, 2008 - 2:47:59 PM - admin Back To Top (968)

Mazharuddin,

Thank you for the contribution.

Thank you,
The MSSQLTips.com Team


Sunday, April 20, 2008 - 12:24:52 AM - Mazharuddin Back To Top (896)
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















get free sql tips
agree to terms