Access SQL Server temporary tables created in different scope

Question

I am writing a stored procedure for SQL Server 2008 in which I need to extract information from a set of tables. I do not know ahead of time the structure of those tables. There is another table in the same database that tells me the names and types of the fields in this table.

I am doing this:

declare @sql nvarchar(max)

set @sql = 'select ... into #new_temporary_table ...'
exec sp_executesql @sql

Then I iterate doing:

set @sql = 'insert into #another_temporary_table ... select ... from #new_temporary_table'
exec sp_executesql @sql

After that I drop the temporary table. This happens in a loop, so the table with be created, populated and dropped many times, each time with different columns.

This fails with the error:

Invalid object name: #new_temporary_table.

After some googling I have found that:

1. The table #new_temporary_table is being created in the scope of the call to exec sp_executesql which is different from the one of my stored proc. This is the reason the next exec sp_executesql cannot find the table. This post explains it: http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/1dd6a408-4ac5-4193-9284-4fee8880d18a

2. I could use global temporary tables, which are prepended with ##. I can't do this because multiple stored procs could run at the same time and they would be affecting each other's state

3. In this article it says that if I find myself in this situation I should change the structure of the database. This is not an option for me: http://www.sommarskog.se/dynamic_sql.html

One workaround I have found was combining all the select into #new_temporary_table.. and all the insert into ... scripts into one gigantic statement. This works fine but it has some downsides.

If I do print @sql to troubleshoot, the text gets truncated, for example.

Do I have any other option? All ideas are welcome.

Answer 1

I think it's best to use one single script.

You can change how many characters will print in Tools > Options > Query Results > SQL Server > Results to Text - change "Maximum number of characters..." from 256 to the max (8192).

If it's bigger than 8192, then yes, printing is difficult. But you could try a different option in this case. Instead of PRINT @sql; instead use the following (with Results to Grid):

SELECT sql FROM (SELECT @sql) AS x(sql) FOR XML PATH;

Now you can click on the result, and it opens in a new query window. Well, it's an XML file window, and you can't execute it or see color-coding, and you have to ignore that it changes e.g. > to > to make it valid as XML data, but from here it's easy to eyeball if you're just trying to eyeball it. You can copy and paste it to a real query editor window and do a search and replace for the entitized characters if you like. FWIW I asked for them to make such XML windows real query windows, but this was denied:

http://connect.microsoft.com/SQLServer/feedback/details/425990/ssms-allow-same-semantics-for-xml-docs-as-query-windows

Answer 2

#temp tables (not global) are available in the scope they were created and below. So you could do something like...

while (your_condition = 1) begin
    set @sql = 'select ... into #temp1 ...from blah
        exec sp_do_the_inserts'
    exec(@sql)
end

The sp_do_the_inserts might look like...

select * into #temp2 from #temp1
....your special logic here....

This assumes you create sp_do_the_inserts beforehand, of course. Don't know if that serves your need.

Answer 3

You could use global temp tables, but use a context id (such as newid()) as part of the global temp table name.

declare @sql varchar(2000)
declare @contextid varchar(50) = convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 4)))
set @sql = 'select getdate() as stuff into ##new_temporary_table_' + @contextid
exec (@sql)