how stored procedures return multiple result sets?

Question

hi

i read that stored procedures can return multiple result sets?how is that?

thanks in advance.

Answer 1

Returning multiple result sets
You can have multiple queries inside a single sproc, as shown in the following sproc code:
 
ALTER PROCEDURE MultipleResultSets
AS
Select * from Products;
Select * from Customers;

hth

Barry Andrew

Answer 2

thanks for reply
so if i have 2 select statements how to view the results in 2 different grids or something like that?

thanks in advance.

Answer 3

Hi,

Once you use MyCommandObject.ExecuteNonQuery() to execute your SP The datareader object returned by ExecuteReader() has a "NextResult()" method. Simply call NextResult() in a loop until you go through all the results. In sort-of-VB:

reader = command.ExecuteReader()
do
do while reader.Read()
   ' process the row
loop
loop while reader.NextResult() ' this moves to the next result-set