SQL Stored Procedures in SAP HANA – An Essential Application for SAP HANA

In our DataFlair series of learning application of SQL in SAP HANA, our last topic is SQL stored procedures in SAP HANA. So, let’s start by taking an insight into SQL stored procedures.

What are SQL Stored Procedures?

An SQL stored procedure is a set or group of SQL statements with a user-assigned name. This collection of SQL statements is known as a procedure and is stored in the RDBMS as a group of reusable statements.

We use these stored procedures primarily to access and modify the data residing in databases in a certain way. One major advantage of SQL stored procedures is that they can be used in multiple databases and can be shared by multiple programs.

SAP HANA SQL Stored Procedures

Following the basic concept of stored procedures, SAP HANA SQL stored procedures are also a unit or module i.e. a set of SQL statements made to perform a specific task. In SAP HANA, stored procedures are used to creating modular designs for large programs with complex designs.

Also, a different procedure can be called by another procedure. This process is the calling program. SQL procedures stores SQL statements aimed at performing a specific task at the database according to the proper process flow.

In SAP HANA, the SQL stored procedures can be created as read-only procedures or read-write procedures. Also, the stored procedures can be created at two levels in SAP HANA, at a Schema Level (in Catalog Node) or at a Package Level (in Content Node) depending on the user’s preference.

The task stored in the stored procedure will perform itself on the database whenever the set of statements are being executed in an SAP HANA application.

Key Benefits of SQL Stored Procedures

Some advantages of SQL stored procedures that work in favor of SAP HANA are as follows:

• The stored procedures in SAP HANA ensures performance optimization as the program execution occurs in a systematic way. The result of the execution of one set of SQL statement or procedure will determine the execution of the next set of statements.
• SQL stored procedures ease the process of scripting for SAP HANA users. As it is not necessary for the users to sneak under the hood and understand all the logical complexity behind SQL scripting to apply on the database. The users can just use the pre-created SQL procedure by knowing what task a procedure performs on the database.
• Users can just call a procedure in a program by using its procedure name. Every procedure follows a business logic and performs a specific task on the database.
• The SQL procedures return data as integers, characters or cursor variables in the form of output parameters.

Syntax of SQL Stored Procedure in SAP HANA

CREATE PROCEDURE <proc_name> [(<parameter_clause>)] [LANGUAGE <lang>]
[SQL SECURITY <mode>] [DEFAULT SCHEMA <default_schema_name>]
[READS SQL DATA [WITH RESULT VIEW <view_name>]] AS
{BEGIN [SEQUENTIAL EXECUTION]
<procedure_body>
END
| HEADER ONLY }

Syntax Elements of Stored Procedure

• <proc_name> is the name of the procedure.
• <parameter_clause> defines parameters such as IN, OUT and INOUT.
• LANGUAGE <Lang> defines the programming language for the procedure. The default language is SQLSCRIPT.
• SQL SECURITY<mode> defines the security mode for the procedure. There are two security modes; DEFINER and INVOKER. The default is DEFINER.
• <default_schema_name> specifies the schema for unqualified objects in the procedure body. If no schema is specified, then the current session’s schema is taken.
• We use READS SQL DATA to mark a procedure as a read-only procedure. It cannot perform modifications in the database but we can only use it to call other read-only procedures.
• WITH RESULT VIEW<view_name> specifies a result view which is used as the output of other read-only procedure.
• We use SEQUENTIAL EXECUTION to force a sequential execution of the procedure logic.
• <procedure body> contains the main body of the procedure in the language selected.
• We use HEADER ONLY to create procedure properties with OID.

How to Create SQL Stored Procedures in SAP HANA?

Follow these easy steps to create a stored procedure in SAP HANA:

Step 1: Open the SAP HANA Modeler. Expand the Content folder.

Right-click on the package name > New and select Procedure…

Step 2: Enter the details specific to the new procedure such as Name (for instance, DataFlair1), Description, Default Schema, Package, Run rights, Access Modes, Languages.

Click on Finish.

A script view pane will open where you write your SQL script for stored procedures. The general script is:

PROCEDURE “<procedure_name>” ( )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER

-- DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS

BEGIN

/**************************************
Write your procedure logic

************************************/

END

Step 3: Add a SELECT statement between BEGIN and END.

Save the script by clicking on the (green tick icon) Save button.

Step 4: Click on the Execute button to execute the script.

Upon successful execution of the script, the new stored procedure can be seen under the Procedures folder of the Content node under your schema.

Summary

This concludes our tutorial on SQL stored procedures and its significance in SAP HANA. After completing this tutorial, you have learned all the uses of SQL in SAP HANA. We hope you are clear with all the concepts.

If you have any queries related to SQL stored procedures or SAP HANA, do leave your comments below. We will be happy to help.