tsql - 如何访问通过调用 TSQL 中的存储过程生成的当前范围内的数据集？

Question

问题背景

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

另一方面，如果要生成具有动态列布局的数据，通常必须动态构建 SQL 语句并使用“exec sp_executesql”执行它。由于数据布局在运行时是未知的，因此您无法预先创建临时表，并且一旦在“exec sp_executesql”语句中，在那里创建的任何临时表都绑定(bind)到该范围并在调用返回时消失，所以访问数据要困难得多(即您的选择更加有限)。

我的具体情况

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

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

@sql 语句类似于“select * into #temptable from...”，但在“exec sp_executesql”返回时，#temptable 已被破坏。对此的快速解决方法是只使用“##temptable”(即全局临时表)，因为它在存储过程返回时仍然存在并且我可以在调用范围内轻松访问它(因为它具有已知/静态名称)。

我不喜欢这个解决方案，因为全局临时表不是线程安全的(名称冲突)，而且我不想弄乱动态生成的唯一名称，因为我最终不得不使用更动态的 SQL 来访问它们......这让我回到了第一方，让数据在 SP 之外无法访问。

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

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

多语句表值函数(而不是存储过程)也不是一种选择，因为这样的函数必须具有明确定义的表格式，而我正在运行动态 SQL 以返回具有可变数量的表列和列名取决于输入参数值。

我真正想做的就是将动态查询的结果集选择到一个新表中，但我发现这很困难，因为上述方法都不起作用；特别令人恼火的是，本地临时表不是 session 本地的，而是存储过程本地的，因此它们在返回时消失了。唯一的solution我看到坚持使用 OPENROWSET 是唯一的方法，但我不想在我的存储过程中弄乱连接字符串，出于同样的原因，我不想包含唯一名称管理代码......它是只是比它应该的复杂得多。

总之，我只想执行生成未知格式数据集的动态 SQL，并能够轻松地从调用范围访问它。

Answer 1

在 sp_executesql 之前创建临时表:它仍然在“内部”范围内，如 sp_executesql
更改 SQL 以执行 INSERT而不是 SELECT..INTO...
编辑:

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

坦率地说，SQL 旨在使用固定的表定义:可变输出签名(表)会导致您遇到的问题......