从存储过程 Transact-SQL SQL Server 中访问结果集

Access to Result sets from within Stored procedures Transact-SQL SQL Server

提问by Matthew Farwell

I'm using SQL Server 2005, and I would like to know how to access different result sets from within transact-sql. The following stored procedure returns two result sets, how do I access them from, for example, another stored procedure?

我正在使用 SQL Server 2005，我想知道如何从 transact-sql 中访问不同的结果集。以下存储过程返回两个结果集，例如，如何从另一个存储过程访问它们？

CREATE PROCEDURE getOrder (@orderId as numeric) AS
BEGIN   
    select order_address, order_number from order_table where order_id = @orderId
    select item, number_of_items, cost from order_line where order_id = @orderId
END

I need to be able to iterate through both result sets individually.

我需要能够分别遍历两个结果集。

EDIT: Just to clarify the question, I want to test the stored procedures. I have a set of stored procedures which are used from a VB.NET client, which return multiple result sets. These are not going to be changed to a table valued function, I can't in fact change the procedures at all. Changing the procedure is not an option.

编辑：为了澄清这个问题，我想测试存储过程。我有一组从 VB.NET 客户端使用的存储过程，它们返回多个结果集。这些不会更改为表值函数，我实际上根本无法更改程序。改变程序不是一种选择。

The result sets returned by the procedures are not the same data types or number of columns.

程序返回的结果集不是相同的数据类型或列数。

采纳答案by Brannon

The short answer is: you can't do it.

简短的回答是：你做不到。

From T-SQL there is no way to access multiple results of a nested stored procedure call, without changing the stored procedure as others have suggested.

从 T-SQL 没有办法访问嵌套存储过程调用的多个结果，而不像其他人建议的那样更改存储过程。

To be complete, if the procedure were returning a single result, you could insert it into a temp table or table variable with the following syntax:

完整地说，如果过程返回单个结果，您可以使用以下语法将其插入到临时表或表变量中：

INSERT INTO #Table (...columns...)
EXEC MySproc ...parameters...

You can use the same syntax for a procedure that returns multiple results, but it will only process the first result, the rest will be discarded.

您可以对返回多个结果的过程使用相同的语法，但它只会处理第一个结果，其余的将被丢弃。

回答by Brannon

I was easily able to do this by creating a SQL2005 CLR stored procedure which contained an internal dataset.

通过创建一个包含内部数据集的 SQL2005 CLR 存储过程，我很容易做到这一点。

You see, a new SqlDataAdapter will .Fill a multiple-result-set sproc into a multiple-table dataset by default. The data in these tables can in turn be inserted into #Temp tables in the calling sproc you wish to write. dataset.ReadXmlSchemawill show you the schema of each result set.

您会看到，默认情况下，新的 SqlDataAdapter 会将多结果集 sproc 填充到多表数据集中。这些表中的数据可以依次插入到您希望编写的调用 sproc 中的 #Temp 表中。 dataset.ReadXmlSchema将显示每个结果集的架构。

Step 1: Begin writing the sproc which will read the data from the multi-result-set sproc

第 1 步：开始编写将从多结果集 sproc 读取数据的 sproc

a. Create a separate table for each result set according to the schema.

一种。根据架构为每个结果集创建一个单独的表。

CREATE PROCEDURE [dbo].[usp_SF_Read] AS
SET NOCOUNT ON;
CREATE TABLE #Table01 (Document_ID VARCHAR(100)
  , Document_status_definition_uid INT
  , Document_status_Code VARCHAR(100) 
  , Attachment_count INT
  , PRIMARY KEY (Document_ID));

b. At this point you may need to declare a cursor to repetitively call the CLR sproc you will create here:

湾 此时，您可能需要声明一个游标来重复调用您将在此处创建的 CLR sproc：

Step 2: Make the CLR Sproc

第 2 步：制作 CLR Sproc

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub usp_SF_ReadSFIntoTables()

    End Sub
End Class

a. Connect using New SqlConnection("context connection=true").

一种。使用New SqlConnection("context connection=true").

b. Set up a command object (cmd) to contain the multiple-result-set sproc.

湾 设置命令对象 (cmd) 以包含多结果集 sproc。

c. Get all the data using the following:

C。使用以下方法获取所有数据：

    Dim dataset As DataSet = New DataSet
    With New SqlDataAdapter(cmd)
        .Fill(dataset) ' get all the data.
    End With
'you can use dataset.ReadXmlSchema at this point...

d. Iterate over each table and insert every row into the appropriate temp table (which you created in step one above).

d. 迭代每个表并将每一行插入适当的临时表（您在上面的第一步中创建）。

Final note:In my experience, you may wish to enforce some relationships between your tables so you know which batch each record came from.

