How to access temptable after creating it dynamically.

Question

Hi.. I am using the statement

SELECT    * 
INTO      #gsig_ref  
FROM      gsign 
WHERE     [name] NOT LIKE 'RESERVE%' OR [name] NOT LIKE 'Spare%'



EXECUTE('SELECT * INTO #db1 FROM ' + @db1)
EXECUTE('SELECT * INTO #db2 FROM ' + @db2)

where @db1 will be supplied at runtime (for eg @db1 = '#gsig_ref')

If I say select * from #db1... it says invalid object. Please help me how to access data from #db1.. since i have use this in another query like

SELECT        DISTINCT @p1 INTO #curs_name
FROM          #db1 
WHERE         @p1 NOT IN (SELECT @p2 FROM #db2)
ORDER BY      @p1

How can #db1 be acccessed in the above query and also @p1 is the input variable to this procedure and I should use it for the distinct @p1..

Please help me in workin on this Thanks Ramm

Thanks Ramm

Answer 1

You can not reference variables declared in the EXEC(like your temp tables) statement outside that statement.

Answer 2

You cannot access temp tables declared in dynamic SQL.

However, if you create a temp table before running a dynamic query, you can reference it in that dynamic query.

In your example, this would become

CREATE TABLE #db1
(...table definition...)

CREATE TABLE #db2
(...table definition...)

EXECUTE('INSERT #db1 SELECT * FROM ' + @db1)
EXECUTE('INSERT #db2 SELECT * FROM ' + @db2)

You will also need to construct and execute your second query (using @p1) as dynamic SQL, since you want to use variables as placeholders for column names.

EDIT

There is no simple way to modify this solution if the structure of your source tables varies.

You could write some code to query the information schema to generate and apply dynamic ALTER TABLE scripts to the temp table to make its structure match the source table, but this may be more complexity than you're willing to take on.

Andomar's suggestion of carrying out the whole operation in dynamic SQL may be a better fit.

It might be a good idea to take a step back and re-evaluate whether what you're trying to do is the best solution to whatever problem it is you're trying to solve.

Answer 3

A local temporary table that is created in a stored procedure is dropped when the procedure ends; other stored procedures, or the calling process, can't see them.

You could generate the entire SQL, effectively moving the temporary table to the dynamic SQL, like:

declare @sql varchar(max);
set @sql = 'select into #t1 ....; select * from #t1';
exec (@sql);

If I read that, I wonder why your outer procedure even needs the temporary table, but there may be good reasons for that.

Another option, if you're sure only one call is executed at the same time, is to use a global temp table. They're declared with a double hash, like ##TempTable. Global temp tables survive the end of their procedure, but can be referenced from multiple sessions.