如何在TSQL中访问由存储过程调用生成的当前作用域内的数据集？

Question

问题背景

生成和访问固定列布局的数据很容易。您可以预先创建本地临时表，并通过调用存储过程来填充它们。

另一方面，如果您想生成具有动态列布局的数据，则通常必须动态构建SQL语句，并使用“exec sp_executesql”执行它。由于数据布局在运行时未知，因此无法预先创建临时表，在“exec sp_executesql”语句内部创建的任何临时表都绑定到该范围，并在调用返回时消失，因此更难访问数据（即您的选项更有限）。

我的具体情况

我有一个查询需要访问动态生成的表中的数据。

该表由存储过程生成，该存储过程动态构建查询，将其存储在变量“@sql nvarchar(max)”中，并通过调用“exec sp_executesql @statement = @sql”运行它。

该@sql语句类似于"select * into #temptable from..."，但是#temptable在"exec sp_executesql"返回时被销毁了。一个快速的解决方法是只使用"##temptable"（即全局临时表），因为它在存储过程返回时仍然存在，并且我可以轻松地在调用范围内访问它（因为它具有已知/静态名称）。

我不喜欢这个解决方案，因为全局临时表在名称冲突方面不是线程安全的，而且我不想必须处理动态生成的唯一名称，因为我最终将不得不使用更多的动态SQL来访问它们...这将使我回到原点，导致数据在存储过程外部无法访问。

我认为返回表变量（通过输出参数）不是一个选项（对于SQL Server 2008也是如此），除非可以在不必定义静态表类型的情况下完成。我的存储过程生成的表是动态的，并且取决于传递的输入参数。

内联表值函数不是一个选项，因为我正在运行代码循环来构建@sql查询并调用"exec sp_executesql"。

多语句表值函数（而不是存储过程）也不是一个选择，因为这种函数必须具有明确定义的表格式，而我正在运行动态SQL以返回一个具有可变列数和列名的表取决于输入参数值。我真正想做的就是将动态查询结果集选择到一个新表中，但我发现很难，因为以上方法都不起作用；特别令人恼火的是局部临时表不是局部会话的，而是局部存储过程的，因此它们在返回后消失。我看到唯一的solution坚持认为使用OPENROWSET是唯一的方法，但是出于与不想包含唯一名称管理代码相同的原因，我不想在存储过程内部混乱连接字符串... 它比应该复杂得多。总之，我只想执行动态SQL生成未知格式的数据集，并能够轻松地从调用范围访问它。

- Triynko

---

为什么要踩这篇帖子？虽然发帖人试图让SQL做一些其本身并不擅长的事情，但显然他已经花了一些精力考虑各种选项，并清晰地表达了他的目标。 - user565869

这个问题让我很感兴趣，因为它似乎是SQL应该非常擅长处理的事情。真正的问题在于本地临时表的范围太有限了。我们真正需要的是第三种类型的临时表，它可以在子作用域中创建，但绑定到顶级调用作用域。表变量很好传递，最终在SQL Server 2008中可以作为输出参数传递，但是...它们仍然需要固定的定义和预先声明的类型才能将它们作为输出参数传递。 - Triynko

我自己说不出来得更好了！我有完全相同的问题。顺便说一句，我希望T-SQL支持一个“变量表变量”，它不需要预先确定的结构。 - Pete Alvin

Answer 1

在sp_executesql之前创建临时表：它仍然适用于"内部"范围，如sp_executesql 将SQL更改为执行INSERT而不是SELECT..INTO... 编辑：

使表足够宽以涵盖所有选项。

坦白地说，SQL旨在使用固定的表定义：可变输出签名（表）会导致您遇到的问题...

- gbn

---

那样做行不通，因为我无法提前创建临时表，因为表的定义来自于动态生成的SQL语句。如果表的列布局是静态的，我可以在当前范围内简单地创建临时表，而不会有问题。然而，正如你所看到的...当“select”语句是动态生成的，并且必须使用“exec sp_executesql”运行时，就存在跨存储过程范围访问数据的问题。 - Triynko

@Triynko：请看我的更新。您选择了SQL不支持的操作，因此您的选项有限。 - gbn

没错，这就是我在这里发布帖子寻找那些选项的原因。我在这里找到了一个很好的选项列表...http://www.sommarskog.se/share_data.html，它是关于“存储过程之间共享数据”的文章，但我仍然希望/正在努力寻找更好的解决方案。OPENROWSET选项仍然不错，因为它似乎支持我想要做的事情（从动态查询直接选择到一个临时表），但我讨厌管理SP内部连接的性能影响和语法。 - Triynko

将表格变得足够宽也不是一个好的选择，因为这实际上意味着将表格视为一种通用容器，这使得访问数据变得困难。作为通用容器，它将具有通用列名，这种情况下，我的静态查询将期望数据在命名列中返回，然后必须利用存储过程返回的关于包含所需数据的列（序数）的数据。 - Triynko

Answer 2

一个笨重但可行的选择是在调用者中创建一个单列表，然后在被调用者中进行修改。这样可以避免作用域问题，但会使事情变得更加困难...

如果您构建了一个存储过程，接受输入和输出表名，并通过删除和添加列使第二个表与第一个表完全相同，那么您可能会有一个更通用的解决方案。

从 tempdb.INFORMATION_SCHEMA.COLUMNS 中选择将让您找到任何临时表的列信息（您可以通过表名是否以 # 开头来检测）。以下是一个示例：

CREATE TABLE #blah (a int)
SELECT *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE Object_id('tempdb.dbo.' + TABLE_NAME) = Object_id('tempdb.dbo.#blah')
DROP TABLE #blah

请注意，视图中的表名不是#blah（它可能是类似于#blah___{很多下划线}___00000000021D这样的东西），因此使用Object_id()来关联两者。

为了实际使用它，建议首先通过添加TOP 0或WHERE 1 = 0运行您的过程来创建一个空表，然后使用您的表复制SP将表的结构复制到正确的表中，最后运行数据处理以仅一次插入到正确的表中，而不是填充第一个表格的数据，然后变形第二个表格并将数据复制到其中。

就像我说的那样，有些笨重，但在没有其他选择的狭窄情况下可能会有用。

- ErikE

Answer 3

所以，我从 将存储过程的结果插入临时表 复制了我的答案。希望能有所帮助。是的，这又是全局临时表，唯一不同的是附加了GUID。

---

我遇到了相同的问题，这里是我从Paul's suggestion中为此所做的。主要部分是使用NEWID()来避免多个用户同时运行存储过程/脚本，解决全局临时表的问题。

DECLARE @sql varchar(max) = '', 
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'
EXEC(@sql)

EXEC('SELECT * FROM [' + @tmp_global_table + ']')

- zhongxiao37