Fetching Multiple Result Sets using the SQL_NEXT_RESULT_SET Command

You can enter multiple SELECT statements in a stored procedure to retrieve multiple resultsets and then use SQL_NEXT_RESULT_SET in the client code to position the reader to these resultsets.

SQL_NEXT_RESULT_SET iterates through the resultsets in order when multiple resultsets are returned. SQL_NEXT_RESULT_SET returns 0 (FALSE) if there are no more resultsets and a nonzero value (TRUE) otherwise.

Stored Procedure

For example, you might create a stored procedure with three SELECT statements:

CREATE PROCEDURE MULTI_RS
 AS
 BEGIN
 SET NOCOUNT ON;
 SELECT COUNT(*) from orderhea;
 SELECT CUSTOMER_NUMBER,NAME,STATE FROM customer WHERE STATE = 'FL' ORDER BY NAME;
 SELECT * FROM salesp order by ID;
 END
 

Client Code

Then use the following client code to process the data.

Procedure TestRSFromSP
 Local Integer iRS iColumn iNumCols
 Local String sColumnValue sColumnName
 Move 1 to iRS
 CALL_SQLSERVER_PROCEDURE "[dbo].[MULTI_RS]"
 Repeat
 sql_fetch_next_row
 If (Found) Begin
 showln 'RS#' iRS
 Sql_Get_Num_Cols TO iNumCols
 Repeat
 For iColumn From 1 to iNumCols
 Sql_Fetch_Column iColumn INTO sColumnValue
 show sColumnValue ' - '
 Loop
 Showln
 sql_fetch_next_row
 Until (Not(Found))
 Indicate Found True
 End
 Showln
 Showln
 Sql_Next_Result_Set
 If (Found) Begin
 Move (iRS + 1) to iRS
 End
 Until (Not(Found))
 Showln
 Showln “Total RS:” iRS
 End_Procedure

Support for SQL_NEXT_RESULT_SET was added to the Mertech v11 MSSQL driver. Previously this feature was only supported by the MySQL driver. Mertech.inc version 3.0.8 describes this new feature.

Syntax

SQL_NEXT_RESULT_SET Of <FileNum|FileName>

Important Note

Always use a stored procedure if you plan to return multiple resultsets. Results are unpredictable if multiple select statements are separated by semicolons in inline SQL code.