从具有多个结果集的存储过程中检索数据

Given a stored procedure in SQL Server which has multiple select statements, is there a way to work with those results separately while calling the procedure?

给定SQL Server中有多个select语句的存储过程，有没有办法在调用过程时单独使用这些结果？

For example:

例如：

alter procedure dbo.GetSomething
as
begin
    select * from dbo.Person;
    select * from dbo.Car;
end;

In .NET, if I call this proc, I can use a SqlDataReader to move between the two result sets, so I can easily retrieve all people and cars. In SQL however, when I execute the proc directly, I get both result sets.

在.NET中，如果我调用此proc，我可以使用SqlDataReader在两个结果集之间移动，这样我就可以轻松检索所有人和汽车。但是在SQL中，当我直接执行proc时，我得到两个结果集。

If I call:

如果我打电话：

insert @myTempTable
    exec dbo.GetSomething;

Then it errors because the column definition doesn't match. If by some chance Person and Car have the same columns, it concatenates the two together, and @myTempTable gets all records from both tables, which obviously is no good either.

然后它会出错，因为列定义不匹配。如果Person和Car有一些机会有相同的列，它将两者连接在一起，@ myTempTable从两个表中获取所有记录，这显然也不好。

I can define new custom types representing the two result sets, and make those output parameters instead of having the multiple select statements, but I'm wondering if there's a better way - some way of pulling both results into temporary tables, or looping through the results, or something.

我可以定义代表两个结果集的新自定义类型，并创建那些输出参数而不是具有多个select语句，但我想知道是否有更好的方法 - 某种方式将两个结果拉入临时表，或循环遍历结果，或者其他什么。

EDIT

编辑

Actually, after looking more closely, even output table parameters won't solve this - they're readonly, and that's still true in SQL 2012. (Connect ticket asking for this to be added)

实际上，在仔细观察之后，即使输出表参数也无法解决这个问题 - 它们只是读取，而且在SQL 2012中仍然如此。（连接票证要求添加此项）

6 个解决方案

#1

String myConnString  = "User ID="username";password="password";Initial Catalog=pubs;Data Source=Server";
SqlConnection myConnection = new SqlConnection(myConnString);
SqlCommand myCommand = new SqlCommand();
SqlDataReader myReader ;

myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Connection = myConnection;
myCommand.CommandText = "MyProc";

try
{
    myConnection.Open();
    myReader = myCommand.ExecuteReader();

    while (myReader.Read())
    {
        //Write logic to process data for the first result.   
        }

    myReader.NextResult();
    while (myReader.Read())
    {
        //Write logic to process data for the second result.
    }
}

#2

In TSQL land, you're stuck.

在TSQL的土地上，你被困住了。

Here is a trick (some may call semi-hacky) way that I used one time.

这是我用过一次的伎俩（有些人可能称之为半hacky）。

/*  START TSQL CODE */

/*  Stored Procedure Definition */

Use Northwind
GO


IF EXISTS 
    (
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspOrderDetailsByCustomerId'  
    )
BEGIN
    DROP PROCEDURE [dbo].[uspOrderDetailsByCustomerId]
END


GO

CREATE Procedure dbo.uspOrderDetailsByCustomerId
(
  @CustomerID nchar(5)
, @ResultSetIndicator smallint = 0
)
AS

BEGIN

    SET NOCOUNT ON



    /* ResultSet #1 */

    if (@ResultSetIndicator = 0 OR @ResultSetIndicator = 1)
    BEGIN 
        SELECT 
            c.CustomerID, c.CompanyName /*,c.ContactName,c.ContactTitle,c.[Address],c.City,c.Region,c.PostalCode,c.Country ,c.Phone,c.Fax */
        FROM 
            Customers c 
            JOIN Orders o ON c.CustomerID = o.CustomerID 
        WHERE 
            c.CustomerID = @CustomerID
    END


    /* */
    /* ResultSet #2 */ 

    if (@ResultSetIndicator = 0 OR @ResultSetIndicator = 2)
    BEGIN 

        SELECT o.OrderID,o.CustomerID /* ,o.EmployeeID,o.OrderDate,o.RequiredDate,o.ShippedDate,o.ShipVia ,o.Freight,o.ShipName,o.ShipAddress,o.OrderID,o.CustomerID,o.EmployeeID,o.OrderDate  */
        FROM 
            Orders o 
         WHERE 
            o.CustomerID = @CustomerID
        ORDER BY 
            o.CustomerID , o.OrderID 

    END


    /* */
    /* ResultSet #3 */

    if (@ResultSetIndicator = 0 OR @ResultSetIndicator = 3)
    BEGIN 
         SELECT od.OrderID,od.ProductID /* ,od.UnitPrice,od.Quantity,od.Discount  */
         FROM 
            [Order Details] od 
         WHERE 
            exists (select null from dbo.Orders  innerOrds where innerOrds.OrderID = od.OrderID and innerOrds.CustomerID = @CustomerID )
         ORDER BY 
            od.OrderID 

    END

    SET NOCOUNT OFF


