Dynamic query works with float but not with varchar????
-
I ama hoping someone can shed light on why the first dynamic query works and the second does not!
declare @temp varchar(25)
declare @impstore varchar (10)
declare @sql varchar(1000)
set @temp = 'Y02P02'
set @impstore = '17'
exec ('select ' + @temp + ' from first_sales where store = ' +@impstore)
The query above works but when I run it with a different set of data, it returns an error. The only difference in the table layouts is that the one that works above, the store in first_sales is defined as a float where in the sql below store in second_sales is defined as a varchar. Here is the same query that does not work (the only difference being the table and store value):
declare @temp varchar(25)
declare @impstore varchar (10)
declare @sql varchar(1000)
set @temp = 'Y02P02'
set @impstore = 'K12390'
exec ('select ' + @temp + ' from second_sales where store = ' +@impstore)
It returns error:
Server: Msg, Level 16, State 3, Line 1
Invalid column name ‘K12390’
I am not sure why it think 'K12390' is a column as opposed to a store?
-
Hi
use nvarchar
-
This is the query you are executing:
select Y02P02 from second_sales where store = K12390
You have to enclose literal strings in quotes like below, or it will assume you are giving it a column name.
select Y02P02 from second_sales where store = 'K12390'
-
That makes sense except that I thought I tried that (I feel like I have tried a million things to get this to work) and when I added the quotes:
exec ('select ' + @temp + ' from second_sales where store = ' + '@impstore')
I get the error:
Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@impstore'.
Even though I declared it at the top!
Still confused..............
-
Ok so I got the basic dynami query to work but am not trying with:
exec ('update temp_data set nu_m13_prior_amount =
(select '+ @dc_prd_year1 + '
from taco_sales where store = ' +@sc_store1+')' +
' where ex_store = ' +@sc_store1
+ ' and new_store = ' + @sc_store)
Now I am really confused with where to put all the quotes. Anyone??
-
Couple of things first its much better to use sp_executesql than exec as you can then parameterise the query and its much more likely that the plan will be reused by the query optimiser. Secondly its much less likely you'll get your quotes wrong so from your first example I would re-write as follows
declare
@temp nvarchar(25)
declare
@impstore nvarchar (10)
declare
@sql nvarchar(1000)
set
@temp = 'Y02P02'
set
@impstore = 'K12390'
SET
@sql = 'select ' +@temp+ ' from second_sales where store = @i'
EXEC
sp_executesql @sql, N'@i as nvarchar(10)', @i = @impstore;
Notice that you still need to concatenate the @temp variable with the rest of the string but that the @impstore variable has been substituted by the parameter @i which is now passed in when you call the proc sp_executesql. Anyway I find this method easier as there is much less concatenating of strings together and its got the benefit of being the most efficient too.
Anyway hth
David
-
It is also possible to use QUOTENAME to generate the required quote marks for your existing query
Sean -
From your example try this out
Considering all your variables are of VARCHAR type.
DECLARE @SQL VARCHAR(2000)
DECLARE @dc_prd_year1 VARCHAR(50)
DECLARE store1 VARCHAR(50)
DECLARE @sc_store VARCHAR(50)
SET
@SQL = 'update temp_data set nu_m13_prior_amount =
(select '
+ @dc_prd_year1 + '
from taco_sales where store = '''
+@sc_store1 +''') where ex_store = ''' +@sc_store1 + ''' and new_store = ''' + @sc_store + ''''
EXEC
(@SQL)
Prasad Bhogadi
www.inforaise.com -
Do what David has recommend, use sp_executesql and pass in the value as a parameter.
The fundamental problem here is that you're passing in data which you then execute, so you're running the risk of SQL Injection. There are ways you can avoid it of course, but it's still messy. The fact that your query is getting confused between a column name and a value demonstrates that you're not entirely clear on what you're trying to execute.
But to avoid SQL Injection, you really need to make sure that the server understands which bits of your query are the query, and which bits are the data. You don't want anything provided by a user to be executed, in case it's malicious.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply