24 Sep 2016 8:11 am ||

This tutorial is about how to write SQL script in SAP HANA stored procedure in order to create a complex calculation on the fly to generate a report from pulling the data from the table

Table definition

Below we can see a table with four columns as follows:

DEPTID(INT)

CITY (NVARCHAR)

QUARTER (NVARCHAR)

SALARY (DECIMAL)

The commission percentage is based on department ID's therefore,we have 3 department ids

DEPTID1 = 20% of salary

DEPTID2 = 30% of salary

DEPTID3 = 40% of salary

We need a complex calculation which we can build by using a case expression by using a stored procedure.

Therefore we move over to HANA system where we see a table DEPTTOTPAYROLL with following:

3 Department Ids

DEPTID1, DEPTID2, DEPTID3

3 CITY

SEATLE, TACOMA, REDMOND

AND QUARTER

Q1, Q2, Q3

Creating SQL Procedure

For creating SQL procedure we must first go to SQL Interface

In SQL Interface please run this code to create SQL procedure DEPT_PAYROLL_COM

Create Procedure SCHEMANAME.DEPT_PAYROLL_COMM (IN VarDepID INTEGER, IN VarQuartr NVARCHAR (2)) LANGUAGE SQLSCRIPT AS

BEGIN

SELECT "DEPTID", "CITY", "QUARTER", "SALARY"

CASE "DEPTID"

WHEN 1 THEN "SALARY" = 0.20

WHEN 2 THEN "SALARY" = 0.30

WHEN 3 THEN "SALARY" = 0.40

END AS COMISSION

FROM "SCHEMANAME" . "DEPTTOTPAYROll"

WHERE "DEPTID" = :VarDepID AND "QUARTER" = :VarQuartr

END;

Note: Variable :VarDepID and :VarQuartr which is used to pull information.

After creating the SQL Procedure we can call the procedure by executing this code

call "SCHEMANAME" . "DEPT_PAYROLL_COMM" (2. 'Q3')