June 10, 2009 at 2:31 pm
I'm trying to create a dynamic SQL string within a stored procedure. A table name will be fed to the SP at run time. Here's what I've got so far. But when I run this I get
"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'PAR1'"
Note that this code is just a simplified example to help me understand what's going on. In the real code, all the PAR1's would be replaced with @strParTable
declare @strSQL nvarchar(500)
declare @strParTable nvarchar(500)
set @strSQL= 'SELECT ' + @strParTable +'.refdes, PAR1.type, PAR1.errcode,
PAR1.repcode,PAR1.c,PAR1.uid,PAR1.noun,
ex1=case
when tagset is null
then ''none''
else tagset
end,
PAR1.image,PAR1.serial,PAR1.h,PAR1.date,PAR1.time
FROM PAR1 INNER JOIN par2parunique1
ON PAR1.time = par2parunique1.repTime
AND PAR1.date = par2parunique1.repDate
AND PAR1.refdes = par2parunique1.refdes
AND PAR1.image = par2parunique1.image
AND PAR1.serial = par2parunique1.Serial
AND PAR1.noun = par2parunique1.noun'
set @strParTable='PAR1'
exec sp_executesql @strSQL,@strParTable
The following SQL statement runs fine:
SELECT PAR1.refdes, PAR1.type, PAR1.errcode,
PAR1.repcode,PAR1.c,PAR1.uid,PAR1.noun,
ex1=case
when tagset is null
then 'none'
else tagset
end,
PAR1.image,PAR1.serial,PAR1.h,PAR1.date,PAR1.time
FROM PAR1 INNER JOIN par2parunique1
ON PAR1.time = par2parunique1.repTime
AND PAR1.date = par2parunique1.repDate
AND PAR1.refdes = par2parunique1.refdes
AND PAR1.image = par2parunique1.image
AND PAR1.serial = par2parunique1.Serial
AND PAR1.noun = par2parunique1.noun
What am I getting wrong????
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
June 10, 2009 at 2:48 pm
You have to complete the variable substitution where you are building the SQL command, not in the call the sp_executesql.
June 10, 2009 at 3:01 pm
Could you please illustrate what you mean? (by marking up my code....?)
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
June 10, 2009 at 3:06 pm
Your first code block actually has the proper start. You just need to move the SET of the variable of the table above the SET for the strSQL command.
I seem to be having issues trying to post code blocks on this thread. Don't ask, it is a strange intermittent problem.
June 10, 2009 at 3:46 pm
Changed code per below. No difference. Still getting the same error, "incorrect syntax near 'PAR1'.
If I understand sp_executeSQL correctly (and there's certainly some doubt of that!), the order in which you set the variables shouldn't make any difference.
declare @strSQL nvarchar(500)
declare @strParTable nvarchar(500)
set @strParTable='PAR1'
set @strSQL= 'SELECT ' + @strParTable +'.refdes, PAR1.type, PAR1.errcode,
PAR1.repcode,PAR1.c,PAR1.uid,PAR1.noun,
ex1=case
when tagset is null
then ''none''
else tagset
end,
PAR1.image,PAR1.serial,PAR1.h,PAR1.date,PAR1.time
FROM PAR1 INNER JOIN par2parunique1
ON PAR1.time = par2parunique1.repTime
AND PAR1.date = par2parunique1.repDate
AND PAR1.refdes = par2parunique1.refdes
AND PAR1.image = par2parunique1.image
AND PAR1.serial = par2parunique1.Serial
AND PAR1.noun = par2parunique1.noun'
exec sp_executesql @strSQL,@strParTable
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
June 10, 2009 at 3:51 pm
You are missing the point. You can't pass in the table name as a parameter to the dynamic sql in the sp_executesql. You can pass in variables where you can NORMALLY use variables in a normal query.
You have to build the dynamic query such that when you to a print @SQLCmd, you see the query you are trying to build.
June 10, 2009 at 4:02 pm
Here is how I would code it.
Tried posting directly, but couldn't so it is posted as an attachment.
June 10, 2009 at 4:51 pm
I copied your code into my db and did nothing but uncomment the exec statement. (Oh, and add a missing ")" at the end after par2parunique1.noun).
I get the same error message, "incorrect syntax near 'PAR1'.
If I copy the resulting SQL string to a query window, it runs fine. But the SP still won't run.
BTW, it looks to me like you are still passing the table name as a variable. Your'e just passing it only to the FROM statement, then using the 'vt1' alias for all the other references to it. ...? I don't see how this is really any different from what I was doing...?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
June 10, 2009 at 5:46 pm
At last I have figured it out!
I changed from:
exec sp_executesql @strSQL, @strParTable
To:
exec sp_executesql @strSQL
So my take on this is that I can only pass where-clause-type variables as parameters via sp_executeSQL; the remained of the query (table names and such) must be built with concatenated strings. Lynn, I think this is what you were trying to tell me.... Thanks for your help!
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
June 10, 2009 at 6:00 pm
She Through Whom All Data Flows (6/10/2009)
...So my take on this is that I can only pass where-clause-type variables as parameters via sp_executeSQL; the remained of the query (table names and such) must be built with concatenated strings. Lynn, I think this is what you were trying to tell me.... Thanks for your help!
Close. Basically parameters and variables can be anywhere that a column value can: the SELECT list, the WHERE clause, the ON clauses, etc. But like columns, only to supply values, they cannot be used to do Name substitution of columns, tables or any other SQL objects.
So building a string and doing Name substitutions in that string and then executing the string (i.e.,dynamic SQL) is the only way to get that effect.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 10, 2009 at 6:05 pm
And lest we forget, we would be remiss if we did not point out that if your routine is going to be receiving the table name parameter from client code, then it is very vulnerable to SQL Injection attacks in it's current form.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 10, 2009 at 6:13 pm
True, Barry, the code I provided was very basic for generating dynamic sql.
Thank you for pointing out the need to do more regarding sql injection.
June 10, 2009 at 6:26 pm
Yeah, but it's only a SQL Injection target if the tablename string is somehow coming from a user and the sProc is not executing under that user's domain account. So a lot of the time, it's not necessary to add all of the Injection-Protection(c).
(c) hmm, maybe I should copyright that phrase. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 10, 2009 at 7:57 pm
Using QUOTENAME will pretty much put the kabosh on SQL Injection. But, I agree... if it's not used in a public facing environment, there's not much need for the protection.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply