For Loop in Procedure

In this article we will show an example of SAP HANA procedure to show – How to use For Loop.

Introduction:

The For loop iterates a range of numeric values.
BREAK:
Specifies that a loop should stop being processed.
CONTINUE:
Specifies that a loop should stop processing the current iteration, and should immediately start processing the next.

In this example we will define a loop sequence. If the loop value :x is less than 3 the iterations will be skipped. If :x is 5 then the loop will terminate.

Create procedure:

Copy and paste the below script to create the procedure.

----REPLACE <SCHEMA_NAME> WITH YOUR SCHEMA NAME
CREATE PROCEDURE <SCHEMA_NAME>."FOR_LOOP_EXAMPLE"(
        OUT output_var INT)
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER
    AS
/*********BEGIN PROCEDURE SCRIPT ************/
BEGIN
    DECLARE count INT := 0;
    DECLARE v_index INT;
 
    FOR v_index IN 0 .. 10 DO
        IF :v_index < 3 THEN
            CONTINUE;
        ELSEIF :v_index = 5 THEN
            BREAK;
        ELSE
            count := count + 1;      
        END IF;
    END FOR;
    output_var := count;  
END;

Call procedure:

Call the procedure using below statement.

CALL <SCHEMA_NAME>."FOR_LOOP_EXAMPLE"(?);

The output will be 2.

Click here to go to Next Chapter. 4.7 Array