Execute Dynamic SQL commands in SQL Server
By: Greg Robidoux | Updated: 2018-11-16 | Comments (57) | Related: 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 SQL 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
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:
- Writing a query with parameters
- Using EXEC
- 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 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.
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. 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 Person.Address WHERE City = @city

We can turn the above into a stored procedure as follows:
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 stored procedure development check out this tutorial.
Dynamic SQL commands 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 AddressID, AddressLine1 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 = 'AddressID, AddressLine1, City' SET @city = '''London''' SET @sqlCommand = 'SELECT ' + @columnList + ' FROM Person.Address WHERE City = ' + @city EXEC (@sqlCommand)

Dynamic SQL commands using sp_executesql
With this 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.
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

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.
- Protecting Yourself from SQL Injection in SQL Server - Part 1
- Protecting Yourself from SQL Injection in SQL Server - Part 2
- More SQL Injection Articles
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 Updated: 2018-11-16
About the author

View all my tips
Comments For This Article
Thursday, February 06, 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 |
Thursday, February 06, 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 04, 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 04, 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) EXECUTE sp_executesql @SqlString,@ParmDefinition,@[email protected]_Tmp OUTPUT SELECT @Valor_Tmp |
Wednesday, December 04, 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 04, 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 04, 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,@[email protected]_Tmp OUTPUT SET @[email protected]_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 09, 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 |
Tuesday, April 09, 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 '%'[email protected]+'%' 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 '%'[email protected]+'%' 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 '%'[email protected]+'%' and Authoriser like '%'[email protected]+'%' 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 [' [email protected]+ '], BY THE USER IS INVALID, PLEASE CONTACT SYSTEM ADMINISTRATOR!!!'; END END
|
Monday, November 06, 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 03, 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 03, 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' Set @SqlString = 'Select @OutCount = Count(*) From ' [email protected]
|
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://air-hosting.com?exam=sqlservertip/1050/simple-way-to-create-tables-in-sql-server-using-excel/ Thanks |
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 04, 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. :SETVAR TBL MyTable You can write multi-server scripts, like a database copy. (GO required before a second :CONNECT) :SETVAR SOURCE SERVERA 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 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 (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 set @test2 = @Month Set @Select = 'Select Hdl_Nr,' + @test1 + ',' + @test3 + ' from [Table1] as T' set @Select2 = 'update t2 set t2.ROS_S = t1.' + @test1 + ' from Table2 t2 inner join Table1 t1 on t1.Hdl_Nr = t2.Hdl_Nr' |
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 04, 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. Set @test2 = @Month Set @Select = 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)) DECLARE @sqlcommand varchar(500) DECLARE db_cursor CURSOR FOR OPEN db_cursor WHILE @@FETCH_STATUS = 0 CLOSE 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
|
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, |
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 ( EXEC ('INSERT INTO #temp 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' Set @SqlString = 'Select @OutCount = Count(*) From ' [email protected]
|
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 01, 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 '[email protected]+'.dbo.PEROSN WHERE POSTAL_CODE = '''[email protected]+'''' EXEC (@SQL)
|
Tuesday, July 24, 2012 - 8:21:46 PM - Angelo01 | Back To Top (18788) | ||
|
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),
AND declare @script nvarchar(1000), select comapnyid = 1 , @area = 1
|
Friday, May 09, 2008 - 2:47:59 PM - admin | Back To Top (968) |
Mazharuddin, Thank you for the contribution. Thank you, |
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 |