Example

This example defines a selfunc procedure using database specific SQL-Statements (Informix). It also calls the procedure using the SAP-specific Native-SQL-Statement EXECUTE PROCEDURE in a LOOP-loop by means of a Selection Table, and deletes the the procedure using an SQL-Statement. In the case shown here, the procedure is a function whose return value output in EXECUTE PROCEDURE is copied to the host variable name.

DATA scarr_carrid TYPE scarr-carrid.
SELECT-OPTIONS s_carrid FOR scarr_carrid NO INTERVALS.
DATA s_carrid_wa LIKE LINE OF s_carrid.

DATA name TYPE c LENGTH 20.

TRY.
    EXEC SQL.
      CREATE FUNCTION selfunc( input CHAR(3) )
        RETURNING char(20);
        DEFINE output char(20);
        SELECT carrname
               INTO output
               FROM scarr
               WHERE mandt  = '000' AND
                     carrid = input;
        RETURN output;
        END FUNCTION;
    ENDEXEC.
    LOOP AT s_carrid INTO s_carrid_wa
                     WHERE sign = 'I' AND ption = 'EQ'.
      TRY.
         EXEC SQL.
            EXECUTE PROCEDURE selfunc( IN  :s_carrid_wa-low,
                                       OUT :name )
          ENDEXEC.
          WRITE: / s_carrid_wa-low, name.
        CATCH cx_sy_native_sql_error.
          MESSAGE `Error in procedure execution` TYPE 'I'.
      ENDTRY.
    ENDLOOP.
    EXEC SQL.
      DROP FUNCTION selfunc;
    ENDEXEC.
  CATCH cx_sy_native_sql_error.
    MESSAGE `Error in procedure handling` TYPE 'I'.
ENDTRY. 

 

dbco1.JPG

dbco2.JPG