returning multiple records sets from a stored procedure? 1

[DOGWATCH]

Question, I am creating some web based reports in ASP with
data from SQL 2000.

I have created a stored procedure which will create three
dynamic temp tables called

#Vehicles
#Onyx
#Sales

And data in teach of the tables is populated.

My question is, can I return mutiple record sets from the stored procedure and display all 3 of them in ASP?

If I run the stored procedure, the last few lines in my procedure wil just do a select * statement on each of the populated tables and if I am running this in query analyser for example. Yes it will return the data from each table,
However this procedure would normally be executed from a web page.

So either, there is some way I can return all 3 record sets from the procedure and dispay them in on an on my ASP page

OR I could also just create 3 separate store procedures that just return 1 recordset and execute these inivividually. I know for certain that would work. However, making a separate stored procedure for each recordset doesn't seem like a very efficent way of doing things.

Any ideas?? I just might also be able to do some sort of outter join statement on all these tables to get one table, but the data in each table is fairly unique so this may not be an option.

 

[TJRTech]

You can do either...three recordsets and use ADO and it's NextRecordset (I think that is the method call) or three different SPs each with one recordset.

TR

 

[DOGWATCH]

oh? I haven't seen that ADO procedure before, I will
look up the syntax and give it a shot.

 

[TJRTech]

ADO and its NextRecordset is probably the way to go then, because it is only one round-trip.

Good luck,

TR

 

[gavinhuet]

Not sure if that is one round trip or 3...

I remember reading some time ago (i.e. years) that when NextRecordset is called the next statement to return a recordset is executed, and not when the original call is done. You may need to double check the ADO documentation regarding this.

 

[TJRTech]

Well, if calling a stored proc, and said stored proc has 3 select statements in it, I don't see how it could be anything else than one round trip, with 3 recordsets returned; unless there is a server-side cursor at play, and NextRecordset simply goes out to the server and gets the next recordset in batch. Dunno. Would be interesting to watch it in SQLProfiler.

Regards,
TR

 

[DOGWATCH]

NextRecordset method worked like a champ. In fact there are a number of reports I have developed in the past that would have benefited from using this.

Basically I will create multiple temp tables and fill the data in as required in my stored proc. Then at the bottom just do multiple select * statements on each table.

In ASP I use a while loop and then NextRecordset to move the the next set of data.

 

[TJRTech]

Glad to hear it. I have used it the same way in the past.

Regards,
TR

 

[gavinhuet]

OK, this has been bugging me for a couple of days now.

Let us take the case of the following SQL statement

SELECT * FROM table1;SELECT * FROM table2

As I said earlier I believed that using ADO's NextRecordset would mean that each statement is only executed when the NextRecordset function is called i.e. there will be more than one round-trip (one for each statement to be exact).

I have confirmed this to be the case, from the MSDN website:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthnextrec.asp

the first paragraph under the Remarks section says:

Use the NextRecordset method to return the results of the next command in a compound command statement or of a stored procedure that returns multiple results. If you open a Recordset object based on a compound command statement (for example, "SELECT * FROM table1;SELECT * FROM table2") using the Execute method on a Command or the Open method on a Recordset, [highlight]ADO executes only the first command and returns the results to recordset. To access the results of subsequent commands in the statement, call the NextRecordset method.[/highlight]

Now using query analyser the result is different, there is only one round-trip for the 2 selects.

In conclusion, I would not use NextRecordset to retrieve more than one recordset simply because of the extra code involved.
I would however use a multiple statement executed as a string value to, for example, update multiple tables. This would result in only one round trip.

-- Gavin

 

[TJRTech]

Thanks for the work Gavin.

But, I think you have confirmed your supposition and the documented behavior for compound statements like the one given in the example:

"SELECT * FROM table1;SELECT * FROM table2"

But, I don't see how it could work that way for Stored Procs; and the documentation even alludes to that when it says "of a stored procedure that returns multiple resultsets".

In other words, the text you highlighted in yellow can't seem to hold true for a stored proc because the stored proc is a single command.

I suspect that in the stored procedure case, which is the context of this thread, that the stored procedure runs to completion with the first (only) execute from ADO, and that the first recordset is returned (depending on the cursor type being used); then the only question is are the remaining recordsets cached on the server or are they also sent to the client. The answer to that last question, I suspect, again depends on the type of cursor/connection you are using.

The way to find out is to turn on SQLProfiler and what what happens when you call such an SP from ADO and use NextRecordset (sorry, I don't have the time for that right now).

Regards,
TR

 

[DOGWATCH]

In conclusion, I would not use NextRecordset to retrieve more than one recordset simply because of the extra code involved."

well the alternative for me was to have written 3 separate stored procedures then call each one individually from ASP.
There is enough ASP code that goes into calling 1 procedure let alone 3 or 4.

If your creating dozens and dozends of reports in ASP with SQL backend as I do multiplied by X number of stored procs to handle each recordset. For me at least its easier to have one stored proc. per report. I don't care if its more overhead for SQL, its less code for me to manage.

 

[gavinhuet]

TJRTech, I agree with what you are saying. And like you I am confused by that particular line alluding to stored procedures.
I have looked at both from sql profiler and they both look as though they are executed at once with all the results returned to ADO, however the documentation says that this is not the case.
We have had similar issues here where we were expected an error to be returned from a stored procedure which calls another stored procedure etc etc (via RAISERROR) and this was not happening, in the end we traced it down to one of the stored procedures had a SET NOCOUNT OFF at the end which meant that multiple recordsets where now being returned which we had not catered for, this meant that the error was not being raised because ADO had not executed it yet, even though the profiler has told us that the statement had completed, and the next few lines of SQL where not being executed either.

All the more confusing.

-- Gavin

 

[TJRTech]

Yes...the documented behavior is not always the real behavior.

I expected that MS SQL would run all the commands and return all the recordsets if using a client-side cursor; all with one round-trip...this has always been my expectation regardless of any documentation stating the contrary because:

1. It is akin to the standard ODBC mechanism of supporting multiple recordsets

2. The tabular data set (TDS) standard that SQL Server is based on also supports multiple record sets.

So, as I said, I always assumed that if using ADO to a SQL Server db and using a client-side cursor that all info was sent back for all recordsets at once.

Glad to see what you are seeing seems to confirm that.

Regards,
TR