Referencing a table variable in a query string

Question

I have created a temprary table variable, which I then need to pivot:

Declare @TempTable TABLE(
Name varchar(150),
CloseDate Date, 
Revenue Float)

.... <add data to it> .....    

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(CloseDate) 
            FROM @TempTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT  Name, ' + @cols + ' from 
                (select 
                    t.Name,
                    t.CloseDate,
                    t.Revenue
                from @TempTable as t
                ) x
            pivot 
                (SUM(Revenue)
                    for CloseDate in (' + @cols + ')
                ) p '

execute(@query)

However, I am getting this error:

Must declare the scalar variable "@TempTable".

When I test the @TempTable variable using a normal SELECT it works fine:

SELECT * from @TempTable

How can I reference the variable successfully in the query string?

Use 'proper' temp tables instead

T I
– T I

2013-11-11 15:14:15 +00:00
Commented Nov 11, 2013 at 15:14 — T I
– T I, Commented Nov 11, 2013 at 15:14
@TI what do you mean by 'proper' temp tables ??

Dhaval
– Dhaval

2013-11-11 15:22:50 +00:00
Commented Nov 11, 2013 at 15:22 — Dhaval
– Dhaval, Commented Nov 11, 2013 at 15:22

David · Accepted Answer · 2013-11-11 15:52:43Z

5

Unfortunately when you use execute and sp_executesql they are run within their own context, so they cannot reference table variable objects which are defined outside the scope of the dynamic SQL.

In cases like this, what I have tended to do is create a temp table instead and embed a GUID in the name of the temp table that gets created in tempdb. This ensures the table name is unique for simultaneous operations, and then I place that guid in the dynamic SQL that is created.

~~Performance wise it's slightly slower but still quick, but whether that is an issue for you or not depends on the number of times this will be executed and the frequency.~~

After some discussion with Damien in the comments to this answer we've determined that local temp tables (single hashtag) can be used when executing dynamic SQL.

Therefore if you change your declare table variable to CREATE TABLE #TempTable and change the reference in your dynamic SQL this should work properly for you.

The only concern I have about using temp tables is the persistence of objects if your calling code uses some form of connection pooling which doesn't sound like it's the case here.

However, as a general self-paranoid self practice I like to throw one of these before the create table and at the end of the statement to clean up the objects.

IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (Value VARCHAR(50));

edited Nov 11, 2013 at 15:52

answered Nov 11, 2013 at 15:18

David

1,6111 gold badge11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Damien_The_Unbeliever Over a year ago

There's no need to manually "uniqueify" temp tables - unless you create a global temp table (not needed here) temp table names only have to be unique on your own connection. Multiple connections can be using temp tables with the same name simultaneously without conflicts.

David Over a year ago

@Damien_The_Unbeliever I believe that dynamic sql performed with EXEC and sp_executesql are not considered part of the same connection which would require the use of a global temp table, which is the reason for the uniquefier in this case. I did a case study on this at one point but it's been a few months so it's entirely possible I may be misremembering or it may be worth doing the tests again for the new versions as that was in SQL2005.

Damien_The_Unbeliever Over a year ago

create table #T (ID int not null) exec('insert into #T(ID) values (10)') select * from #T produces a row containing one column, with the value 10. Like I said, global temp tables aren't needed here (and my comment was the first mention of globals, also). Has been true at least as far back as 2000, to my recollection.

Collectives™ on Stack Overflow

Referencing a table variable in a query string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related