END

GO 
/* Get everything */


exec dbo.uspOrderDetailsByCustomerId 'ALFKI'




    IF OBJECT_ID('tempdb..#TempCustomer') IS NOT NULL
    begin
            drop table #TempCustomer
    end


    CREATE TABLE #TempCustomer
    ( 
      [CustomerID] nchar(5)
    , [CompanyName] nvarchar(40)
    )

INSERT INTO #TempCustomer ( [CustomerID] , [CompanyName])
exec dbo.uspOrderDetailsByCustomerId 'ALFKI' , 1

Select * from #TempCustomer



    IF OBJECT_ID('tempdb..#TempOrders') IS NOT NULL
    begin
            drop table #TempOrders
    end


    CREATE TABLE #TempOrders
    ( 
        OrderID int
      , [CustomerID] nchar(5)

    )

INSERT INTO #TempOrders ( OrderID , [CustomerID] )
exec dbo.uspOrderDetailsByCustomerId 'ALFKI' , 2

Select * from #TempOrders






    IF OBJECT_ID('tempdb..#TempOrderDetails') IS NOT NULL
    begin
            drop table #TempOrderDetails
    end


    CREATE TABLE #TempOrderDetails
    ( 
        OrderID int
      , [ProductID] int

    )

INSERT INTO #TempOrderDetails ( OrderID , [ProductID] )
exec dbo.uspOrderDetailsByCustomerId 'ALFKI' , 3

Select * from #TempOrderDetails


    IF OBJECT_ID('tempdb..#TempOrderDetails') IS NOT NULL
    begin
            drop table #TempOrders
    end


    IF OBJECT_ID('tempdb..#TempOrders') IS NOT NULL
    begin
            drop table #TempOrders
    end



    IF OBJECT_ID('tempdb..#TempCustomer') IS NOT NULL
    begin
            drop table #TempCustomer
    end

#3

While this does not appear to be supported natively in T-SQL, if using a CLR Stored Procedure is an option for you, then you should be able to create a Stored Procedure in your preferred .Net language that uses the SqlDataReader.NextResult() method to advance to the desired result set and then send the SqlDataReader back via the SqlPipe.Send(SqlDataReader) method. You would just need to pass in the SQL to execute and the desired result set as parameters to this proc.

虽然在T-SQL中似乎没有本地支持这一点，但如果您使用CLR存储过程是一个选项，那么您应该能够使用SqlDataReader.NextResult（）在您首选的.Net语言中创建存储过程。进入所需结果集的方法，然后通过SqlPipe.Send（SqlDataReader）方法返回SqlDataReader。您只需要将SQL执行并将所需的结果集作为参数传递给此proc。

This would allow you to work with the proc as is, without modifying it to send back all or only one result set.

这将允许您按原样使用proc，而无需修改它以发送回全部或仅一个结果集。

#4

It seems like there's no good simple way to do this, without a hack or a major paradigm shift. It looks like the best way is to just split out the original procs and end up with one more proc than before:

似乎没有一个简单的方法可以做到这一点，没有黑客或重大的范式转变。看起来最好的方法是拆分原始的procs并最终得到一个比以前更多的proc：

Old way:

旧方式：

create procedure dbo.GetSomething
as
begin
    select * from dbo.Person;
    select * from dbo.Car;
end;

New way:

新方法：

create procedure dbo.GetPeople
as
begin
    select * from dbo.Person;
end;

create procedure dbo.GetCars
as
begin
    select * from dbo.Car;
end;

-- This gives the same result as before
create procedure dbo.GetSomething
as
begin
    exec dbo.GetPeople;
    exec dbo.GetCars;
end;

Then when I'm in a different proc and need both result sets, I'd just have to call them one at a time.

然后，当我处于不同的过程并且需要两个结果集时，我只需要一次调用它们。

#5

You can put multiple result set in form of xml to a table

您可以将多个结果集以xml的形式放入表中

So that when you want to access all these result you parse those result set column to a tabular form

因此，当您想要访问所有这些结果时，您将这些结果集列解析为表格形式

#6

Der. Read the whole question before writing an answer! :-P

明镜。在写答案之前阅读整个问题！ :-P

If you're trying to work with the results in TSQL land you're going to need to use some way to keep the results separate. Writing results to Temp tables is possibly your best bet since you won't need to depend on columns lining up (or not, as the case may be) and can deal with the data in a "natural" fashion for SQL Server. E.g.

如果您尝试在TSQL中使用结果，则需要使用某种方法将结果分开。将结果写入Temp表可能是您最好的选择，因为您不需要依赖排列的列（或不是，视情况而定）并且可以以“自然”方式处理SQL Server的数据。例如。

create proc test_something
as begin
    select a, b into temp1 from table1
    select b, c into temp2 from table2
end
go

exec dbo.test_something()

select * from temp1
select * from temp2