从存储过程 Transact-SQL SQL Server 中访问结果集 [英] Access to Result sets from within Stored procedures Transact-SQL SQL Server

问题描述

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

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?

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.

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

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.

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

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

推荐答案

简短的回答是:你做不到.

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

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

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.

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

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.

这篇关于从存储过程 Transact-SQL SQL Server 中访问结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！