Online Tutorials & Training Materials | STechies.com
Register Login
Advertisement

How to write SQL Stored Procedure Script in SAP HANA

|| 0

How to write SQL Stored Procedure Script in SAP HANA
Stechies

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')

 


Advertisement
Advertisement
Advertisement