Metadata for stored procedures that return Multiple Recordsets (MARS )

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

Closed

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

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

Workaround(s)

view

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 (expand)

English

SQL Engine

Metadata added to the procedure will help to differentiate between MARS, returned by stored procedure.

    CREATE PROC MyProc
    AS
        SELECT * FROM Authors
        sp_add_MARS_metadata MyProc, Authors

        SELECT * FROM Authors WHERE State = 'CA'
     sp_add_MARS_metadata MyProc, AuthorsCA
    GO

Faster Development

0 attachments

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