Generating Multiple Recordsets

Microsoft® SQL Server™ 2000 allows a batch of queries to be issued and executed. When a batch of queries is executed, more than one recordset can be generated. Multiple recordsets can also be generated by SQL statements that include multiple SELECT statements or COMPUTE BY and COMPUTE clauses, or by stored procedures that contain more than one SELECT statement.

Note  If you are using a SQL Server API server cursor, you cannot execute a Transact-SQL statement or stored procedure that generates more than one result set. If you need to generate multiple result sets, use a client cursor by leaving the cursor properties of the Recordset object set to their defaults (for example, forward only/read-only (adOpenForwardOnly) and an editing lock of adLockReadOnly).

When multiple recordsets are generated, you need to fetch one recordset at a time until no more recordsets are available. The NextRecordset method of the Recordset object allows you to fetch subsequent recordsets. If no more recordsets are available, the returned Recordset object is set to Nothing. Generally, you write code to test whether a Recordset object is set to Nothing as the test condition for exiting the multiple recordset loop.

The following example shows how to fetch multiple recordsets from a stored procedure using the NextRecordset method of the Recordset object.

The stored procedure syntax is:

DROP PROC myNextproc
GO
CREATE PROC myNextproc AS
SELECT * FROM titles
SELECT * FROM publishers
GO

The stored procedure generates two result sets: one for the result of SELECT * FROM titles and the other for the result of SELECT * FROM publishers.

The ADO code syntax is:

Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset    

cn.Provider = "sqloledb"
cn.Properties("Data Source") = "MyServerName"
cn.Properties("Initial Catalog") = "pubs"
cn.Properties("user ID") = "sa"
cn.Properties("password") = ""
cn.Open

Cmd.CommandText = "myNextProc"
Cmd.CommandType = adCmdStoredProc

Set rs = Cmd.Execute
While Not rs Is Nothing
    If (Not rs.EOF) Then
        Debug.Print rs(0)
    End If
    Set rs = rs.NextRecordset
Wend

After the myNextProc stored procedure is executed, a Recordset object is created. Because two result sets are generated by the myNextProc stored procedure, each Recordset object can be retrieved by using the NextRecordset method. The Recordset object, rs, is reused for each recordset.