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

Question

在 SQL Server 中有一个存储过程，其中包含多个select语句，是否有一种方法可以在调用该存储过程时分别处理这些结果？

例如：

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

在.NET中，如果我调用这个过程，我可以使用SqlDataReader在两个结果集之间移动，因此我可以轻松检索所有人员和汽车。然而，在SQL中，当我直接执行该过程时，我会得到两个结果集。

如果我调用：

insert @myTempTable
    exec dbo.GetSomething;

由于列定义不匹配，因此它会出现错误。如果 Person 和 Car 恰巧具有相同的列，则将两者连接在一起，并且 @myTempTable 从两个表中获取所有记录，这显然也是不好的。

我可以定义新的自定义类型来表示这两个结果集，并使它们成为输出参数，而不是使用多个 select 语句，但我想知道是否有更好的方法-某种方式将两个结果汇总到临时表中，或循环遍历结果，或其他什么方式。

编辑

实际上，在仔细查看后，即使输出表参数也无法解决此问题-它们是只读的，在 SQL 2012 中仍然如此。 （Connect 票据要求添加此内容）

- Joe Enos

---

不。如果您想从一个存储过程中使用单个结果集，那么您只能返回一个结果集。 - Kermit

令人惊讶的是，正如您所提到的那样，您可以使用SqlDataReader从ADO.NET应用程序中访问多个结果集 - 但是您无法从T-SQL访问多个结果集.....这对我来说从未有过意义，但事实就是这样（至少目前是这样）。 - marc_s

假设你的Car和Person表都有一个名为"type"的列，新表不能有两个名为"type"的列。你可以在其中一个SELECT语句中显式地列出列名，例如：SELECT type AS carType from Car。 - NoChance

@EmmadKareem 它不会创建一个包含两组列的大结果集 - 如果两个查询恰好具有相同的列，则在从过程中选择时，您将获得联合的结果集。即使这样，在我想要这两个查询给出相同类型的结果时，它也只有潜在的用处。这个想法是这两个查询给出完全不相关的结果，不同数量和类型以及列名。 - Joe Enos

感谢您的澄清，我重新阅读了您的文字，我想到了其他事情。 - NoChance

Answer 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.
    }
}

- Mahesh Gaikwad

---

myReader.NextResult(); //返回第二个结果数据 - Mahesh Gaikwad

Answer 2

看起来没有好的简单方法可以做到这一点，除非采用hack或者进行重大范式转换。最好的方法似乎是将原始过程分离出来，最终得到比之前多一个过程：

旧方法：

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

新方法：

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;

然后，当我在不同的进程中需要这两个结果集时，我只需要一个接一个地调用它们。

- Joe Enos

Answer 3

在TSQL领域，你会陷入困境。

以下是我曾用过的一种技巧（有些人可能称之为半黑客方式）。

