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

Creation of HDB procedure (1)Creation of HDB procedure (2)

  • Or by creating it with an SQL command such as :

CREATE ProcedureName AS [SQL Code].

Create SQL procedure

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 procedureTable Function
Does not necessarily return a valueWill 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 streamUsed 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.



temp = SELECT

T1. "FIELD1″,

T1. "FIELD2″,

T2. "FIELD3″


ON T1. "FIELD1″ = T2. "FIELD1″ AND T1. "FIELD2″ = T2. "FIELD2″




T3. "FIELD2″,

T3. "FIELD3″

FROM :temp T3;




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.


The following two tabs change content below.

Alexandre Chevé

Alexandre is a junior consultant specialising in Business Intelligence. Dynamic and rigorous, he has a solid knowledge of the SAP BI suite and SAP visualisation tools.
Share This