SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SQLScript
+
-
Tables in SAP HANA
+
-
Procedure
+
-
Examples of Procedure
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')

SAP HANA Procedure Example

Continue reading:
Check the next example on For Loop in Procedure




Support us by sharing this article.



Explore More
Close X
Close X

One thought on “SAP HANA Procedure Example – How to use IF ELSE logic

  1. Kevin says:

    Hello There,

    This is one awesome blog. Much thanks again. Fantastic.

    We are busying testing an upgrade to SAP9.3 PL02 and it failed on the 2 schemas we are testing on. This in a test environment.
    I want to drop the schemas and try on a more recent backup as I did not get an error code reason or note for the failed upgrade. Storing data in main memory rather than on disk provides SAP HANA Training faster data accessing and by extension faster querying and processing.

    However, when I try to drop those schema’s I get an error:
    [129]: transaction rolled back by an internal error: Allocation failed; $size$=648; $name$=libhdbrskernal.so; $type$=pool; $inuse_count$=632593; $allocated_size$=530359301
    I get the same error by trying to right click on the schema and delete(CASCADE) or if i use the drop schema cascade;

    Does anyone know how to resolve this issue?

    Anyways great write up, your efforts are much appreciated.

    Kind Regards,
    Kevin

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy