How do I execute a sql statement through a variable (dynamic sql) that tries to do an insert into a variable table?

Question

Possible Duplicate:
How to use table variable in a dynamic sql statement?

If I do what I want to do with a TEMPORARY TABLE, it works fine:

DECLARE @CTRFR VARCHAR(MAX)

SET @CTRFR = 'select blah blah blah' -- <-- very long select statement. this returns a 0 or some greater number. Please note! --> I NEED THIS NUMBER.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo][#CTRFRResult]') AND type IN (N'U') ) 
   DROP TABLE [dbo].[#CTRFRResult]

CREATE TABLE #CTRFRResult
(
  CTRFRResult VARCHAR(MAX)
)

SET @CTRFR = 'insert into #CTRFRResult ' + @CTRFR
EXEC(@CTRFR)

The above works fine.

The problem is that several databases are using the same TEMP table. Therefore I need to use a VARIABLE table (instead of a temporary table).

What I have below is not working because it says that the table must be declared.

DECLARE @CTRFRResult TABLE
(
   CTRFRResult VARCHAR(MAX)
)

SET @CTRFR = 'insert into @CTRFRResult ' + @CTRFR -- I think the issue is here.
EXEC(@CTRFR)

Setting @CTRFR to insert into... is not working because I'm assuming the table name is out of scope. How would I go about mimicking the temporary table code using a variable table?

The error message I'm getting is:

Must declare the table variable "@CTRFRResult"

score 1 · Accepted Answer · answered Sep 18 '12 at 21:40

1

You can't use an @ table with dynamic SQL like that, you'd have to create the table within the dynamic sql statement as well.

But I don't think you really have a problem at all with using # tables.

The # table will only be available to the current session - you can have 100s of sessions each creating their own version of it with no problems.

If you want to create a temp table that other sessions can access, you need to use ## instead.

answered Sep 18 '12 at 21:40

Mister Bee

69
2
4

the problem is, multiple sessions creating the SAME table name. i think this is the issue ive been having. it's creating object X, then someone else running another sproc that creates object X would cause an issue, no? – JJ. Sep 18 '12 at 21:45
@Testifier, local temporary tables (created like `#TableName`) do not have this problem as "the database Engine does this by internally appending a numeric suffix to each local temporary table name." Check out the "Local temporary table" section [on this page](http://msdn.microsoft.com/en-us/library/ms174979.aspx). – Tim Lehner Sep 18 '12 at 21:50
@TimLehner, how do I know a temp table name whose name was changed by SQL when I go to drop these temp tables? – JJ. Sep 18 '12 at 22:04
@Testifier, SQL Server keeps track of that for you. Just go ahead and create the same temp table in two windows of SQL Server Management Studio. Then add data, then query them, then drop them. They're different the whole time because they're done in two different sessions. – Tim Lehner Sep 19 '12 at 00:09
1

@Testifier what Tim says is correct, SQL Server manages that all for you. Look up SQL Books Online for a description of the differences between # tables and ## tables. – Mister Bee Sep 19 '12 at 19:15

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

You cannot insert into a table variable like this since exec runs in its own scope.

From Erland Sommarskog's The Curse and Blessings of Dynamic SQL:

Next thing to observe is that the dynamic SQL is not part of the stored procedure, but constitutes its own scope. Invoking a block of dynamic SQL is akin to call a nameless stored procedure created ad-hoc. This has a number of consequences:

Within the block of dynamic SQL, you cannot access local variables (including table variables) or parameters of the calling stored procedure. But you can pass parameters – in and out – to a block of dynamic SQL if you use sp_executesql.

score 0 · Answer 3 · answered Sep 18 '12 at 22:00

While there is nothing you can do about sending table variable as parameter to dynamic sql or another stored procedure, you might still use it in your particular case. Instead of

SET @CTRFR = 'insert into @CTRFRResult ' + @CTRFR -- I think the issue is here.
EXEC(@CTRFR)

You might put:

insert into @CTRFRResult
  exec sp_executesql @CTRFR

How do I execute a sql statement through a variable (dynamic sql) that tries to do an insert into a variable table?

3 Answers3