SAP HANA Procedure - Old Wine in New Bottle!

In this article we will explain What is SQL Procedure? What are the different ways to create procedure in HANA? The pros and cons of procedure in HANA
What is Procedure?

A procedure is a unit/module that performs a particular task. Procedures are reusable processing blocks, and describe a sequence of data transformations Procedures can have multiple input and output parameters (scalar or table types) DROP and CREATE statements are used to modify the definition of a procedure A procedure can be created as read only (without side-effects) or read-write (with side-effects)
Few more facts on procedure:

The body of a procedure consists of a sequence of statements separated by semicolons An intermediate variable, inside a procedure, is not required to be defined before it is bound by an assignment A variable name is prefixed by ':' while used as input to another statement Cyclic dependencies that result from the intermediate result assignments or from calling other functions are not allowed A Procedure can be created using the SQL editor or using creation wizards available for the different perspectives in the SAP HANA Studio (Modeler and Development perspectives)
Different ways to create a procedure:

There are 3 ways to create a procedure in HANA. Using the SQL editor Using the Modeler wizard in Modeler perspective Using SAP HANA XS project in "SAP HANA Development" perspective We will learn about each of these approaches in details.

Note: Do not get confused by the questions like - why so many ways to create procedure? Which is one should I use? Which one is better? We will explain these later. Right now let us just learn each of these approaches.

Prerequisites:

Before creating a procedure as part of this exercise, we need to create the tables that will be used. Let us do that.
Example Scenario:

Suppose there are 3 tables PRODUCTS, REGION and SALES.

We need to find out sales value for different region. We also need to calculate NET_AMOUNT based on the DISCOUNT. DISCOUNT value will be passed as input parameter. 

We will create a procedure to achieve this. 

Create Tables:

Open HANA Studio and expand the SAP HANA system. Go to your schema. Right-click on your schema and select SQL editor. 

Note: In this example schema name is "SAP_HANA_TUTORIAL". In case you want to create a new schema use below query.

create schema <schema_name>;

Copy and paste the below script in SQL editor and execute. 

----REPLACE <YOUR SCHEMA> WITH YOUR SCHEMA NAME 

---- Create Product table

create column table "<YOUR SCHEMA>"."PRODUCT"( 

      "PRODUCT_ID" INTEGER, 

      "PRODUCT_NAME" VARCHAR (100),

      primary key ("PRODUCT_ID")

); 

insert into "<YOUR SCHEMA>"."PRODUCT" values(1,'Shirts'); 

insert into "<YOUR SCHEMA>"."PRODUCT" values(2,'Jackets'); 

insert into "<YOUR SCHEMA>"."PRODUCT" values(3,'Trousers'); 

insert into "<YOUR SCHEMA>"."PRODUCT" values(4,'Coats'); 

insert into "<YOUR SCHEMA>"."PRODUCT" values(5,'Purse'); 

-- Create Region table 

create column table "<YOUR SCHEMA>"."REGION"(

      "REGION_ID" INTEGER, 

      "REGION_NAME" VARCHAR (100),

      "SUB_REGION_NAME" VARCHAR (100),

      PRIMARY KEY ("REGION_ID") 

);

insert into "<YOUR SCHEMA>"."REGION" values(100,'Americas','North-America'); 

insert into "<YOUR SCHEMA>"."REGION" values(200,'Americas','South-America'); 

insert into "<YOUR SCHEMA>"."REGION" values(300,'Asia','India'); 

insert into "<YOUR SCHEMA>"."REGION" values(400,'Asia','Japan'); 

insert into "<YOUR SCHEMA>"."REGION" values(500,'Europe','Germany'); 

-- Create Sales table

create column table "<YOUR SCHEMA>"."SALES"( 

      "REGION_ID" INTEGER , 

      "PRODUCT_ID" INTEGER , 

      "SALES_AMOUNT" DOUBLE,       PRIMARY KEY ("REGION_ID", "PRODUCT_ID") ); 

insert into "<YOUR SCHEMA>"."SALES" values(100,1,100); 

