Create Stored Procedure on SAP HANA Database
In this SAP HANA database tutorial, I want to show steps to create HANA stored procedure for SQLScript developers using .hdbprocedure design-time artifact. .hdbprocedure is DDL based, a new way of creating stored procedures on SAP HANA database.
I will use SAP HANA DEMO content and my development and test database schema on hanatrial.ondemand.com
As seen in below screenshot, I have demo data in SAP_HANA_DEMO schema tables and I want to create the stored procedure in my development user schema KODYAZ
Open the SAP HANA Web-based Development Workbench Editor and create a new package by using the context menu on Content node.
Provide package details and press Create
On the package node, right click and choose New > HDB Procedure as follows
Enter the HDB procedure name in the "File name" textbox and press Create button
A default template for your new HANA database procedure will be displayed on the screen.
For this stored procedure, I want to define 2 parameters:
The first one is input parameter defined as IN named EMPLOYEEID with data type INT.
The second one is output parameter defined as OUT and is a table type.
Although it is best practise to create the table type and then use it in the HANA database procedure, I will define the output table parameter inline within the stored procedure definition.
This approach makes it a bit difficult and more coding during consuming the output parameter of the HDB procedure.
But it is not so important at the moment for the sake of this HANA tutorial.
So replace the first line of the procedure definition
PROCEDURE "KODYAZ"."Employee::EmployeeById" ( )
With these following lines of codes
PROCEDURE "KODYAZ"."kodyaz::EmployeeById" (
IN EMPLOYEEID INT,
OUT EMPLOYEE TABLE ( ID nvarchar(10), fullname nvarchar(100), emailaddress nvarchar(255) ),
OUT NumberOfRows int
)
In fact, SQLScript developers can only insert the paremeter definitions inside "()"
As the comment in HDB procedure template issues, we need to implement our business logic between BEGIN and END commands.
I want to display employee data including fullname and email address for the employee requested by passed employeeid parameter.
Here is the source table structure in SAP_HANA_DEMO schema.
As ABAP programmers and SQL developers can realize, there is no fullname table.
So first of all, we have to concatenate name parts like first name, middle name and last name into a single column value for fullname.
Let's assume that following requirements are my business logic in the HDB procedure.
Here is the SQLScript code to create fullname for the employee.
If you are not familir with TRIM() and IFNULL() functions, it may be some confusing but not
Especially middle name fields are NULL, contains no data, not even empty string.
So if I directly concatenate first name with a NULL valued middle name field, the produced output will be NULL.
Because of this reason, not to lose any data caused by inappropriate SQL coding, I first check if data is NULL or not.
If it is NULL, using IFNULL() function, I set it to '' which is empty string.
And then I concatenate the string pieces to form the fullname for the employee.
TRIM(IFNULL("NAME.FIRST",' ') || IFNULL("NAME.MIDDLE",' ') || IFNULL("NAME.LAST",' '))
A second requirement is that.
I have input parameter defined as integer.
But my EmployeeId field is defined as NVARCHAR
So If I immediately execute following SELECT statement, it returns no rows.
SELECT *
FROM "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Employees"
WHERE EMPLOYEEID = 5;
As seen in following screenshot from my sample database table content, you see there is a zero padding structure for EmployeeId column data.
So my second task within the HANA stored procedure is to convert input integer parameter value to zero padding varchar data type using SQL. I preferred to use the solution for padding zeros shared in my tutorial Replicate Function in SAP HANA Database using SQLScript
select LPAD(123456, 10, '0') replicated from dummy;
So I modified the HANA Database procedure .hdbprocedure source codes by replacing comment for procedure logic with following SQL code
EMPLOYEE =
select
EMPLOYEEID as ID,
TRIM(IFNULL("NAME.FIRST",' ') || IFNULL("NAME.MIDDLE",' ') || IFNULL("NAME.LAST",' ')) as fullname,
EMAILADDRESS
from "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Employees"
where EMPLOYEEID = :EMPLOYEEID;
select count(*) into "NUMBEROFROWS" from :EMPLOYEE;
OK, now I have my procedure code as follows
When you Save the last changes on the HDBProcedure codes, if everything is OK with the code, you will see following message in the console:
File /Employee/EmployeeById.hdbprocedure saved & activated successfully.
Let's now see how to execute stored procedure in HANA SQL.
In fact, to execute a stored procedure is a term coming from SQL Server.
On SAP HANA database or in ABAP development, the general term is to CALL procedure
So let's see how to call stored procedure in SAP HANA database using HANA SQL codes.
First of all, SQL programmer has to declare data variables; one for structured data defined in table type, others are two integer variables.
If in the definition of the HANA procedure, I had used a table that is already in DDIC, this section of code will be a single line code.
Since I did not use a predefined table structure, I have to repeat the table details for the variable once more.
To pass the desired employee's employee id value, I use integer variable eid. This is input parameter to the HANA stored procedure.
To convert it to varchar() data type, you will remember I used LPAD() for replicating 0's and provide leading zeros in front of the integer value.
Other integer parameter is output parameter. I set its value by simply counting the SELECT statements result using COUNT() function.
Then to execute stored procedure, or CALL stored procedure in HANA SQL, I use CALL command with parameters inside parenthesis following the stored procedure name.
do
begin
declare outtbl table (
ID nvarchar(10),
fullname nvarchar(100),
emailaddress nvarchar(255)
);
declare numberOfRows nvarchar(10);
declare eid nvarchar(10);
eid := 8;
CALL "KODYAZ"."Employee::EmployeeById" (eid, OUTTBL, numberOfRows);
select * from :OUTTBL;
select :numberOfRows, :eid from dummy;
end;
After HANA database stored procedure CALL statement, I can execute SELECT statements on table type variable which was the output parameter of the procedure.
To display scalar parameter values, I run SELECT statement using DUMMY table as seen in above SQLScript code.
