Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Generating Multiple Recordsets

SQL Server 2000

  New Information - SQL Server 2000 SP3.

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
SELECT * FROM titles
SELECT * FROM publishers

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("Integrated Security") = "SSPI"

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

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.

© 2015 Microsoft