Variable Scope Nesting in Procedure

In the previous article Local Scalar Variable , we learn about local scope variable in HANA.
In this article we will show how variables scope works in HANA.

Introduction:

SQLScript supports local variable declaration in a nested block. Local variables are only visible in the scope of the block in which they are defined. It is also possible to define local variables inside LOOP / WHILE /FOR / IF-ELSE control structures.

Create procedure:

Copy and paste the below script to create the procedure.
-- REPLACE <SCHEMA_NAME> WITH YOUR SCHEMA
CREATE PROCEDURE "<SCHEMA_NAME>"."VARIABLE_SCOPE_EXAMPLE"(
       OUT val INT)
        LANGUAGE SQLSCRIPT
        READS SQL DATA
        AS
BEGIN
    DECLARE a INT := 1;
    BEGIN
        DECLARE a INT := 2;
        BEGIN
            DECLARE a INT;
            a := 3;
        END;
        val := a;
    END;
END;

Call procedure:

Call the procedure using below statement.

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

The output will be 2.

From this result you can see that the inner most nested block value of 3 has not been passed to the val variable.

Click here to go to Next Chapter. 4.5 IF-ELSE Logic