There are plenty of times when just a single query result of data returned just doesn’t quite cut it. You end up creating a second stored procedure to return some additional information that could be summary info, corresponding column names from a dynamic pivot, or any number of things.

I got tired of having a single set of logic split among a couple procedures…. so the Google search began: How can I get multiple result sets from a single stored procedure call. I eventually ran into MARS (Multiple Active Result Sets) which essentially allows an application to make several calls to the database under the same session – so any SQL Temp tables are still around upon the second, third, or even fourth requests. Not quite what I was looking for, and required additional connection string configuration… for more information on MARS check out: http://technet.microsoft.com/en-us/library/ms345109(SQL.90).aspx 

This was the simple solution I found a couple searches later:

Believe it or not thats it!! Just call the “NextResult()” on the data reader, and the second query returned from the Stored Procedure will be active. This can be used any number for any number of result sets, with zero additional configuration or setup. This proved insanely helpful when adding additional summary information on large volume of data.