Create Procedure on SAP HANA using SQLScript and Execute

In this SQLScript tutorial, I'll show ABAP developers how to create SQL SAP HANA procedure with a sample case and call database procedures with input parameter on SAP HANA. SAP HANA developers coding SQLScript to create procedures (aka stored procedures) can use samples given in this tutorial as a template and use them on their SAP HANA database developments.

What is SQL Database Procedure on SAP HANA?

Just like on other data platform tools like Oracle databases or SQL Server databases, SQLScript developers on SAP HANA database can also create stored procedures as a unit of code blocks that take input parameters and output a result or do a task (like inserting a new row into a database table, or simply deleting a record from SQL table) by processing a business logic. Procedures include stored sql scripts that are formed of more than a single SQL statements. They have their own data declarations where SQL programmers can create variables and use these variables temporarily during the execution time. A stored procedure can work just like a SQL View where only a SELECT statement is executed or can contain hundreds of code lines with complex SQL statements and business logic.

Create Sample Stored Procedure on SAP HANA Database

On hanatrial.ondemand.com using SAP HANA Web-based Development Workbench I connected to a catalog (database) and create sample stored procedure which is very basic, without any input or output parameters.
Following sample SQL stored procedure only returns all data from a specific SAP HANA database view named "SYS"."M_CS_TABLES"

"SYS"."M_CS_TABLES" system view stores meta data about SAP HANA database tables which are created as column tables

Following SQL Select statement returns the list of database tables created as column table under the specified schema on a SAP HANA catalog


select * from "SYS"."M_CS_TABLES" where schema_name = 'KODYAZ';

Code

Here is my sample SAP HANA database

SAP HANA database schema

Let's create our first SAP HANA procedure using above SQL Select statement.
SQL procedure will be created under KODYAZ schema.
SQL procedure does not have any input or output parameters as procedure arguments. But a result set will be returned which is the column tables list as the outcome of the SELECT command.


Create Procedure "KODYAZ"."Database_Tables_Metadata"()

Language SQLScript

as

Begin

select table_name, record_count

from "SYS"."M_CS_TABLES"

where schema_name = 'KODYAZ';

End;

Code

As seen above and also in below screenshot, SQLScript programmers create stored procedures on HANA database using Create Procedure command.

The database procedure is created under a schema, it is defined just before the procedure name. Database schema and procedure name is seperated from each other with a "." (dot).

Language SQLScript is used. SQL code block takes place between "BEGIN" and "END" .

create procedure using SQLScript on SAP HANA database

Execute Stored Procedure on HANA Database

SAP developers can execute SAP HANA procedures using "CALL" command. Here is the way how to call our sample stored procedure using SQL.


CALL "Database_Tables_Metadata"();

-- or using schema name

CALL "KODYAZ"."Database_Tables_Metadata"();

Code

call and execute SAP HANA database procedure

Create HANA Database Procedure with Input Parameter

Let's modify our first SAP HANA procedure and add an input parameter. Also instead of M_CS_TABLES system view, I'll use M_TABLES database table.

Unfortunately, when SQL developer has to change the number of parameters of the SAP HANA procedure it is not possible to change procedure using ALTER PROCEDURE command.
We have to DROP PROCEDURE (delete stored procedure from HANA catalog) and then re-create the HANA procedure with new parameters.

Here is how to drop SAP HANA procedure (delete procedure) using SQL command


Drop Procedure "KODYAZ"."Database_Tables_Metadata";

Code

And following code creates the new SAP HANA procedure with new input parameter which is used for filtering tables by schema name dynamically.


Create Procedure "KODYAZ"."Database_Tables_Metadata"(i_schema nvarchar(16))

Language SQLScript

as

Begin

select table_name, record_count

from "SYS"."M_TABLES"

where schema_name = :i_schema;

End;

Code

SQL programmers will realize that the input parameter is defined with parameter name and data type between "()" right after the procedure name.

SAP HANA database procedure with parameters

Within SQL procedure, HANA database programmers can use the parameter by using ":" before the input parameter name like ":i_schema" in WHERE clause of the SELECT statement

And following SQL command shows how to CALL PROCEDURE with parameter. The parameter is passed to the SQL command between "()" and for string parameters between ''. If sample input parameter to the stored procedure was a numeric data type, then the input parameter value will be directly written in the parameter list.


CALL "KODYAZ"."Database_Tables_Metadata"('KODYAZ');

Code

It is important to note that the input parameter values in CALL statements must match to the parameters order in HANA database procedure definition.