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

SELECT of a Table Procedure

A table procedure can return zero or more result sets of parameters. Therefore, a SELECT ... INTO statement is only used when the SELECT returns zero or only one result set. A cursor is required if more than one row is returned to the result set.

Declaration of the Cursor

EXEC SQL
     DECLARE C_BONUS_SET CURSOR
             FOR SELECT BONUS_AMOUNT, BONUS_CURRENCY
             FROM 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 :BONUS-AMOUNT,
          :BONUS-CURRENCY
END-EXEC.

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