3

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.

Improve this question
2
  • Why not use a different name for your temp tables like #new_temporary_table_1. Also, since you are dropping them ... you can use normal table (staging table); create them under tempdb. So even if you missed to drop them they will wiped off automatically. Commented May 18, 2012 at 15:25
  • You've summed up the options pretty nicely - "local" temp table (only visible within same scope), global temp tables, regular tables - that's about it. One solution might be to create real tables, in a specific schema - that the stored procs agree on; e.g. the first stored proc creates the temp1 schema and stores it's data in there, and then the others that "belong together" will all use that temp1 schema for their tables. A second run would then use a temp2 schema - and so on... Commented May 18, 2012 at 15:28

3 Answers 3

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This sounds good. I could even use the current session id (@@SPID)
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

Improve this answer

1 Comment

This setting is just what I need! I think 8192 will be enough to troubleshoot.
1

#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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.