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.
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:
answered Aug 16, 2010 at 11:01 AM
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.
answered Aug 17, 2010 at 03:00 AM
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.
answered Aug 16, 2010 at 10:44 AM
Grant Fritchey ♦♦