Table variable in dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Table variable in dynamic SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-06 : 07:40:25

Dana writes "Can you reference a table variable as part of a dynamic sql statement. For instance, I have a table variable @results declared. I am testing if this variable can be used in a dynamic sql statement such as

SELECT @select = 'SELECT * FROM @results'
exec (@select)
When this runs, I get the error Must declare the variable '@results'

I have also tried 'SELECT * FROM ' + @results, but then that does not even compile with the same error that @results must be declared (I am using that variable in other places where it works fine, so the variable is declared properly)

I am attempting to run a dynamic crosstab report where the results from my table variable contains the data I am trying to crosstab. The columns vary, therefore, I need to build the sql for the crosstab dynamically requiring the table variable.

HELP...TIA"

Nazim
A custom title

1408 Posts

Posted - 2002-02-06 : 07:55:23

I dont have Sql Server 2000. So, cant answer on your original Question.

but follow this link on Cross tab. Great work by Robvolk http://www.sqlteam.com/item.asp?ItemID=2955

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-06 : 08:02:21

Hi

No that won't work. A variable declared outside of your dynamic sql will have a different scope.

You have two options here, the first is put it all in the dynamic sql, i.e. declare your variable in the dynamic sql batch, which I think for you will mean putting the whole thing there (yuk).

The other option is to use a temp table. These are actually created in temp DB so will be available to your dynamic batch.

Hope that helps. For more info on dynamic sql scope have a read of the Part 2 dynamic sql article.

Damian

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-06 : 08:26:10

I think You should be able to pas this table with sp_executesql. I have never tried this tough and I can not right now as I am at home.
Here is the idea though:

SELECT @SELECT = 'Select * FROM @TableInner'

exec sp_executesql @Select,'@TableInner Table...',@Results

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources