Recuperar datos del procedimiento almacenado que tiene múltiples conjuntos de resultados

Dado un procedimiento almacenado en SQL Server que tiene múltiples instrucciones de select , ¿hay alguna manera de trabajar con esos resultados por separado al invocar el procedimiento?

Por ejemplo:

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

En .NET, si llamo a este proceso, puedo usar un SqlDataReader para moverme entre los dos conjuntos de resultados, de modo que pueda recuperar fácilmente a todas las personas y autos. Sin embargo, en SQL, cuando ejecuto el proceso directamente, obtengo ambos conjuntos de resultados.

Si llamo:

 insert @myTempTable exec dbo.GetSomething; 

Luego se produce un error porque la definición de la columna no coincide. Si por casualidad Person and Car tienen las mismas columnas, concatena las dos juntas, y @myTempTable obtiene todos los registros de ambas tablas, lo que obviamente tampoco es bueno.

Puedo definir nuevos tipos personalizados que representen los dos conjuntos de resultados, y crear esos parámetros de salida en lugar de tener las múltiples declaraciones de select , pero me pregunto si hay una forma mejor: alguna forma de convertir ambos resultados en tablas temporales o pasar por el resultados, o algo.

EDITAR

En realidad, después de mirar más de cerca, incluso los parámetros de la tabla de salida no resolverán esto, son de solo lectura, y eso sigue siendo cierto en SQL 2012. ( Conecte el ticket pidiendo que se agregue )

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

En TSQL Land, estás estancado.

Aquí hay un truco (algunos pueden llamar semi-hacky) forma que usé una vez.

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

Si bien esto no parece ser compatible de forma nativa en T-SQL, si el uso de un Procedimiento almacenado de CLR es una opción para usted, entonces debería poder crear un Procedimiento almacenado en su idioma .Net preferido que use SqlDataReader.NextResult() método para avanzar al conjunto de resultados deseado y luego enviar el SqlDataReader a través del SqlPipe.Send(SqlDataReader) . Solo necesita pasar el SQL para ejecutar y el resultado deseado como parámetros para este proceso.

Esto le permitiría trabajar con el proceso como está, sin modificarlo para devolver todo o solo un conjunto de resultados.

Parece que no hay una buena forma simple de hacerlo, sin un hack o un gran cambio de paradigma. Parece que la mejor manera es simplemente dividir los procesos originales y terminar con un proceso más que antes:

Vieja forma:

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

Nueva manera:

 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; 

Entonces, cuando esté en un proceso diferente y necesite ambos conjuntos de resultados, tendré que llamarlos uno a la vez.

Puede poner un conjunto de resultados múltiples en forma de xml en una tabla

Para que cuando quiera acceder a todos estos resultados, analice la columna del conjunto de resultados en una forma tabular

Der. Lee toda la pregunta antes de escribir una respuesta. :-PAG

Si intentas trabajar con los resultados en el terreno TSQL, vas a necesitar usar alguna forma de mantener los resultados separados. Escribir los resultados en tablas Temp es posiblemente la mejor opción, ya que no necesitará depender de columnas alineadas (o no, según sea el caso) y podrá tratar los datos de una manera “natural” para SQL Server. P.ej

 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 

Cree un SqlDataAdapter, configure su SelectCommand para ejecutar el SP “GetSomething” y luego use el adaptador de datos para completar un DataSet. El DataSet contendrá tantas DataTable como usted tenga instrucciones de “selección” que devuelvan conjuntos de registros desde el SP.

Así es como se vería tu código:

 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.