SAP HANA Stored Procedure – Part 2
Welcome back to the second part tutorial of the SAP HANA Stored procedure tutorial using HDB Procedures. If you have directly landed on this page, please visit the first part of this tutorial before reading this second part to maintain proper continuity and less confusion.
Now, we come to understanding this auto generated code. Line 1 tells the system that this is a Stored Procedure that we are writing, After the keyword PROCEDURE, is the name of the schema where this procedure would be stored followed by the package name and part of the procedure name we provided during file creation. This entire name together constitutes the procedure name.
The two brackets are to be used when the procedure expects an input parameter. In this first example, we leave it blank.
The LANGUAGE keyword in line 2 informs the HANA system on the language that will be used to code this logic. Usually, this would be SQLSCRIPT. Optionally, this can also be written in R language.
1
There are 2 security modes:
- INVOKER: In this case, the function executes with the privileges of the invoker of the procedure.
- DEFINER: In this case, the function executes with the privileges of the definer of the procedure.
It’s always best to leave it as INVOKER so that whenever this function is called, it will run based on the invoking user’s privileges.
READS SQL DATA tells the HANA system that this procedure is meant to only read data out of tables and would not be performing any other type of actions on them like deleting or inserting data. The AS keyword marks the beginning of the BEGIN-END block.
The BEGIN-END block marks the area within which the procedure logic must be written. Make sure you don’t remove the semicolon after END keyword.
Starting off simple, let’s create add a simple SQL code to pick up all fields and data from VBAK table in the schema ECC_DATA. Write it between the START and END block as shown below and activate this procedure using the button marked in red.
Now, to check if this works, switch back to the systems tab and start the SQL console by clicking on the SQL button shown below.
1
The CALL keyword is used to run or “Call” a procedure. The syntax is:
CALL < Full procedure name with schema and package paths> (<input parameter values if any>)
The first line of our procedure code gives you this procedure name. Just copy and use it here in the call statement. Since we did not use any input parameters, a blank set of brackets is provided. Press F8 or the execute button to run this CALL statement.
The below result opens up confirming that the basic example of our stored procedure works as expected.
Thank you for reading this second part of the SAP HANA Stored Procedure tutorial. Please continue to Part 3 of the tutorial on the next page.
Keep Going!
<<Previous Tutorial Next Page>>