最后说明：根据我的经验，您可能希望在您的表之间强制执行一些关系，以便您知道每条记录来自哪个批次。

That's all there was to it!

这就是全部！

~ Shaun, Near Seattle

~ 肖恩，西雅图附近

回答by Daniel Barbalace

There is a kludge that you can do as well. Add an optional parameter N int to your sproc. Default the value of N to -1. If the value of N is -1, then do every one of your selects. Otherwise, do the Nth select and only the Nth select.

你也可以做一个kludge。将可选参数 N int 添加到您的 sproc。将 N 的值默认为 -1。如果 N 的值为 -1，则执行所有选择。否则，执行第 N 个选择并且仅执行第 N 个选择。

For example,

例如，

if (N = -1 or N = 0)
    select ...

if (N = -1 or N = 1)
    select ...

The callers of your sproc who do not specify N will get a result set with more than one tables. If you need to extract one or more of these tables from another sproc, simply call your sproc specifying a value for N. You'll have to call the sproc one time for each table you wish to extract. Inefficient if you need more than one table from the result set, but it does work in pure TSQL.

未指定 N 的 sproc 调用者将获得包含多个表的结果集。如果您需要从另一个 sproc 中提取这些表中的一个或多个，只需调用您的 sproc 并指定 N 的值。您必须为要提取的每个表调用一次 sproc。如果您需要来自结果集中的多个表，则效率低下，但它确实可以在纯 TSQL 中工作。

回答by Chris Wuestefeld

Note that there's an extra, undocumented limitation to the INSERT INTO ... EXEC statement: it cannot be nested. That is, the stored proc that the EXEC calls (or any that it calls in turn) cannot itself do an INSERT INTO ... EXEC. It appears that there's a single scratchpad per process that accumulates the result, and if they're nested you'll get an error when the caller opens this up, and then the callee tries to open it again.

请注意，对 INSERT INTO ... EXEC 语句有一个额外的、未公开的限制：它不能嵌套。也就是说，EXEC 调用的存储过程（或它依次调用的任何过程）本身不能执行 INSERT INTO ... EXEC。似乎每个进程都有一个暂存器来累积结果，如果它们是嵌套的，当调用者打开它时你会得到一个错误，然后被调用者尝试再次打开它。

Matthieu, you'd need to maintain separate temp tables for each "type" of result. Also, if you're executing the same one multiple times, you might need to add an extra column to that result to indicate which call it resulted from.

马修，您需要为每种“类型”的结果维护单独的临时表。此外，如果您多次执行相同的操作，您可能需要在该结果中添加一个额外的列来指示它来自哪个调用。

回答by Chris Wuestefeld

Sadly it is impossible to do this. The problem is, of course, that there is no SQL Syntax to allow it. It happens 'beneath the hood' of course, but you can't get at these other results in TSQL, only from the application via ODBC or whatever.

遗憾的是，这是不可能做到的。当然，问题是没有 SQL 语法允许这样做。它当然发生在“幕后”，但您无法在 TSQL 中获得这些其他结果，只能通过 ODBC 或其他方式从应用程序中获得。

There is a way round it, as with most things. The trick is to use ole automation in TSQL to create an ADODB object which opens each resultset in turn and write the results to the tables you nominate (or do whatever you want with the resultsets). you can also do it in DMO if you enjoy pain.

就像大多数事情一样，有一种方法可以解决它。诀窍是在 TSQL 中使用 ole 自动化创建一个 ADODB 对象，该对象依次打开每个结果集并将结果写入您指定的表（或对结果集执行任何您想要的操作）。如果您喜欢疼痛，也可以在 DMO 中进行。

回答by Josef

There are two ways to do this easily. Either stick the results in a temp table and then reference the temp table from your sproc. The other alternative is to put the results into an XML variable that is used as an OUTPUT variable.

有两种方法可以轻松做到这一点。要么将结果粘贴在临时表中，然后从您的 sproc 中引用临时表。另一种替代方法是将结果放入用作 OUTPUT 变量的 XML 变量中。

There are, however, pros and cons to both of these options. With a temporary table, you'll need to add code to the script that creates the calling procedure to create the temporary table before modifying the procedure. Also, you should clean up the temp table at the end of the procedure.

然而，这两种选择各有利弊。对于临时表，您需要将代码添加到创建调用过程的脚本中，以便在修改过程之前创建临时表。此外，您应该在程序结束时清理临时表。

With the XML, it can be memory intensive and slow.

使用 XML，它可能是内存密集型的，而且速度很慢。

回答by Kilhoffer

You could select them into temp tables or write table valued functions to return result sets. Are asking how to iterate through the result sets?

您可以将它们选择到临时表中或编写表值函数以返回结果集。正在询问如何遍历结果集？