Introduction: AMDP or ABAP Managed Database Procedure, is a SAP technology that allows database specific procedure (like those written in SQL Script for SAP HANA) to be managed and executed directly from ABAP code. The primary aim of AMDP is to optimize performance by pushing complex data operations to the database layer.
Important Points:
While adding interface IF_AMDP_MARKER_HDB. Remember, interfaces can only be added in PUBLIC SECTION.Only Variables and Tables are allowed as parameters. We cannot use structures or nested tables.Generic types cannot be used for parameters. For example, Type Any cannot be used. Only elementary data types and table types with a structured row type can be used.The table type components must be elementary data types and it cannot have elements which are table types.Only pass by value can be used. Pass by reference is not permitted. Using VALUE keyword for all parameters is required.RETURNING parameters are not allowed. We can use EXPORTING or CHANGING to receive the values.Only input parameters can be flagged as optional with a DEFAULT value (literals/constants)Every AMDP method will have below addition. READ-ONLY is only the optional addition and is used for methods that only read the data.
o BY DATABASE PROCEDURE
o FOR HDB
o LANGUAGE SQLSCRIPT
o OPTIONS READ-ONLY
o USING table/view namesIt is also mandatory to specify all the database objects and other AMDP methods that are used within the SQLSCRIPT code.No ABAP statements can be written in the method code.AMDP methods do not have any implicit enhancement options.
Internal Table and Flow Control in AMDP
In AMDP (ABAP Managed Database Procedures), you can declare internal tables using SQL Script. These internal tables are used to store intermediate results and can be manipulated within your AMDP methods.
CLASS zrj_amdp_01 DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
TYPES: BEGIN OF ty_spfli,
mandt TYPE mandt,
carrid TYPE s_carr_id,
connid TYPE s_conn_id,
countryfr TYPE land1,
countryto TYPE land1,
END OF ty_spfli.
TYPES: tt_spfli TYPE TABLE of ty_spfli.
INTERFACES: if_amdp_marker_hdb.
METHODS: get_spfli_details IMPORTING VALUE(iv_mandt) TYPE mandt
EXPORTING VALUE(et_spfli) TYPE tt_spfli.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zrj_amdp_01 IMPLEMENTATION.
METHOD get_spfli_details BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
DECLARE lt_spfli TABLE ( mandt “$ABAP.type( sy-mandt )”,
carrid “$ABAP.type( S_CARR_ID )”,
connid “$ABAP.type( S_CONN_ID )”,
countryfr “$ABAP.type( LAND1 )”,
countryto “$ABAP.type( LAND1 )” );
lt_spfli.mandt[1] := ‘100’;
lt_spfli.carrid[1] := ‘AA’;
lt_spfli.connid[1] := ‘1000’;
lt_spfli.countryfr[1] := ‘DE’;
lt_spfli.countryto[1] := ‘US’;
et_spfli = select * from :lt_spfli;
ENDMETHOD.
ENDCLASS.
Some Useful Internal Table with Operations:
RECORD_COUNT( ) Operator is used to get to know the no of lines in the internal table. So
it is likes LINES( ) / DESCRIBE table in ABAP.
SEARCH() can be applied on an internal table and it receives two arguments, the column
name and the value, if it finds then it returns the index of that record within the internal
table.
EXISTS() tells whether the record exists or not.
IS_EMPTY( ) can be used and we can pass the Internal table name as an argument to it and
then it checks the emptiness of the table , it is similar to IS INITIAL check in ABAP.
APPLY_FILTER( ) function can be used on internal table or DB tables to get all the items that
match the filter condition.
EXCEPT use is quite simple. It’s like a minus operation. The result of EXCEPT operation is the
record set that present in first and not present in the second.
Flow Control with IF and Loops
Using IF and ELSE
If syntax is similar to ABAP but uses additional THEN keyword.
Condition can have following
EXISTSComparison between variablesIS [NOT] NULLIS_EMPTYIN
The conditions can be negated with NOT, combined with keywords like AND, OR and can be
nested.
CLASS zrj_amdp_02 IMPLEMENTATION.
METHOD if_statement BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING sflight.
DECLARE lv_result INT;
DECLARE lv_carrier CONSTANT NVARCHAR(2) := ‘AA’;
DECLARE lv_carrier1 CONSTANT NVARCHAR(2) := ‘BB’;
***Example for EXISTS
if EXISTS ( select carrid FROM sflight WHERE carrid = ‘1’ )
THEN lv_result = 1;
else
lv_result = 2;
END if;
***comparision between variables
if :lv_carrier = :lv_carrier1
THEN lv_result = 1;
end if;
*** IN
if :lv_carrier1 IN( ‘AA’, ‘AB’, ‘AZ’ )
THEN lv_result = 1;
end if;
ENDMETHOD.
ENDCLASS.
For Loop
FOR <variable> IN [REVERSE] <initial_value>..<final_value>
DO <block>
END FOR;
The variable is assigned an initial value for the first iteration, incremented or, if you
specified REVERSE, decremented with every iteration till final value is reached. Variables
declared inside the FOR loop are not visible on the outside.
While Loop
WHILE <condition>
DO <block>
END WHILE;
Condition block is same as IF statement. BREAK statement can be used to abort the loop.
METHOD loop_statement BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.
DECLARE lv_sum INT := 0;
DECLARE lv_index INT := 0;
*** for loop
for lv_index IN 1..10
DO
DECLARE lv_tmp int := lv_sum;
lv_tmp = lv_tmp + lv_index;
lv_sum = lv_tmp;
END for;
*** while loop
lv_index = 0;
WHILE lv_index <= 10
do
DECLARE lv_tmp INT := lv_sum;
lv_tmp = lv_tmp + lv_index;
lv_sum = lv_tmp;
lv_index = lv_index + 1;
END WHILE;
While Loop With Break :
*** while loop with break
lv_index = 0;
WHILE lv_index <= 1000
DO
lv_index = lv_index + 1;
if lv_index = 10
THEN BREAK ;
END IF;
END WHILE;
ENDMETHOD.
ENDCLASS.
Conclusion: Internal tables in AMDP mirror ABAP’s data handling but are managed using SQL Script constructs. Flow control allows procedural operations inside the database to optimize logic execution. Together, these enable high-performance, code-pushed data processing-minimizing data transfer between ABAP and database layer and making AMDP ideal for complex analytical logic.
Introduction: AMDP or ABAP Managed Database Procedure, is a SAP technology that allows database specific procedure (like those written in SQL Script for SAP HANA) to be managed and executed directly from ABAP code. The primary aim of AMDP is to optimize performance by pushing complex data operations to the database layer.Important Points:While adding interface IF_AMDP_MARKER_HDB. Remember, interfaces can only be added in PUBLIC SECTION.Only Variables and Tables are allowed as parameters. We cannot use structures or nested tables.Generic types cannot be used for parameters. For example, Type Any cannot be used. Only elementary data types and table types with a structured row type can be used.The table type components must be elementary data types and it cannot have elements which are table types.Only pass by value can be used. Pass by reference is not permitted. Using VALUE keyword for all parameters is required.RETURNING parameters are not allowed. We can use EXPORTING or CHANGING to receive the values.Only input parameters can be flagged as optional with a DEFAULT value (literals/constants)Every AMDP method will have below addition. READ-ONLY is only the optional addition and is used for methods that only read the data.o BY DATABASE PROCEDUREo FOR HDBo LANGUAGE SQLSCRIPTo OPTIONS READ-ONLYo USING table/view namesIt is also mandatory to specify all the database objects and other AMDP methods that are used within the SQLSCRIPT code.No ABAP statements can be written in the method code.AMDP methods do not have any implicit enhancement options.Internal Table and Flow Control in AMDPIn AMDP (ABAP Managed Database Procedures), you can declare internal tables using SQL Script. These internal tables are used to store intermediate results and can be manipulated within your AMDP methods.CLASS zrj_amdp_01 DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
TYPES: BEGIN OF ty_spfli,
mandt TYPE mandt,
carrid TYPE s_carr_id,
connid TYPE s_conn_id,
countryfr TYPE land1,
countryto TYPE land1,
END OF ty_spfli.
TYPES: tt_spfli TYPE TABLE of ty_spfli.
INTERFACES: if_amdp_marker_hdb.
METHODS: get_spfli_details IMPORTING VALUE(iv_mandt) TYPE mandt
EXPORTING VALUE(et_spfli) TYPE tt_spfli.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zrj_amdp_01 IMPLEMENTATION.
METHOD get_spfli_details BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
DECLARE lt_spfli TABLE ( mandt “$ABAP.type( sy-mandt )”,
carrid “$ABAP.type( S_CARR_ID )”,
connid “$ABAP.type( S_CONN_ID )”,
countryfr “$ABAP.type( LAND1 )”,
countryto “$ABAP.type( LAND1 )” );
lt_spfli.mandt[1] := ‘100’;
lt_spfli.carrid[1] := ‘AA’;
lt_spfli.connid[1] := ‘1000’;
lt_spfli.countryfr[1] := ‘DE’;
lt_spfli.countryto[1] := ‘US’;
et_spfli = select * from :lt_spfli;
ENDMETHOD.
ENDCLASS.Some Useful Internal Table with Operations:RECORD_COUNT( ) Operator is used to get to know the no of lines in the internal table. Soit is likes LINES( ) / DESCRIBE table in ABAP.SEARCH() can be applied on an internal table and it receives two arguments, the columnname and the value, if it finds then it returns the index of that record within the internaltable.EXISTS() tells whether the record exists or not.IS_EMPTY( ) can be used and we can pass the Internal table name as an argument to it andthen it checks the emptiness of the table , it is similar to IS INITIAL check in ABAP.APPLY_FILTER( ) function can be used on internal table or DB tables to get all the items thatmatch the filter condition.EXCEPT use is quite simple. It’s like a minus operation. The result of EXCEPT operation is therecord set that present in first and not present in the second.Flow Control with IF and LoopsUsing IF and ELSEIf syntax is similar to ABAP but uses additional THEN keyword.Condition can have followingEXISTSComparison between variablesIS [NOT] NULLIS_EMPTYINThe conditions can be negated with NOT, combined with keywords like AND, OR and can benested.CLASS zrj_amdp_02 IMPLEMENTATION.
METHOD if_statement BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING sflight.
DECLARE lv_result INT;
DECLARE lv_carrier CONSTANT NVARCHAR(2) := ‘AA’;
DECLARE lv_carrier1 CONSTANT NVARCHAR(2) := ‘BB’;
***Example for EXISTS
if EXISTS ( select carrid FROM sflight WHERE carrid = ‘1’ )
THEN lv_result = 1;
else
lv_result = 2;
END if;
***comparision between variables
if :lv_carrier = :lv_carrier1
THEN lv_result = 1;
end if;
*** IN
if :lv_carrier1 IN( ‘AA’, ‘AB’, ‘AZ’ )
THEN lv_result = 1;
end if;
ENDMETHOD.
ENDCLASS.For LoopFOR <variable> IN [REVERSE] <initial_value>..<final_value>
DO <block>
END FOR; The variable is assigned an initial value for the first iteration, incremented or, if youspecified REVERSE, decremented with every iteration till final value is reached. Variablesdeclared inside the FOR loop are not visible on the outside.While LoopWHILE <condition>
DO <block>
END WHILE; Condition block is same as IF statement. BREAK statement can be used to abort the loop.METHOD loop_statement BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.
DECLARE lv_sum INT := 0;
DECLARE lv_index INT := 0;
*** for loop
for lv_index IN 1..10
DO
DECLARE lv_tmp int := lv_sum;
lv_tmp = lv_tmp + lv_index;
lv_sum = lv_tmp;
END for;
*** while loop
lv_index = 0;
WHILE lv_index <= 10
do
DECLARE lv_tmp INT := lv_sum;
lv_tmp = lv_tmp + lv_index;
lv_sum = lv_tmp;
lv_index = lv_index + 1;
END WHILE;While Loop With Break :*** while loop with break
lv_index = 0;
WHILE lv_index <= 1000
DO
lv_index = lv_index + 1;
if lv_index = 10
THEN BREAK ;
END IF;
END WHILE;
ENDMETHOD.
ENDCLASS.Conclusion: Internal tables in AMDP mirror ABAP’s data handling but are managed using SQL Script constructs. Flow control allows procedural operations inside the database to optimize logic execution. Together, these enable high-performance, code-pushed data processing-minimizing data transfer between ABAP and database layer and making AMDP ideal for complex analytical logic. Read More Application Development and Automation Blog Posts articles
#SAP
