Question:
What are my options when I need to send multiple recordsets back to ASP from a single call? Which is the best for performance?
Answer:
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_GetLotsOfDataAsSelect * From tblCustomersSelect * From tblOrdersSelect * From tblShippers
To get at this data, you can use this code:
Dim rsCust, rsOrders, rsShippers ' As ADODB.RecordsetDim dcnDB ' As ADODB.ConnectionSet 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.NextRecordsetSet rsShippers = rsOrders.NextRecordset' Now you can do whatever you want with the three recordsets.
