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 TitleNameWhen 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, rsTitlesrsStates.Open "sp_GetAllTheData", dcnDB, adOpenStaticSet rsCountries = rsStates.NextRecordset Set rsTitles = rsCountries.NextRecordsetBecause 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.
