Hana Stored Procedure
What is a Hana Stored Procedure? When should they be used and how do they differ from other similar objects? We will answer these questions in the rest of this article.
What is a stored procedure?
A procedure is a sequence of SQL commands grouped in a single block. This code is precompiled and can be executed on demand, either manually by a SQL command (CALL, EXECUTE) or by a schedule (Job) or by calling it in another procedure. The stored procedure does not only allow to read the data but also to modify it thanks to the manipulation functions (INSERT, DELETE, UPDATE). Once set up, it can therefore be reused in your various data flows.
Creating a Procedure
There are two ways to create a procedure:
- Either by creating it directly in the repository
- Or by creating it with an SQL command such as :
CREATE ProcedureName AS [SQL Code].
The first option is more recommended because once the HDB object is created it is more easily transportable and reusable.
Stored Procedure vs Table Functions
The stored procedure can be similar to the "Table Function" is a function that returns a table that can be queried in the same way as a database table with a: FROM [Function name]. The two objects can in some cases be used in the same way, but here are the points in which they differ:
| Stored procedure | Table Function |
|---|---|
| Does not necessarily return a value | Will necessarily return a table |
| Read + Write (INSERT UPDATE SELECT) | Read only operation (SELECT) |
| Cannot be used in a calculated view | Can be used in a calculated view |
| Compiled only once | Compiled on each call |
| Can be an integral part of the data stream | Used mainly for reporting |
It is possible to use an existing table as the source of a stored procedure. We apply a certain number of transformations to it (filters, DML, joins, etc.) to come out with a new table that can be used in calculated views. You then just need to create a job that will run the procedure to have a table that refreshes itself at regular intervals. The table function also allows to make some simple modifications such as the join, it will not allow on the other hand to use the operations of DML.
The procedure returns a table in the same way as a "table function" but transformations are possible through the procedure.
Example of code for a simple procedure:
This example illustrates what can be done in a procedure, including SELECT, join and data manipulation commands (INSERT, TRUNCATE, UPDATE).
It is also possible to define "Input parameters" which work in the same way as those of the classic Hana views. The input parameter will optimise the performance of the procedure by reducing the amount of data to be processed for example.
The procedure below therefore contains an input parameter called FIELD1_IP which filters the FIELD1 field.
BEGIN
temp = SELECT
T1. "FIELD1″,
T1. "FIELD2″,
T2. "FIELD3″
FROM " TEST1 " T1 LEFT JOIN " TEST2″ T2
ON T1. "FIELD1″ = T2. "FIELD1″ AND T1. "FIELD2″ = T2. "FIELD2″
WHERE T1. "FIELD1 " = :FIELD1_IP;
TRUNCATE TABLE "TABLE";
INSERT INTO "TABLE" ( T3. "FIELD1″,
T3. "FIELD2″,
T3. "FIELD3″
FROM :temp T3;
END;
Conclusion
When it comes to developing a logic to be implemented in a data flow, the stored procedure offers many advantages: DML functions, reusability, optimised performance... The procedure as well as the table function will potentially be an obligatory passage in the realisation of your data flow, as both allow you to make transformations not achievable using classic Hana computed views.
Please feel free to consult the other Blogs written by our consultants to keep up to date with the latest SAP technologies and the various projects carried out by our teams.
Alexandre Chevé
Latest articles by Alexandre Chevé (view all)
- Tune your data integration in HANA with Stored Procedure - 25 June 2021
- Hierarchy in HANA - 12 January 2021
- Produce the FEC (accounting entries file) with a modern BI solution - 27 October 2020