locked
how stored procedures return multiple result sets? RRS feed

  • Question

  • hi

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

    thanks in advance.
    Sunday, January 28, 2007 3:29 PM

Answers

  • 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

    Monday, January 29, 2007 11:45 AM

All replies

  • 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

    Monday, January 29, 2007 8:43 AM
  • 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.
    Monday, January 29, 2007 11:41 AM
  • 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

    Monday, January 29, 2007 11:45 AM