sql-server - that - stored procedure to get data from table in sql server




Retrieve data from stored procedure which has multiple result sets (5)

Given a stored procedure in SQL Server which has multiple select statements, is there a way to work with those results separately while calling the procedure?

For example:

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

In .NET, if I call this proc, I can use a SqlDataReader to move between the two result sets, so I can easily retrieve all people and cars. In SQL however, when I execute the proc directly, I get both result sets.

If I call:

insert @myTempTable
    exec dbo.GetSomething;

Then it errors because the column definition doesn't match. If by some chance Person and Car have the same columns, it concatenates the two together, and @myTempTable gets all records from both tables, which obviously is no good either.

I can define new custom types representing the two result sets, and make those output parameters instead of having the multiple select statements, but I'm wondering if there's a better way - some way of pulling both results into temporary tables, or looping through the results, or something.

EDIT

Actually, after looking more closely, even output table parameters won't solve this - they're readonly, and that's still true in SQL 2012. (Connect ticket asking for this to be added)


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.

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

It seems like there's no good simple way to do this, without a hack or a major paradigm shift. It looks like the best way is to just split out the original procs and end up with one more proc than before:

Old way:

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

New way:

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;

Then when I'm in a different proc and need both result sets, I'd just have to call them one at a time.


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.


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




stored-procedures