Data Manipulation with SQL › Invoking Procedures › CALL Statement › CALL of a Table Procedure

CALL of a Table Procedure

A table procedure can return zero or more result sets of parameters. Therefore, a simple CALL statement can not be used to invoke and return all the result sets of the table procedure; a cursor is required.

Declaration of the Cursor

EXEC SQL
     DECLARE C_BONUS_SET CURSOR
             FOR CALL DEMOEMPL.GET_BONUS_SET
             ( EMP_ID =1234 )
END-EXEC.

Opening the Cursor

EXEC SQL
     OPEN C_BONUS_SET
END-EXEC.

Fetching the Result Sets

EXEC SQL
     FETCH C_BONUS_SET INTO
           :EMP-ID,
           :BONUS-AMOUNT,
           :BONUS-CURRENCY
END-EXEC.

Host variables for all parameters specified in the table procedure definition should be provided.

Note: For more information about using cursors, see Using a Cursor.