Stack Recordset Returns

If you're getting a bunch of data for an ASP page, you can retrieve all the data through a single stored procedure and through a single call. Here's what your stored procedure might look like:

 
CREATE PROCEDURE sp_GetAllTheData AS 
SELECT * FROM tblStates ORDER BY StateCode 
SELECT * FROM tblCountries ORDER BY CountryName 
SELECT * FROM tblTitles ORDER BY TitleName

When you retrieve this, you'll get three recordsets back at the same time. Simply open the query as a static recordset. Then use the NextRecordset method to assign each recordset to a different variable, as shown here:

 
Dim rsStates, rsCountries, rsTitles
rsStates.Open "sp_GetAllTheData", dcnDB, adOpenStatic
Set rsCountries = rsStates.NextRecordset 
Set rsTitles = rsCountries.NextRecordset

Because it's more time consuming to do another COM operation than it is to just pass more data, this is a more efficient way to pass back a large batch of data.