Generating Multiple Recordsets
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 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("Integrated Security") = "SSPI" 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.