What are my options when I need to send multiple recordsets back to ASP from a single call? Which is the best for performance?
You can return multiple recordsets from a single query (such as in SQL Server). The trick to accessing them is the NextRecordset method. Here's a quick example of how to do this. Assume that the stored procedure is as follows:
Create Procedure sp_GetLotsOfData
Select * From tblCustomers
Select * From tblOrders
Select * From tblShippers
To get at this data, you can use this code:
Dim rsCust, rsOrders, rsShippers ' As ADODB.Recordset
Dim dcnDB ' As ADODB.Connection
Set dcnDB = Server.CreateObject("ADODB.Connection")
dcnDB.Open "some connection string"
Set rsCust = Server.CreateObject("ADODB.Recordset")
rsCust.Open "sp_GetLotsOfData", dcnDB, adOpenStatic
' At this point, rsCust will point to the first recordset.
Set rsOrders = rsCust.NextRecordset
Set rsShippers = rsOrders.NextRecordset
' Now you can do whatever you want with the three recordsets.