Recupera i dati dalla stored procedure che ha più set di risultati

Data una stored procedure in SQL Server che ha più istruzioni select , esiste un modo per lavorare separatamente con questi risultati durante la chiamata della procedura?

Per esempio:

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

In .NET, se chiamo questo proc, posso usare un SqlDataReader per spostarmi tra i due set di risultati, così posso facilmente recuperare tutte le persone e le auto. In SQL tuttavia, quando eseguo direttamente il proc, ottengo entrambi i set di risultati.

Se chiamo:

 insert @myTempTable exec dbo.GetSomething; 

Quindi si commette errori perché la definizione della colonna non corrisponde. Se per caso Person e Car hanno le stesse colonne, concatena i due insieme e @myTempTable ottiene tutti i record da entrambe le tabelle, il che ovviamente non va bene neanche.

Posso definire nuovi tipi personalizzati che rappresentano i due set di risultati, e rendere quei parametri di output invece di avere le istruzioni multiple select , ma mi chiedo se c’è un modo migliore – un modo di tirare entrambi i risultati in tabelle temporanee, o di scorrere il risultati o qualcosa

MODIFICARE

In realtà, dopo aver esaminato più da vicino, anche i parametri della tabella di output non risolveranno questo problema: sono di sola lettura, e questo è ancora vero in SQL 2012. ( Connetti il ​​ticket per chiedere che venga aggiunto )

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

In terra TSQL, sei bloccato.

Ecco un trucco (qualcuno potrebbe chiamare semi-hacky) che ho usato una volta.

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

Anche se questo non sembra essere supportato in modo nativo in T-SQL, se l’utilizzo di una stored procedure CLR è un’opzione per te, dovresti essere in grado di creare una stored procedure nel tuo linguaggio .Net preferito che utilizza SqlDataReader.NextResult() metodo per passare al set di risultati desiderato e quindi inviare di nuovo SqlDataReader tramite il SqlPipe.Send(SqlDataReader) . Dovresti solo passare l’SQL da eseguire e il set di risultati desiderato come parametri per questo proc.

Ciò consentirebbe di lavorare con il proc così com’è, senza modificarlo per rimandare tutto o solo un set di risultati.

Sembra che non ci sia un buon modo semplice per farlo, senza un trucco o un cambiamento di paradigma. Sembra che il modo migliore sia quello di suddividere i proc originali e finire con un altro proc di prima:

Vecchio modo:

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

Nuovo modo:

 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; 

Poi, quando sono in un proc diverso e ho bisogno di entrambi i set di risultati, dovrei semplicemente chiamarli uno alla volta.

È ansible inserire più set di risultati in forma di xml su una tabella

In questo modo, quando si desidera accedere a tutti questi risultati, si analizza la colonna del set di risultati su un modulo tabulare

Der. Leggi l’intera domanda prima di scrivere una risposta! 😛

Se stai cercando di lavorare con i risultati in terra TSQL, avrai bisogno di usare un modo per mantenere separati i risultati. Scrivere i risultati in tabelle Temp è probabilmente la soluzione migliore dato che non è necessario dipendere da colonne in fila (o meno, a seconda dei casi) e può gestire i dati in modo “naturale” per SQL Server. Per esempio

 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 

Creare un SqlDataAdapter, impostare SelectCommand per eseguire SP “GetSomething”, quindi utilizzare l’adattatore dati per riempire un DataSet. Il DataSet conterrà il numero di DataTable quanti sono le istruzioni “select” che restituiscono i recordset dall’SP.

Ecco come sarà il tuo codice:

 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.