Would a stored procedure return more than one recordset or not

Transact-SQL
1

I need receive multiple recordsets at the same time. Can a stored procedure do this?

Thank for your help !

2

A stored procedure can return as many resultsets as you want. These are difficult to process in t-sql but easy with the ADO.Net datareader NextResult method.

3

Thank for your answer.
But I want to know more detail.
For example, I have a stored procedure like this:

CREATE PROCEDURE storename
(params)
AS
BEGIN
/* Process blocks /
/ Results saved in 2 temp table /
/ I want to return both temp tables by using:
SELECT * FROM #temp1
SELECT * FROM #temp2
Is this OK?
*/
END

In .NET, I'll a ADO.NET DataReader to receive them (with NextResult method), won't I?

4

Yes, definitely OK :slight_smile:

I don't know ADO.NET, but the concept of "Next Result Set" is definitely how you "get" to the second (and subsequent) resultsets.

We have a centralised function to do the "Next resultset thing" which includes handling these scenarios:

  • I expected a second resultset, but there isn't one (there was an "End of resultsets" marker instead)
  • I only expected two resulsets, but there is a third one (probably want to know about this!)

It also handles "I got an error, along with a resulset for the Error Message(s)", and things like

SELECT MAX(MyCol)
FROM MyTable

where MyCol has NULL values and this statement returns a WARNING message about that (that too can interfere with the whole "Next Resultset" process :frowning: )

Put

SET NOCOUNT ON

at the top of (inside) your SProc. Otherwise all the "1 row(s) affected)" messages will also get in the way :frowning:

And if you have any PRINT statements (e.g. for Debugging) they can muck things up too ...

Anyway, our "GetNextResultSet()" function takes care of handling all those situations, so I recommend centralising that bit of your code so you can "improve" it over time as you fall into each of these pitfalls.

maybe ADO.NET includes solutions for all those GotChas without you having to write a line of code?!!