How to Use Local Scalar Variable in Procedure

In this article we will show an example of SAP HANA procedure to show – How to use local scalar variables.

Introduction:

Local variables are declared using DECLARE keyword and they can optionally be initialized with their declaration. By default scalar variables are initialized with NULL.
A scalar variable var can be referenced the same way as described above using :var.

Assignment (in contrast to binding table variables) is possible multiple times overwriting the previous value stored in the scalar variable. Assignment is performed using the operator “:=”

In this example we are going to use 2 tables PRODUCTS and SALES. The procedure will join these 2 tables to get PRODUCT_NAME and SALES_AMOUNT. The output will be sent in output using a table type.

Create tables in SAP HANA:

Copy and paste the below script in SQL editor and execute.
Note: If you already have created the PRODUCT table in previous example, then ignore this step.

----REPLACE <SCHEMA_NAME> WITH YOUR SCHEMA NAME
---- Create Product table
create column table "<SCHEMA_NAME>"."PRODUCT"(
      "PRODUCT_ID" VARCHAR(10),
      "PRODUCT_NAME" VARCHAR (100),
     "CATEGORY" VARCHAR(100),
       primary key ("PRODUCT_ID")
);

insert into "<SCHEMA_NAME>"."PRODUCT" values('P1','Shirts', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P2','Jackets', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P3','Trousers', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P4','Coats', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P5','Purse', 'Accessories');


Create procedure:

Copy and paste the below script to create the procedure.

--REPLACE <SCHEMA_NAME> WITH YOUR SCHEMA NAME
CREATE PROCEDURE "<SCHEMA_NAME>"."SCALAR_VARIABLE_EXAMPLE" ()
                LANGUAGE SQLSCRIPT
                SQL SECURITY INVOKER
                AS
BEGIN
/*****************************
                Write your procedure logic
 *****************************/
                DECLARE productid varchar(20) := 'P6';
                DECLARE productname varchar(20);
                DECLARE category varchar(20);

                productname := 'Watch';
                category := 'Accessories';
             
                INSERT INTO "<SCHEMA_NAME>"."PRODUCT"
                VALUES (:productid, :productname, :category);
             
                SELECT PRODUCT_ID, PRODUCT_NAME, CATEGORY FROM "<
                SCHEMA_NAME>"."PRODUCT";

END;

Call procedure:

Call the procedure using below statement and see the output.

CALL <SCHEMA_NAME>."SCALAR_VARIABLE_EXAMPLE"();

Click here to go to Next Chapter. 4.4 Variable Scope Nesting