Btukfyl

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 TSQL land, you're stuck.

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

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

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.

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

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

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

Der. Read the whole question before writing an answer! :-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.

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

Would passing a parameter to the sp do the trick

----------------------

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

Create an SqlDataAdapter, set its SelectCommand to execute the SP "GetSomething", and then use the data adapter to fill a DataSet. The DataSet will contain as many DataTable's as you have "select" statements returning recordsets from the SP.

Here's what your code would look like:

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.