Retrieving Multiple Result Sets

Any SQL statement can include multiple SELECT statements or stored procedures that invoke one or more SELECT statements. Each SELECT statement generates a result set that must be processed by your code or discarded before the resources are released and the next result set is made available. Because you don’t necessarily know how many result sets will be generated by a stored procedure, your code must be prepared to process an unknown number of result sets. Note that when a stored procedure returns multiple result sets, none of the result sets can be updated.

You can use either client-side cursors or server-side cursors to retrieve multiple result sets. If you use client-side cursors, multiple result sets are returned no matter what type of Recordset object you open. If you use server-side cursors to retrieve multiple result sets, you must open a forward-only-type Recordset object.

Û To retrieve multiple result sets

1. Set the workspace’s DefaultCursorDriver property to dbUseServerCursor to specify server-side cursors.
   
   
2. Create a QueryDef object and set its SQL property to a valid SQL string that returns multiple Recordset objects.
   
   
3. Set the CacheSize property of the QueryDef object to 1 to request that the server sends you one record at a time. When you retrieve records in this way, you don’t actually use the cursor.
   
   
4. Open a Recordset object on the QueryDef object you just created. Specify dbOpenForwardOnly for the type argument of the OpenRecordset method.
   
   
5. Use the NextRecordset method to access the next Recordset object in the group of Recordset objects returned by the server. This discards the current Recordset object and replaces it with the next Recordset object specified in your query’s SQL statement. If there are no more Recordset objects in the group of Recordset objects, then the return value of the NextRecordset method will be False and the current Recordset object will be empty.

The following example prints the values of each field for each record in each result set.

Function GetMultipleResults()
	Dim wrk As Workspace, rst As Recordset, cnn As Connection, qdf As QueryDef
	Dim fld As Field, strSQL As String, strConnect As String, fDone As Boolean

	' Create ODBCDirect workspace.
	Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
	' Create connection string.
	strConnect = "ODBC;DSN=Pubs;DATABASE=Pubs;UID=sa;PWD=;"
	' Open connection.
	Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
	' Create SQL statement.
	strSQL = "SELECT au_lname, au_fname FROM Authors; SELECT title FROM Titles;"
	' Set default cursor driver.
	wrk.DefaultCursorDriver = dbUseServerCursor

	' Create temporary QueryDef object.
	Set qdf = cnn.CreateQueryDef("", strSQL)
	qdf.CacheSize = 1
	' Open recordset on QueryDef.
	Set rst = qdf.OpenRecordset(dbOpenForwardOnly)

	Do Until fDone = True
		' Print values for each field in each record of recordset.
		While Not rst.EOF
			For Each fld In rst.Fields
				Debug.Print fld.Value
			Next fld
			rst.MoveNext
		Wend
		fDone = Not rst.NextRecordset()
	Loop
	rst.Close
	cnn.Close
	wrk.Close
End Function