ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Native SQL →  AMDP - ABAP Managed Database Procedures →  AMDP - Examples 

AMDP, SQL Script with Tabular CHANGING Parameter

This example demonstrates how an SQLScript procedure is implemented using AMDP with a tabular CHANGING parameter.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    IF cl_db_sys=>is_in_memory_db = abap_false.
      cl_demo_output=>display(
        `Example can be executed on SAP HANA Database only` ).
      LEAVE PROGRAM.
    ENDIF.

    DATA lower TYPE scarr-carrid VALUE 'AA'.
    DATA upper TYPE scarr-carrid VALUE 'BA'.
    DATA call_flag TYPE abap_bool.
    cl_demo_input=>new(
       )->add_field( CHANGING field = lower
       )->add_field( CHANGING field = upper
       )->add_line(
       )->add_field( EXPORTING text = 'Indirect call'
                               as_checkbox = abap_true
                     CHANGING  field = call_flag
       )->request( ).

    DATA carriers TYPE cl_demo_amdp_changing=>t_carriers.
    SELECT mandt, carrid
           FROM scarr
           WHERE carrid BETWEEN @lower AND @upper
           ORDER BY mandt, carrid
           INTO CORRESPONDING FIELDS OF TABLE @carriers.
    DATA(out) = cl_demo_output=>new( )->write( carriers ).

    TRY.
        IF call_flag IS INITIAL.
          NEW cl_demo_amdp_changing(
            )->get_carriers( CHANGING carriers = carriers ).
        ELSE.
          NEW cl_demo_amdp_changing(
            )->call_get_carriers( CHANGING carriers = carriers ).
        ENDIF.
      CATCH cx_amdp_error INTO DATA(amdp_error).
        cl_demo_output=>display( amdp_error->get_text( ) ).
        RETURN.
    ENDTRY.

    out->display( carriers ).

Description

The following SQLScript procedure is implemented in the AMDP method GET_CARRIERS of the AMDP class CL_DEMO_AMDP_CHANGING:

METHOD get_carriers BY DATABASE PROCEDURE FOR HDB
                       LANGUAGE SQLSCRIPT
                    USING scarr.
  carriers  = select s.*
                   from scarr as s
                   inner join :carriers as c
                     on s.mandt  = c.mandt and
                        s.carrid = c.carrid;
ENDMETHOD.

The tabular CHANGING parameter carriers can be used in this way in the procedure in reader and writer positions. Internally, the database procedure uses an identically named output parameter to which the initial value of the CHANGING parameter is assigned (using the invisible IN parameter carrriers__in__) when the procedure is first executed (see SQL Script for the SAP HANA Database).

A further method, CALL_GET_CARRIERS, demonstrates how the SQL Script procedure implemented in GET_CARRIERS is called from a different SQL Script procedure.

METHOD call_get_carriers BY DATABASE PROCEDURE FOR HDB
                            LANGUAGE SQLSCRIPT
                         USING cl_demo_amdp_changing=>get_carriers.
  call "CL_DEMO_AMDP_CHANGING=>GET_CARRIERS"(
    CARRIERS__IN__  => :CARRIERS,
    CARRIERS        => :CARRIERS );
ENDMETHOD.

In this case, the implicit IN parameter carrriers__in__ must be filled explicit with the modified actual parameter.

Note

This is a syntax example. The same function can be provided with the same efficiency in Open SQL. AMDP is not needed in simple cases like this.