SAP HANA HDB Stored Procedure – Part 1 

Welcome to then next SAP HANA HDB Stored Procedure tutorial. I hope the tutorials up until now were clear enough and you now have a solid foundation in HANA. In this tutorial, we learn another important concept of Stored procedures. This tutorial explains how to create an HDB Procedure which is the correct way to create stored procedures in the latest SAP HANA service packs. If you have already worked slightly or even read about stored procedures on other websites, you would find the below tutorial strange and different than what you already read. This is because HDB procedures are the correct way to develop stored procedures but the other websites were usually written back in 2013 or around that time when these techniques did not exist.

Later on, we would also add another tutorial to show the old way of developing stored procedures just so you are not surprised if you see one in a project that still uses the old one for some reason.

Before we start developing anything, we need to understand what a stored procedure is and why it is used. If a logic is required to be used again and again in different developments, we could either waste time writing the same piece of code each time in each development artifact or we create a reusable artifact containing this piece of code and call it wherever needed.

Let’s examine 2 scenarios for using stored procedures:

1. Reusable SELECT blocks

If lots of views require the join result of two specific tables based on a filter, you could create a stored procedure that joins these two tables. Then in your view, you could call this view with the filter and get the result you need for your further steps. This reusable procedure helped you save your time and effort in coding. But if you have read the table functions tutorial table function, you already know that this requirement can be fulfilled easily by Table functions which are also the recommended development object in these scenarios where the reusable code we wish to make only involve SELECT statements. But, as explained this is one of the things stored procedures could do but should not do. Try to use only table functions in these scenarios. But since many projects still create these scenarios using Stored procedures, we will also illustrate one using an example.

2. For other statements like INSERT, DELETE, UPDATE

When you wish to create a code that deletes, inserts or updates data in tables, table functions would become inadequate as it doesn’t support these operations. This is the best use case for stored procedures in SAP HANA and we would take up an example to understand this in detail once the first scenario is done.

For now, let’s quit the talk and walk the walk.

Developing an SAP HANA Stored Procedure – HDB Procedure Method

Scenario 1 – Reusable SELECT Blocks

To start, switch to the repository tab (You must be in the development perspective to be able to view this tab – Click here for the HANA Studio tutorial if you are unsure on how to switch to this perspective)

Right click on your package and navigate to New Other from the context menu.

Here, we would search for the name of the object we need in the search area marked below.

Search for “procedure”. The below options would show up. Select “Stored Procedure” and click “Next”.

The below window opens up asking for a file name. This would be the part of the procedure name as well.

As seen below, we provide the name as sales_procedure. Press finish.

1

This creates a new SAP HANA HDB Stored Procedure and opens up the below block of code.

This is all auto generated and you need not worry about writing any of it. It’s quite similar to the auto generated code in our previous tutorial on table functions. We’ll explain these lines one by one. But first, let’s do a bit of clean up.

We start by removing the comments between the BEGIN and END block in green. They serve no real purpose. The result should look as below.

Now, we remove the comments in green on line 4 as well as we will not be specifying a default schema here. The following screenshot shows the cleaned up code.

In the next part of this tutorial, we will understand the code generated by our SAP HANA HDB Stored Procedure .

If you liked the tutorial, please share it on social media using the share buttons below and make sure you subscribe to our newsletter to get the latest updates when new tutorials are added.

<<Previous Tutorial                                                                                                Next Page>>