如何访问通过调用TSQL中的存储过程生成的当前范围内的数据集？ [英] How to access dataset in current scope generated by a call to a stored procedure in TSQL?

问题描述

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

Generating and accessing data of a fixed column layout is easy. You can create local temp tables up-front, and populate them by calling stored procedures.

另一方面，如果要生成具有动态列布局的数据，您通常必须动态地构建SQL语句，并使用 exec sp_executesql执行它。由于在运行时数据布局是未知的，因此您无法预先创建临时表，并且一旦在 exec sp_executesql语句内，在此创建的任何临时表都将绑定到该范围并在调用返回时消失。

On the other hand, if you want to generate data with a dynamic column layout, you must generally build an SQL statement dynamically and execute it with "exec sp_executesql". Since the data layout is unknown at run-time, you cannot create a temp-table up-front, and once inside the "exec sp_executesql" statement, any temporary tables created there are bound to that scope and vanish when the call returns, so it's much more difficult to access the data (i.e. your options are more limited).

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

I have a query that needs to access data in a dynamically generated table.

该表由存储过程生成，该存储过程动态生成查询并将其存储在变量中 @sql nvarchar（max），然后通过调用 exec sp_executesql @statement = @sql来运行它。

The table is generated by a stored procedure, which dynamically builds a query, stores it in a variable "@sql nvarchar(max)", and runs it by calling "exec sp_executesql @statement = @sql".

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

The @sql statement was something like "select * into #temptable from...", but #temptable was destroyed by the time "exec sp_executesql" returned. A quick fix for this was to just use "##temptable" instead (i.e. a global temp table), because it survives when the stored procedure returns AND I can easily access it in the calling scope (because it has a known/static name).

我不喜欢这种解决方案，因为全局临时表不是线程安全的（按名称命名），并且我不想搞砸了动态生成的唯一名称，因为我最终将不得不使用更多的动态SQL来访问它们……这使我回到第一个平方，而在SP之外无法访问数据。

I don't like this solution because global temp tables aren't thread-safe (name collistion-wise), and I don't want to have to mess with dynamically-generated unique names, because I'll just end up having to use more dynamic SQL to access them... which puts me right back at square one, leaving the data inaccessible outside the SP.

我不认为（通过输出参数）返回表变量（也是SQL Server 2008的新功能）是一种选择，除非可以不必定义静态表类型而这样做。我的存储过程生成的表是动态的，并且取决于传递的输入参数。

I don't think returning table variables (through output parameters) is an option (new to SQL Server 2008 too), unless it can be done without having to define a static table type. The tables my stored procedure generates are dynamic, and depend on the input parameter(s) passed.

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

Inline table-valued functions are not an option, because I'm running code loops to build the @sql query and calling "exec sp_executesql".

多语句表值函数（而不是存储过程）也是一种选择，因为这样的函数必须具有定义良好的表格式，而我正在运行dyanmic SQL以根据输入参数值返回具有可变数目的列和列名的表。

Multi-statement table-valued functions (instead of the stored procedure), is also not an option, because such a function must have a well-defined table format, whereas I'm running dyanmic SQL to return a table with a variable number of columns and column names depending on the input parameter values.

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

All I really want to do is select the result set of the dynamic query into a new table, but I'm finding it difficult, as none of the above works; particularly irritating is how local temporary tables aren't local to the session, but local to the stored procedure so that they vanish upon returning. The only solution I've seen insists that using OPENROWSET is the only way, but I don't want to mess with connection strings inside my stored procedure, for the same reason I don't want to include unique-name-management code... it's just way more complicated than it ought to be.

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

In summary, I just want to execute dynamic SQL that generates a dataset of an unknown format, and be able to easily access it from the calling scope.

推荐答案

因此，我要从将存储过程的结果插入到临时表中。希望能帮助到你。是的，它又是全局临时表，唯一的不同是在其中附加了GUID。

So, I am copying my answer from Insert results of a stored procedure into a temporary table. Hope it helps. Yes, it's global temporary table again and the only different is the GUID appended there.

我遇到了同样的问题，在这里这就是我从> Paul的建议中所做的事情。主要部分是使用 NEWID（）来避免多个用户同时运行存储过程/脚本，这是全局临时表的痛苦。

I met the same problem and here is what I did for this from Paul's suggestion. The main part is here is to use NEWID() to avoid multiple users run the store procedures/scripts at the same time, the pain for global temporary table.

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 + ']')

这篇关于如何访问通过调用TSQL中的存储过程生成的当前范围内的数据集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！