/*  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

- granadaCoder

---

这是一个有趣的想法，谢谢。正如你所提到的，有点hackish，但看起来应该可以工作。 - Joe Enos

我曾经在继承的一些代码上使用过它，但我绝对不建议将其作为最佳实践。不过它确实能够工作。 - granadaCoder

啊...我明白你的意思了。这肯定会帮助我摆脱一个棘手的问题，因为我只需要从特殊位置访问一个结果集。 - Jaans

Answer 4

尽管T-SQL本身似乎不支持此功能，但如果您可以使用CLR存储过程，则可以使用首选的.NET语言创建一个存储过程，该存储过程使用SqlDataReader.NextResult()方法前进到所需的结果集，然后通过SqlPipe.Send(SqlDataReader)方法将SqlDataReader发送回。您只需要将要执行的SQL和所需的结果集作为参数传递给此存储过程即可。

这样可以让您直接使用存储过程，而无需修改它以返回所有或仅一个结果集。

- Solomon Rutzky

---

谢谢 - 我不是CLR过程的铁粉，因为它们很难处理，但这听起来是一个有趣的方法。 - Joe Enos

Answer 5

您可以将多个结果集以 XML 格式放入表中。

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

- abdulla wasay

---

有性能影响吗？我的第一个假设是这可能会相当昂贵（CPU、内存等）。 - undefined

Answer 6

请在回答之前先阅读整个问题！:-P

如果您想在TSQL环境中处理结果，您需要使用某种方法来保持结果的分离。将结果写入临时表可能是最好的选择，因为您不需要依赖列的对齐（或不对齐，视情况而定），并且可以以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

- Steve G

---

除非在调用过程之前创建temp1和temp2，否则这将无法起作用。在子进程中创建的临时表在返回到调用进程时会被删除。 - Solomon Rutzky

这在我的SQL Server 2008中有效。没有必要在存储过程外部创建temp1和temp2，因为它们实际上不是临时表，而是真实的表。当然，现在你必须自己清理...并且，一如既往，结果可能会有所不同。 :) - Steve G

如果该表实际上不是真正的临时表，那么可能会有2个或更多用户需要同时访问它的情况。如果输入参数不同，则至少一个人得到的结果将是错误的。 - roblem

Answer 7

传递一个参数给存储过程会起到作用吗？

CREATE PROCEDURE  dostuff @parm1 int
AS

BEGIN
Declare @resultset Int
Set @resultset = @parm1

--0 = Select ranks
--1 = Select suits
--other - Select ALL

If @resultset = 0 
 SELECT [rank] FROM [ranks]
 Else If @resultset = 1
 SELECT [suit] FROM [suits]
 Else 
 SELECT * FROM [suits]
 cross join   [ranks] 
END
GO

 declare @mytemptbl table (rank text)
 insert @mytemptbl
  exec dostuff 0

 select * from @mytemptbl

- level3looper

---

作为参数提供的表变量必须标记为只读，不能被存储过程更新。 - user2864740

Answer 8

一个可行的方法是反转过程，让存储过程接受临时表的名称，并使用动态SQL将所需结果填充到临时表中。

当存储过程返回时，调用者可以访问临时表中包含的数据。

-- local temporary tables avoid leaking to other sessions
create table #fast_cars (name varchar(max), top_speed float);
create table #awesome_people (name varchar(max), age int);

-- invoked SPs can access local temporary tables in scope
exec GetAmazingThings @name='%Wonder%'
    ,@cars='#fast_cars'
    ,@people='#awesome_people'

-- use 'returned' data
select name, top_speed from #fast_cars;
select name, age from #awesome_people;

接受表名可以减少“魔法知识”，因为名称是显式提供的，知道哪些表受到影响。它还允许在维护隔离性的同时收集多个调用的结果，包括在嵌套期间。

存储过程可能看起来有点像...

create procedure GetAmazingThings
    @name varchar(100),
    -- output table names..
    ,@cars varchar(100)
    ,@people varchar(100)
as
    set @cars = quotename(@cars); -- bobby is not welcome
    declare @sql nvarchar(max);

    declare #_cars (name varchar(max), top_speed float);

    -- perform queries into local temp tables
    -- (this could also be done as the dynamic SQL to avoid a copy)
    insert into #_cars (name, top_speed)
    select Name, max(LapSpeed)
    from TonkaToys
    where Name like @name and VehicleType = 'car'
    group by Name;

    if patindex('[[]#%', @cars) > 0 -- given temp table
    begin
        -- copy result to supplied temp table
        set @sql = concat(N'
insert into ', @cars, ' (name, top_speed)
select name, top_speed
from #_cars
');
        exec sp_executesql @sql;
    end
    else
    begin
        -- just select result
        select name, top_speed from #cars
    end

    -- ditto for @people query/results
go

注意：

• 在临时表之间至少存在一个（可能更多）数据副本。
• 如果将动态SQL与主查询隔离开来，则SP会更加清晰。首先查询到一个本地临时表，然后使用动态SQL将其复制到提供的临时表中。
• 如果使用全局临时表，则SP可以创建所需的表结果集。但是，虽然方便，但这可能会有问题，因为全局临时表在会话之间共享。
• 参数也可以用于控制SP“返回”的内容，例如跳过查询或选择作为结果集而不是写入到临时表中。

- user2864740

Answer 9

创建一个SqlDataAdapter，将其SelectCommand设置为执行SP“GetSomething”，然后使用数据适配器填充DataSet。 DataSet将包含与从SP返回记录集的“select”语句一样多的DataTable。

以下是您的代码示例：

System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = myConnectionObject;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetSomething";
da.SelectCommand = cmd;

System.Data.DataSet ds = new DataSet();
da.Fill(ds);
// at this point, the (ds) object contains DataTables created from the recordsets returned by the SP
DataTable dt0 = ds.Tables[0];
DataTable dt1 = ds.Tables[1];

// note that dt0 corresponds to the FIRST recordset returned by the SP, etc.

- Olivier RASSI

Answer 10

我知道这个问题我来晚了一点，但我只是想为其他遇到同样问题并需要其他选项的人提供帮助。

如果你有机会像Joe Enos所建议的那样将过程调用分割开来，那么我建议你这样做。但如果你没有这个选项，那么这个链接中的信息可能适合你。

https://khalidabuhakmeh.com/entity-framework-6-multiple-result-sets-with-stored-procedures

这是我处理一个无法分割且返回6个不同查询结果的过程调用所采取的方法。我采取的方法在很大程度上基于那篇文章中的信息，使得这个过程非常容易和可测试。

- nbstrat

---

目标似乎是不使用 .Net。它涉及到一个存储过程调用另一个返回多个结果集并需要访问所有结果集而不离开 MSSql 服务器进程边界的情况。 - roblem

问题 - 如我在回复中所述，这是专门针对其他人遇到此线程并需要另一种选择的情况。 我曾经处于那个确切的位置，并仅提供此信息作为数据库级别无法直接完成操作的另一种选择。 - nbstrat