insert into "<YOUR SCHEMA>"."SALES" values(100,2,90); 

insert into "<YOUR SCHEMA>"."SALES" values(100,5,85); 

insert into "<YOUR SCHEMA>"."SALES" values(200,2,80); 

insert into "<YOUR SCHEMA>"."SALES" values(200,1,75); 

insert into "<YOUR SCHEMA>"."SALES" values(300,3,85); 

insert into "<YOUR SCHEMA>"."SALES" values(400,4,75); 

insert into "<YOUR SCHEMA>"."SALES" values(500,1,65); 

insert into "<YOUR SCHEMA>"."SALES" values(500,2,65); 

After executing the scripts you should have 3 tables created. If there are no tables, try right-clicking on your schema and refreshing. 

Grant schema SELECT rights to _SYS_REPO user:

Open the SQL editor of your schema and execute the following command line:

GRANT SELECT ON SCHEMA <YOUR SCHEMA> TO _SYS_REPO WITH GRANT OPTION;

If you miss this step, an error will occur when you activate your views later. 

Click here to know more about this. 

Create Table Type:

We need to create a table type, which will be used for output parameter of the procedure. 

Execute the below SQL statement. 

--REPLACE SCHEMA_NAME WITH YOUR SCHEMA

CREATE TYPE SCHEMA_NAME.TT_SALES AS TABLE (

            SALES_AMOUNT DECIMAL,

            NET_AMOUNT DECIMAL,

            PRODUCT_NAME NVARCHAR(20),

            REGION_NAME NVARCHAR(20),

            SUB_REGION_NAME NVARCHAR(20)

);

To know more about Table Type, check the article SAP HANA Table Type.

Create Procedure Using the SQL editor:

Syntax:

CREATE PROCEDURE {schema.}name 

            {({IN|OUT|INOUT} 

                        param_name data_type {,...})} 

            {LANGUAGE <LANG>} {SQL SECURITY <MODE>} 

            {READS SQL DATA {WITH RESULT VIEW <view_name>}} AS 

BEGIN 

... 

END 

READS SQL DATA defines a procedure as read-only. Implementation LANGUAGE can be specified. Default is SQLScript. WITH RESULT VIEW is used to create a column view for the output parameter of type table

Steps to Create The Procedure:

Open SQL Editor and execute the below script 

CREATE PROCEDURE SCHEMA_NAME."PROCEDURE_SALES_REPORT"(

            IN DISCOUNT INTEGER,

            OUT OUTPUT_TABLE SCHEMA_NAME."TT_SALES" )

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

/*********BEGIN PROCEDURE SCRIPT ************/

BEGIN

var1 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T2.PRODUCT_ID, T2.SALES_AMOUNT

            FROM SCHEMA_NAME.REGION AS T1

            INNER JOIN

            SCHEMA_NAME.SALES AS T2

            ON T1.REGION_ID = T2.REGION_ID;

var2 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T1.PRODUCT_ID, T1.SALES_AMOUNT, T2.PRODUCT_NAME

            FROM :var1 AS T1

            INNER JOIN

            SCHEMA_NAME.PRODUCT AS T2

            ON T1.PRODUCT_ID = T2.PRODUCT_ID;

OUTPUT_TABLE = SELECT SUM(SALES_AMOUNT) AS SALES_AMOUNT, SUM( SALES_AMOUNT - (SALES_AMOUNT * :DISCOUNT/ 100)) AS NET_AMOUNT,

            PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME

            FROM :var2 

            GROUP BY PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME;

END;

Refresh the procedure folder under schema in the left Systems tab. You will see the created procedure there. 

How to call Procedure in HANA:

We call procedure using CALL statement. Execute the below statement to call this procedure. 

CALL SCHEMA_NAME."PROCEDURE_SALES_REPORT" (10, null);

  

For table output parameters it is possible to either pass a (temporary) table name or to pass NULL. The option NULL will display the output directly on the client output screen. 

Continue Reading:

Create Procedure in HANA - Do Not Use SQL Editor Any More 

Click here to go to Next Chapter. 3.2 Create Procedure Without Using SQL Console