Getiing multiple resultset from a stored procedure

Question

I have a stored procedure that contains multiple selects a bit like this:

select 'Connected'
exec DoWork
  @var1 = 23,
  @var2 = 400
select 'Done'
select 'Bye'

When I call the stored procedure from my C# code I only get a single result in my SqlDataReader and it is the 'Connected' select.

I call it like this

SqlCommand command = connection.CreateCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "SP_MyProc";
command.Parameters.Add("@val", "TEST");
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
    string temp = "";

    for (int i = 0; i < reader.FieldCount; i++)
    {
       temp += reader.GetString(i) + " ";
    }

    worker.ReportProgress(0, temp);
}

connection.Close();

It makes sense that normally a stored procedure only returns a single result set, however I need this for debugging purposes. Is it possible to do?

Check this out.... http://stackoverflow.com/questions/12969318/multiples-table-in-datareader — Adarsh Shah, Dec 30 '13 at 12:43
okay as suggested here is the comment instead of answer. use the SqlDataReader.NextResult method :) — ZedBee, Dec 30 '13 at 12:53
@ZedBee: your answer was perfect as an answer. Some people just don't understand that you can not classify a response as an answer or comment simply by the length of the text. Some answers do not require lengthy explanations. — Sam Axe, Feb 13 '15 at 09:14

score 2 · Accepted Answer · answered Dec 30 '13 at 12:48

2

use the SqlDataReader NextResult

answered Dec 30 '13 at 12:48

ZedBee

2,236
6
38
58

This is more of a comment! – huMpty duMpty Dec 30 '13 at 12:52

Getiing multiple resultset from a stored procedure

1 Answers1