x

Using Dynamic SQL

I have the following procedure that gets some parameters from an SSRS:

declare @SQL AS NVARCHAR(4000)
declare @temptable table(column1 varchar(10), column2 varchar(10)......)
SET @SQL = N' SELECT * FROM @temptable where........'
insert into @temp
select * from sometable where........
execute sp_executesql @SQL

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.

more ▼

asked Aug 16, 2010 at 10:39 AM in Default

Mike 4 gravatar image

Mike 4
44 5 8 9

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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):

declare @SQL nvarchar(4000);

declare @temptable table 
(
    ContactID int, 
    FirstName nvarchar(50),
    LastName nvarchar(50) 
);

set @SQL = 'select * from @temptable;';

insert into @temptable
select ContactID, FirstName, LastName
    from Person.Contact
    where LastName like 'A%';

execute sp_executesql @SQL;

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:

declare @SQL nvarchar(4000);

set @SQL = '
declare @temptable table 
(
    ContactID int, 
    FirstName nvarchar(50),
    LastName nvarchar(50) 
);

insert into @temptable
select ContactID, FirstName, LastName
    from Person.Contact
    where LastName like ''A%'';

select * from @temptable;';

execute sp_executesql @SQL;
Oleg
more ▼

answered Aug 16, 2010 at 11:01 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

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)
10|1200 characters needed characters left

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:

-- First define a custom TABLE type
CREATE TYPE IntegerTableType AS TABLE (ID INTEGER)
GO

-- Fill a var of that type with some test data
DECLARE @MyTable IntegerTableType
INSERT @MyTable VALUES (1),(2),(3)

-- Now this is how you pass that var into dynamic statement
EXECUTE sp_executesql N'SELECT * FROM @MyTable', 
    N'@MyTable IntegerTableType READONLY', 
    @MyTable
more ▼

answered Aug 17, 2010 at 03:00 AM

AdaTheDev gravatar image

AdaTheDev
871 1 1 4

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Aug 16, 2010 at 10:44 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x560

asked: Aug 16, 2010 at 10:39 AM

Seen: 2322 times

Last Updated: Aug 16, 2010 at 10:55 AM