IF-ELSE Logic in Procedure

In this article we will show an example on - How to use imperative logic and control flow (If-Else logic) inside procedure.
Introduction:

Syntax:
IF <bool_expr1>
THEN
    <then_stmts1>
ELSEIF <bool_expr2>
THEN
    <then_stmts2>
ELSE
    <else_stmts3>
END IF

The IF statement consists of a Boolean expression <bool-expr1>. If this expression evaluates to true then the statements <then-stmts1> in the mandatory THEN block are executed. The IF statement ends with END IF. The remaining parts are optional.

In this example we will pass product id, product name and category as input. If the product already exist, we will update the record. If it does not exist, we will create a new record.

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 skip 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>."IF_ELSE_EXAMPLE"(
        IN productid VARCHAR(10),
        IN productname VARCHAR(20),
        IN category VARCHAR(20))
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER
    AS
/*********BEGIN PROCEDURE SCRIPT ************/
BEGIN
    DECLARE found INT := 1;
    SELECT count(*) INTO found FROM "<SCHEMA_NAME>"."PRODUCT"
        WHERE PRODUCT_ID = :productid;
 
    IF :found = 0
    THEN
        INSERT INTO "<SCHEMA_NAME>"."PRODUCT"
            VALUES (:productid, :productname, :category);
    ELSE
        UPDATE "<SCHEMA_NAME>"."PRODUCT"
            SET PRODUCT_NAME = :productname, CATEGORY = :category
            WHERE PRODUCT_ID =:productid;
    END IF;
    SELECT PRODUCT_ID, PRODUCT_NAME, CATEGORY FROM "<SCHEMA_NAME>"."PRODUCT" ;
END;

Call procedure:

Call the procedure using below statement and see output.

CALL <SCHEMA_NAME>."IF_ELSE_EXAMPLE"('P6', 'Watch', 'Accessories')

Click here to go to Next Chapter. 4.6 For Loop