sql-server - 访问在不同范围内创建的 SQL Server 临时表

Question

我正在为 SQL Server 2008 编写一个存储过程，我需要在其中从一组表中提取信息。我不知道这些表的结构。同一个数据库中还有另一个表告诉我这个表中字段的名称和类型。

我这样做:

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

之后我删除临时表。这是在一个循环中发生的，所以这个表被创建、填充和删除了很多次，每次都有不同的列。

失败并出现错误:

Invalid object name: #new_temporary_table.

经过一番谷歌搜索后，我发现:

1. #new_temporary_table 表是在调用 exec sp_executesql 的范围内创建的，这与我的存储过程不同。这就是下一个exec sp_executesql找不到表的原因。这篇文章对此进行了解释: http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/1dd6a408-4ac5-4193-9284-4fee8880d18a

2. 我可以使用前缀为 ## 的全局临时表。我不能这样做，因为多个存储过程可以同时运行，它们会影响彼此的状态

3. 在这篇文章中说，如果我发现自己处于这种情况，我应该更改数据库的结构。这不是我的选择: http://www.sommarskog.se/dynamic_sql.html

我发现的一个解决方法是将所有 select into #new_temporary_table.. 和所有 insert into ... 脚本合并到一个巨大的语句中。这工作正常，但它有一些缺点。

如果我确实打印@sql 来排除故障，例如，文本会被截断。

我还有其他选择吗？欢迎所有想法。

Answer 1

您可以使用global temp 表，但使用context id(例如newid())作为全局临时表的一部分姓名。

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)