opbang

 Profile Answers by opbang 

• Sep 25th, 2006

 

Multiple Recordsets
To create a multiple Recordset, go through the same steps as creating a typical Recordset, except, when specifying your SQL SELECT statement, put all of the SQL SELECT statements that you need in that one Recordset, separating each SQL SELECT statement with a semicolon. (You can also generate a multiple Recordset by populating a Recordset with the results of a stored procedure that returns multiple SQL queries.)

The first step to using multiple Recordsets is to making our SQL SELECT statements all appear as one, semicolon delimited string:

 

sSQL = "SELECT categoryId, categoryName FROM Categories WHERE categoryId > 3"sSQL = sSQL & ";SELECT RegionId , RegionDescription FROM Region"sSQL = sSQL & ";SELECT ShipperID, CompanyName, Phone FROM Shippers"

Now create our multiple Recordset:

sConnectString = "DRIVER={sql server};SERVER=localhost;" & _ "DATABASE=northwind;UID=sa;PWD=" set oRS = Server.CreateObject("ADODB.Recordset")oRs.Open sSQL, sConnectString

If you like to use SQL stored procedures, you can utilize multiple Recordsets by creating a stored procedure that returns multiple SQL queries, like so:

Create Procedure MyResultsASSELECT categoryId, categoryName FROM Categories WHERE categoryId > 3SELECT RegionId, RegionDescription FROM RegionSELECT ShipperID, CompanyName, Phone FROM Shippers

And then create the multiple Recordset as follows:

sConnectString = "DRIVER={sql server};SERVER=localhost;" & _ "DATABASE=northwind;UID=sa;PWD=" set oRS = Server.CreateObject("ADODB.Recordset")oRs.Open "MyResults", sConnectString

At this point, the oRs variable is an ADO multiple Recordset

reshmi

• Nov 8th, 2006

 

Yes.. stored procedure can return multiple recordsets.

Ranjit

 Profile Answers by Ranjit 

• Dec 6th, 2006

 

If you have a stored procedure that looks like this: 
 

CREATE PROCEDURE myProc  AS  BEGIN      SET NOCOUNT ON        SELECT columns FROM table1        SELECT columns FROM table2        SELECT columns FROM table3  END

 
Here is code that demonstrates how to retrieve the second and third set of results, using the NextRecordSet() method: 
 

<%      ' ...      ' assuming valid and open object, conn        set rs = conn.execute("EXEC myProc")        ' process first resultset        if not rs.eof then          do while not rs.eof              response.write rs(0)              rs.movenext          loop      end if        ' move to second resultset, using nextRecordSet()        set rs = rs.nextRecordSet()      if not rs.eof then          do while not rs.eof              response.write rs(0)              rs.movenext          loop      end if        ' move to third resultset, using nextRecordSet()        set rs = rs.nextRecordSet()      if not rs.eof then          do while not rs.eof              response.write rs(0)              rs.movenext          loop      end if      rs.close: set rs = nothing      ' ...  %>