Generating Multiple Recordsets

Microsoft® SQL Server™ 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 There is an important constraint when using ADO with SQL Server. If you are using a SQL Server API server cursor, you cannot execute any Transact-SQL statement or stored procedure that generates more than one result set. If generating multiple result sets is important, leave 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 that tests whether a Recordset object is set to Nothing as the test condition for exiting the “multiple recordset” loop.

The 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 there are two result sets generated by the myNextProc stored procedure, each Recordset object can be retrieved by using the NextRecordset method. The Recordset object rs is simply reused for each recordset.

See Also