Подтвердить что ты не робот

Извлечение данных из хранимой процедуры с несколькими наборами результатов

Учитывая хранимую процедуру в SQL Server, которая имеет несколько операторов select, есть ли способ работать с этими результатами отдельно при вызове процедуры?

Например:

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

В .NET, если я вызываю этот proc, я могу использовать SqlDataReader для перемещения между двумя результирующими наборами, поэтому я могу легко найти всех людей и автомобили. Однако в SQL, когда я выполняю proc напрямую, я получаю оба набора результатов.

Если я вызываю:

insert @myTempTable
    exec dbo.GetSomething;

Затем это ошибки, потому что определение столбца не совпадает. Если каким-то образом Person и Car имеют одинаковые столбцы, он объединяет их вместе, а @myTempTable получает все записи из обеих таблиц, что, очевидно, тоже не хорошо.

Я могу определить новые пользовательские типы, представляющие два набора результатов, и сделать эти выходные параметры вместо нескольких операторов select, но мне интересно, есть ли лучший способ - каким-то образом вывести оба результата во временные таблицы, или зацикливание результатов, или что-то еще.

ИЗМЕНИТЬ

На самом деле, посмотрев более внимательно, даже параметры выходной таблицы не решают этого - они только для чтения, и это все еще верно в SQL 2012. (Подключить билет с просьбой об этом добавляется)

4b9b3361

ОТВЕТЫ

Ответ 1

Кажется, что нет простого простого способа сделать это без взлома или серьезного сдвига парадигмы. Похоже, лучший способ состоит в том, чтобы просто отделить исходные procs и в итоге получить еще один proc, чем раньше:

Старый способ:

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;

Тогда, когда я буду в другом proc и вам нужны оба набора результатов, мне просто нужно будет их вызвать по очереди.

Ответ 2

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

Ответ 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

Ответ 4

Хотя это, как представляется, не поддерживается в T-SQL изначально, если вы используете хранимую процедуру CLR для вас, то вы должны иметь возможность создавать хранимую процедуру на предпочитаемом языке .Net, который использует SqlDataReader.NextResult(), чтобы перейти к желаемому набору результатов, а затем отправить SqlDataReader обратно с помощью метода SqlPipe.Send(SqlDataReader). Вам просто нужно пройти в SQL для выполнения и желаемый результат, заданный в качестве параметров для этого proc.

Это позволит вам работать с proc как есть, не изменяя его для отправки всего или только одного набора результатов.

Ответ 5

Вы можете поместить несколько результирующих наборов в виде xml в таблицу

Таким образом, когда вы хотите получить доступ ко всем этим результатам, вы проанализируете столбец результатов с табличной формой

Ответ 6

Der. Прочтите весь вопрос, прежде чем писать ответ!:-P

Если вы пытаетесь работать с результатами на земле 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