SQL Script for AMDP: Flow Control with IF and Loops

In this post, you will learn to use SQL Script in AMDP with flow control. This will cover below statements.

1. IF
2. LOOP

Commenting the SQL Script Code

The comments can be added using * or —

* Example for EXISTS
declare lv_result int;  --data declaration 

Using IF and ELSE

If syntax is similar to ABAP but uses additional THEN keyword.

IF <condition_1> THEN <block_1>
[ELSEIF <condition_2> THEN <block_2>]
...
[ELSE <block_N>]
END IF;

Condition can have following

• EXISTS 
• Comparison between variables
• IS [NOT] NULL
• IS_EMPTY
• IN 

The conditions can be negated with NOT, combined with keywords like AND, OR and can be nested with

Examples

AMDP Method Declaration

CLASS-METHODS if_statements
  IMPORTING VALUE(iv_carrid) TYPE scarr-carrid
            VALUE(iv_connid) TYPE spfli-connid
  RAISING   cx_amdp_error.

AMDP Method Implementation

Below code includes more examples in addition to above screenshot.

METHOD if_statements BY DATABASE PROCEDURE
                        FOR HDB LANGUAGE SQLSCRIPT
                        USING sflight.
  --Example for EXISTS
  declare lv_result int;  --data declaration
  declare lv_carrier constant nvarchar(2) := 'AA';
  
  if exists ( select carrid from sflight
              where carrid = :iv_carrid
              and   connid = :iv_connid )
      then lv_result = 1;
  elseif :iv_carrid in ( select carrid from sflight
                         where carrid = :iv_carrid
                         and   connid = :iv_connid )
      then lv_result = 2;
  end if;

  --Comparison between variables
  if :lv_carrier = :iv_carrid
      then lv_result = 1;
  end if;

  --IN
  if :iv_carrid IN ( 'AA', 'AB', 'AZ' )
      then lv_result = 1;
  end if;

ENDMETHOD.

LOOP in AMDP

FOR Loop

The syntax is as below.

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

The syntax is as below.

WHILE <condition> 
  DO <block> 
END WHILE;

Condition block is same as IF statement. BREAK statement can be used to abort the loop.

  METHOD loop_statements BY DATABASE PROCEDURE
                         FOR HDB LANGUAGE SQLSCRIPT.

    --for loop
    declare lv_sum int := 0;
    declare lv_indx int := 0;

    for lv_indx in 1..10
       do
           declare lv_tmp int := lv_sum;
           lv_tmp = lv_tmp + lv_indx;
           lv_sum = lv_tmp;
    end for;

    --while loop
    lv_indx = 0;
    while lv_indx <= 10
        do
            declare lv_tmp int := lv_sum;
            lv_tmp = lv_tmp + lv_indx;
            lv_sum = lv_tmp;
            lv_indx = lv_indx + 1;
    end while;


    --while loop with break
    lv_indx = 0;
    while lv_indx <= 1000
        do
            lv_indx = lv_indx + 1;
         if lv_indx = 10
            then break ;
         end if;
    end while;

  ENDMETHOD.

Loop table records using For Loop

For loop with addition record_count( :lt_tab) can be used to loop through the records.

--loop on table data
lt_scarr  = select * from scarr
            where mandt = SESSION_CONTEXT('CLIENT');
for lv_indx in 1..record_count( :lt_scarr )
do lv_str = :lt_scarr.carrid[ lv_indx ];
end for;

Note that the editor might show red for the [ symbol but this works.

For looping on table data, we can also use Cursor, however it is better to avoid cursor technique for performance reasons.

Visit ABAP on HANA series for Tutorials on CDS, AMDP, Eclipse, and ALV IDA.

---

If you like the content, please subscribe…