I have the following procedure that gets some parameters from an SSRS:
when I run this procedure, I get this error --> "Incorrect syntax near the keyword 'execute'" I have tried moving the 'declare' an 'set' keywords around but that didn't help. Any suggestions would greatly be appreciated.
(comments are locked)
|
From the question definition, it looks like you get a stop sign before the query is executed. This is because there is a flaw in the logic, but you don't get to see it yet due to the parsing error. For now, you have a syntax error somewhere in the line reading select * from sometable where......... Since I cannot see the whole statement, I cannot tell what the error is, but it is there, please check your syntax (highlight the whole statement except the line reading execute sp_executesql @SQL and click Parse to reveal it). Once you go past this point, you will then get to see the actual problem: if you declare your local table variable in your original script, it is not visible to sp_executesql. Here is the sample script revealing this behaviour (I use AdventureWorks SQl Server 2005 database):
The above fails with expected Msg 1087, Level 15, State 2, Line 1. Must declare the table variable "@temptable" error. Here is the script which works:
Oleg That's possible. I assumed that the WHERE clause represented by the dots was both accurate and a closed statement as part of the definition of @SQL. If either of those is untrue, your answer is accurate.
Aug 16, 2010 at 11:54 AM
Grant Fritchey ♦♦
@Grant Fritchey Since the error in question states "Incorrect syntax near the keyword 'execute'", I think that the problem happens at parse time, well before sp_executesql kicks in, probably a smple syntax error somewhere on that line we cannot see.
Aug 16, 2010 at 12:28 PM
Oleg
(comments are locked)
|
Just to add to previous answers. You can pass a TABLE variable as a parameter into sp_executesql in SQL Server 2008 and later, thanks to the support for Table Valued Parameters. Quick example:
(comments are locked)
|
You can't build scripts like this as the source for SSRS. You should take a look at the Query Designer window and some of the methods exposed there. If you put the whole thing within double quotes you may be able to do it as you've defined it.
(comments are locked)
|