GET NEXT RESULT SET

Syntax:

>>---EXEC SQL---.------------.--->
                +-AT db_name-+

 >--GET NEXT RESULT SET FOR---cursor_name---END-EXEC---><

Parameter:

AT `db_name`	The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement.
`cursor_name`	A previously declared and opened cursor.

Comments:

For client applications compiled without DIALECT=MAINFRAME, GET NEXT RESULT SET makes the next result set available when retrieving multiple result sets from a stored procedure or from a DECLARE CURSOR statement defined with multiple SELECT statements.

If additional result sets are not available, GET NEXT RESULT SET returns an SQLCODE of 100 and sets SQLSTATE to 02000.

Example:

           exec sql declare c cursor for 
               call TestProc2(:hv-country) 
           end-exec
           
           exec sql open c end-exec
           
           display " "
           display "First result set from proc"
           display " "
       
           perform until exit
               exec sql fetch c into
                   :CustomerID, :Company, :City
               end-exec
               if sqlcode = 100 or sqlcode < 0
                   exit perform
               end-if
               display CustomerID " " City
           end-perform
           
            *> Always get SQLCODE 100 at the end of a result set
            *> until you close the cursor or ask for another result set
            exec sql fetch c into
                :CustomerID, :Company, :City
            end-exec
            if sqlcode not  = 100
                display "FAIL: Fetch after SQLCODE 100 OK"
            end-if
           
            *> Ask for another result set, SQLCODE 100 if there isn't one
            exec sql get next result set for c end-exec
           
            display " "
            display "Second result set from proc"
            display " "

            perform until exit
               exec sql fetch c into
                   :CustomerID, :Company, :City
               end-exec
               if sqlcode = 100 or sqlcode < 0
                   exit perform
               end-if
               display CustomerID " " City
            end-perform