Home Dashboard Directory Help

Metadata for stored procedures that return Multiple Recordsets (MARS ) by Alfred Yomtov


 as Won't Fix Help for as Won't Fix

Sign in
to vote
Type: Suggestion
ID: 773509
Opened: 12/5/2012 2:09:39 AM
Access Restriction: Public


The following procedure returns multiple recordsets:
        SELECT * FROM Authors
        SELECT * FROM Authors WHERE State = 'CA'

By calling NextResult () on the datareader, it is very simple to read the results from the procedure above:

myReader = myCommand.ExecuteReader
            While myReader.Read()
                RecordCount = RecordCount + 1
            End While
            MessageBox.Show("Total number of Authors: " & RecordCount.ToString)

            RecordCount = 0

            While myReader.Read()
                RecordCount = RecordCount + 1
            End While
MessageBox.Show("Authors from California: " & RecordCount.ToString)

But things come difficult then the stored procedure undergoes change in order of the recordsets inside it (or another recordset added before\ between the existing one) .
Sign in to post a comment.
Posted by Microsoft on 1/3/2013 at 11:25 AM
Hello Alfred,
Thanks for your feedback. I don't see us extending the metadata infrastructure like you suggested. Queries process metadata during compilation so we will need some sort of hint mechanism for that - it can't be done during execution. Also, it is trivial to distinguish the results by adding your own columnn like:

select 1 as result_type, * from authors;
select 2 as result_type, * from authors where ...;

Lastly, I am unclear on the benefits since the client has to fetch the first result set completely to get to the subsequent ones. Even if you were using MARS, you need to submit different queries. Either case requires intricate knowledge of the results from the client side so this is something that should be handled by the application code.

Umachandar, SQL Programmability Team
Sign in to post a workaround.