Receiving procedure result sets in SQL routines

You can receive result sets from procedures you invoke from within an SQL-bodied routine.

1. DECLARE result set locators for each result set that the procedure will return. For example:

      DECLARE result1 RESULT_SET_LOCATOR VARYING;
      DECLARE result2 RESULT_SET_LOCATOR VARYING;
      DECLARE result3 RESULT_SET_LOCATOR VARYING;

2. Invoke the procedure. For example:

      CALL targetProcedure();

3. ASSOCIATE the result set locator variables (defined above) with the invoked procedure. For example:

      ASSOCIATE RESULT SET LOCATORS(result1, result2, result3)
         WITH PROCEDURE targetProcedure;

4. ALLOCATE the result set cursors passed from the invoked procedure to the result set locators. For example:

      ALLOCATE rsCur CURSOR FOR RESULT SET result1;

5. FETCH rows from the result sets. For example:

      FETCH rsCur INTO ...