Home Dashboard Directory Help
Search

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


Status: 

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


3
0
Sign in
to vote
Type: Suggestion
ID: 773509
Opened: 12/5/2012 2:09:39 AM
Access Restriction: Public
0
Workaround(s)
view

Description


The following procedure returns multiple recordsets:
    CREATE PROC MyProc
    AS
        SELECT * FROM Authors
        SELECT * FROM Authors WHERE State = 'CA'
    GO

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)

            myReader.NextResult()
            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) .
Details
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.