Mastering SQL Script and SAP HANA Stored Procedure

Introduction to SQL Script and SAP HANA Stored Procedure

We explored the modern SQL which helps to push the code to the database and helps us with performance improvement. Also, the new age SQL is concise and allows us to do kinds of stuff which were never possible in ABAP earlier. In this article, we would check SQL Script and basic about Stored Procedures.

SQL Script Definition?

SAP HANA SQL document says: SQL Script is a collection of extensions to the Structured Query Language (SQL).

Google/Wiki says: An SQL script is a set of SQL commands saved as a file in SQL Scripts. An SQL script can contain one or more SQL statements or PL/SQL blocks. You can use SQL Scripts to create, edit, view, run and delete script files.

SAP further simplifies, SQL Script is and extension to ANSI Standard SQL. It is an interface for applications to access SAP HANA database. SQL Script is the language which can be used for the creation of stored procedures in HANA.

It can have Declarative and Orchestration Logic.
ELECT queries and Calculation Engine(CE) functions follow Declarative Logic.
DDL, DML, Assignment and imperative follow Orchestration Logic.

Data transfer between database and application layer can be eliminated using SQL Script. Calculations can be executed in the database layer using SQL Script to obtain maximum benefit out of SAP HANA database. It provides fast column operations, query optimization and parallel execution (you will read these lines time and again in different language in this post).

Motivation?

SQL Script can be utilized to write data-intensive logic into the database instead of writing code in the application server. Before ABAP 740, most of the data needed for manipulation were copied from database to the application server and all calculation, filtration and other logic were implemented on these data. This technique is a strict No-No for optimization and performance improvement of the ABAP code. SQL Script helps to take maximum benefit of modern hardware and software innovation to perform massive parallelization on multi-core CPUs.

SAP suggests, SQL Script comes into picture when HANA modeling constructs like Analytic or Attribute views fail. Someone’s failure is other one’s success.

Why?

Simply for Code to Data(base) shift.

What is SQL Script? Why we need SQL Script? What is the motivation for having SQL Script?

Did we not answer these What, Why and What above? OK, let’s start from the beginning. The relational database model RDBMS was introduced back in 1970’s by Edger F. Codd (you might remember from college curriculum. something ringing? or did I help you remember one of your beautiful/cute crushes from your college days).

As per RDBMS, the Database must be normalized 1NF, 2NF, 3NF, BCNF and 4NF in order to have ACID properties of the data.

Google says: In computer science, ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties of database transactions.

A simple example would be splitting of data into Header and Item to pass the ACID test. In other words, data is stored in two-dimensional tables with the foreign key relationship instead of having redundant rows and columns in one table.

But the use of digital media has exploded in the recent past both in the consumer world and enterprise world (in a way both are the same thing). This has led to an exponential increase in the amount of the data being stored in the databases. On the other hand, the expectation from users is minimum response time, in some cases zero response time.

We can take the example of TATKAL IRCTC online train ticket booking. There will be few Hundred Thousand if not Million users who want to book a Tatkal ticket and expectation is there should not be any delay from the system. 2 Hundred Thousand transactions (form fill up, validation, payment using credit/debit card or online banking) per minute was one of the criteria for the vendor for IRCTC quote.

For our readers who are outside India, TATKAL’s literal English translation is “INSTANT”. You can consider TATKAL train booking as the Amazon Black Friday Sale of iPhone 6S at 99$. The sale begins exactly at 10:00 AM on 11/24/2016 till stock last. Isn’t iPhone 6S at 99$ an amazing deal? Even if you already have iPhonse 6S, you would still try to buy it. Exactly at 10:00 AM, thousands of users try to order that phone. Most users cannot log in, the system is hung. Some lucky who are able to log in, are not able to hit BUY button. Few others who were successful at hitting the BUY button are still waiting for Payment to be entered. Few lucky who have successfully entered the Payment get the final message, “Sorry, iPhone 6S is out of stock. Please try later“.

HANA is able to deliver this. Absolutely no response lag time by using the techniques which are both hardware and software innovation. Hence it is called as Appliance and not just any Database.

Now, if we want to use the power of fast computing of HANA Database, we have to push all the data intensive computations from application server (ABAP Server) to HANA Database layer. Here SQL Script plays the major part in doing this.

Like any SQL language, SQL Script is used for querying the Database, in this case, HANA Database. SQL Script is as per SQL 92 Standards. This is the sole language used for writing Stored Procedures in the HANA Database.

How does it differ from SQL statements in ABAP?

i) Normal SQL returns only one result set while SQL Script can return multiple results.
ii) Modularization is possible in SQL Script i.e. humungous intricate business logic can be split into smaller pieces of code which are more readable and understandable.
iii) Local variables for the transitional result can be defined in SQL Script. Normal SQL needs globally visible data types/views for intermediate logic.
iv) Control statements like IF/ELSE are available in SQL Script but not in normal SQL

SQL Script follows the Code to Data Paradigm with pushing of data intensive computations to HANA Database. With this, it eliminates the transfer of data from DB to the application server aka ABAP AS. This fully exploits the capability of HANA database achieving the maximum throughput with absolutely no response time.

SQL Script is a very powerful tool. We have always avoided using joins, ordering by clause in ABAP SQL statements. All these are welcome in ABAP 740. We can also use query inside a query etc.

SQL statements can be broadly divided into below three categories:

Data Manipulation Language (DML) SELECT, INSERT, UPDATE
Data Definition Language (DDL) CREATE , ALTER DROP
Data Control Language (DCL) GRANT ,REVOKE

SQL Script also supports the below primitive data types:

TINYINT, SMALLINT, INTEGER, BIGINT, DECIMAL (p, s), REAL, FLOAT, DOUBLE, VARCHAR, NVARCHAR, CLOB, NCLOB, VARBINARY, BLOB,DATE, TIME, TIMESTAMP

Table Creation and Alteration

We can create a table by using the GUI or by writing SQL Statement.

a) Create using SQL Statement

create column table "<Schema_name>"."ZZSTUDENT"( "ROLLNUMBER" NVARCHAR (10) not null,
 "NAME" NVARCHAR (10) ,
 "YEAR" NVARCHAR (4) );

Our schema name was SYSTEM. So, out SQL looks like below.

create column table “SYSTEM”.”ZZSTUDENT”( “ROLLNUMBER” NVARCHAR (10) not null,
“NAME” NVARCHAR (10) ,
“YEAR” NVARCHAR (4) );

Hopefully, you know by now that you need to be in SAP HANA Development Perspective and choose your schema and write at the SQL Console. When you hit execute, the table is created.

b) Create using GUI

For GUI Click on New Table and for SQL Script above Click on Open SQL Console

Both (SQL and GUI) achieve the same function of creating the table

Hit execute button

The tables that are created will be available in the respective Schema.

CREATE COLUMN TABLE "<SCHEMA_NAME>"."ZZENROLL"( "CODE" NVARCHAR (10) NOT NULL,
 "ROLLNUMBER" NVARCHAR (10) NOT NULL,
 "YEAR" NVARCHAR (4) );
 
CREATE COLUMN TABLE "<SCHEMA_NAME>"."ZZCOURSE"( "CODE" NVARCHAR (10) NOT NULL,
 "NAME" NVARCHAR (10));

With the above statements, we have created Column tables (ZZENROLL, ZZCOURSE), along with these, we can also create a Table Type (LT_OUT) and Row storage tables.

CREATE TYPE "<SCHEMA_NAME>"."LT_OUT" AS TABLE ( "STUDENT_NAME" VARCHAR (10) NOT NULL,
 "COURSE_CODE" VARCHAR (10),
 "COURSE" VARCHAR (10));

Some examples of ALTER TABLE

a) Adding new field

ALTER TABLE "<SCHEMA_NAME>"."ZZSTUDENT" ADD ("CITY" VARCHAR (10) NULL);

b) Altering/Changing field type

ALTER TABLE "<SCHEMA_NAME>"."ZZSTUDENT" ALTER ("CITY" VARCHAR (30) NULL);

Changed type from 10 VARCHAR to 30 VARCHAR.

c) Altering Table Type

ALTER TABLE "<SCHEMA_NAME>"."ZZSTUDENT" ALTER TYPE ROW;

Insert Data into table

Data can be inserted using SQL Console. Below are some examples:

INSERT INTO "<SCHEMA_NAME>"."ZZSTUDENT" VALUES ( '10', 'SACHIN', 'MUMBAI');
 
INSERT INTO "<SCHEMA_NAME>"."ZZCOURSE" VALUES('100','HINDI');
INSERT INTO "<SCHEMA_NAME>"."ZZCOURSE" VALUES('200','ENGLISH');
INSERT INTO "<SCHEMA_NAME>"."ZZCOURSE" VALUES('300','MATHS');
 
INSERT INTO "<SCHEMA_NAME>"."ZZENROLL" VALUES ( '100', '10', '2005');
INSERT INTO "<SCHEMA_NAME>"."ZZENROLL" VALUES ( '200', '10', '2005');
INSERT INTO "<SCHEMA_NAME>"."ZZENROLL" VALUES ( '300', '10', '2005');

SQL query examples

Lets us see some of the SQL Query Examples on the above data which we have populated.

a) Let’s start with a simple query

SELECT NAME
FROM "<SCHEMA_NAME>"."ZZSTUDENT"
WHERE ROLLNUMBER = '10';

b) Nested Select or Select inside a Select (name of students who have enrolled for course code 100)

SELECT NAME
FROM "<SCHEMA_NAME>"."ZZSTUDENT"
WHERE ROLLNUMBER IN (SELECT ROLLNUMBER
 FROM "<SCHEMA_NAME>"."ZZENROLL"
 WHERE CODE = '100');

c) A join example

SELECT A.NAME AS STUDENT_NAME,
 B.CODE AS COURSE_CODE,
 C.NAME AS COURSE
 FROM "<SCHEMA_NAME>"."ZZSTUDENT" AS A
 INNER JOIN "<SCHEMA_NAME>"."ZZENROLL" AS B
 ON A.ROLLNUMBER = B.ROLLNUMBER
 INNER JOIN "<SCHEMA_NAME>"."ZZCOURSE" AS C
 ON B.CODE = C.CODE
 WHERE C.CODE = '100';

These are very basic examples, only for the concept. In real time it would not be this simple. Hope the above examples give you a hang of SQLScript. It might be a little different for ABAPers but it is not entirely from another planet. We have been writing Open SQL in ABAP and the above SQL Scripts are our nearest cousins. Nothing to be scared of.

Stored Procedure

Stored Procedure is the natural choice for the next topic as SQL Script is the only language used for creating Stored Procedures. A procedure is a unit/block of related code that performs a certain task. ABAPers can relate Stored Procedures as the subroutines or methods (not truly though). The motivation for having the procedure is reusability.

All the advantages of SQL Scripts are there in Stored Procedures. SAP HANA procedures help us to put data-intensive complex logic into the database, where it can be fine tuned and optimized for performance and return the small result set. Procedures help to control the network and processor load by not transferring large data volume from database layer to application layer. Stored Procedures can return multiple scalar (single value), tabular/array result which is not possible in normal SQL. Like in ABAP programming, local variables can be declared and used in Procedures and hence we do not need to create temporary tables to be used for storing intermediate data as in the case of normal SQL.

General rule

Each statement is to be completed with a semicolon “;” and variable assignment is done using colon “:”.

An example of Stored Procedure using SQL Console. Please note we need to create Procedure in SAP HANA Modeler Perspective.

CREATE PROCEDURE _SYS_BIC.ZZPROCEDURE(
IN IV_CODE NVARCHAR(10),
OUT LT_OUTPUT <SCHEMA_NAME>."LT_OUTPUT")
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
 
/********* Begin Procedure Script ************/
BEGIN
LT_OUTPUT = SELECT A.NAME AS STUDENT_NAME,
 B.CODE AS COURSE_CODE,
 C.NAME AS COURSE
 FROM "<SCHEMA_NAME>"."ZZSTUDENT" AS A
 INNER JOIN "<SCHEMA_NAME>"."ZZENROLL" AS B
 ON A.ROLLNUMBER = B.ROLLNUMBER
 INNER JOIN "<SCHEMA_NAME>"."ZZCOURSE" AS C
 ON B.CODE = C.CODE
 C.CODE =:IV_CODE;
END;
/********* End Procedure Script ************/

One can create Stored Procedure with the help of GUI. This is much faster and one tends to have less number of human error.

Right click on content -> select the Procedure

Put the SQL Script (same as above) in between BEGIN and END (ideally Output and Input Parameters should be created).

Create the output parameters: Right click on Output, Input Parameters and declare the name and types.

Click on save and validate

Click on activate

To test the procedure created above, we need to call the procedure in the SQL Console. Generic syntax for calling procedure is below.

CALL PROCEDURE_NAME (values1, values2 ,……);

For our example

CALL ZZPROCEDURE(100,?)

Food for thought: Check what error we get if we just write below SQL without “?” as second parameter.

CALL ZZPROCEDURE(100)

Database Procedure Proxy

We have created a procedure in HANA Database. Till now, only the half part is done. If we want to achieve the Code Push Down Paradigm then the next part would be calling the procedure in SAP ECC. This is achieved using Database Procedure Proxy.

Go to File -> New -> Others -> Database Procedure Proxy.

Provide the HANA Procedure name.

ZZ12MYDBPROXY is the name of the proxy. Choose the transport or save as local.

Click on finish

Click on Activate button as shown below

The same Database Procedure Proxy can be displayed in SE24 at ABAP AS level.

Calling this Database proxy is very much similar to calling a function module/method.

CALL DATABASE PROCEDURE ZZ12MYDBPROXY
EXPORTING iv_code = p_code
IMPORTING lt_out = tb_output.

Sample program to consume the HANA Stored Procedure in ABAP and display the output.

**---------------------------------------------------------------------*
**  TYPES                                                               *
**---------------------------------------------------------------------*
TYPES:
 
  BEGIN OF ty_output,
    student_name TYPE char10,
    course_code  TYPE char10,
    course       TYPE char10,
  END OF ty_output.
 
**---------------------------------------------------------------------*
**  DATA                                                               *
**---------------------------------------------------------------------*
DATA:
    it_output TYPE TABLE OF ty_output.
 
**---------------------------------------------------------------------*
** SELECTION SCREEN                                                    *
**---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-s01.
PARAMETERS: p_code TYPE char10.
SELECTION-SCREEN END OF BLOCK b1.
 
**---------------------------------------------------------------------*
** START-OF-SELECTION.                                                 *
**---------------------------------------------------------------------*
START-OF-SELECTION.
* Consume the stored procedure in ABAP
  PERFORM call_db_procedure.
 
**---------------------------------------------------------------------*
** END-OF-SELECTION.                                                   *
**---------------------------------------------------------------------*
END-OF-SELECTION.
* Display the output
  PERFORM display_output.
 
**&---------------------------------------------------------------------*
**& SUB ROUTINES
**&---------------------------------------------------------------------*
 
*&---------------------------------------------------------------------*
*&      Form  CALL_DB_PROCEDURE
*&---------------------------------------------------------------------*
*       Consume the database procedure
*----------------------------------------------------------------------*
FORM call_db_procedure.
 
* Callling Database Procedure
  CALL DATABASE PROCEDURE zz12mydbproxy
    EXPORTING
      iv_code = p_code
    IMPORTING
      lt_output = it_output.
 
ENDFORM.
 
*&---------------------------------------------------------------------*
*&      Form  DISPLAY_OUTPUT
*&---------------------------------------------------------------------*
*       Display the Report
*----------------------------------------------------------------------*
FORM display_output .
 
* Display the output
cl_demo_output=>display_data( it_output ).
 
ENDFORM.

Let us test it.

Output

We showed Database Proxy is one way to consume Stored Procedure in ABAP. The other way is calling it through Native SQL. Let us extend this long post a little longer. This is the last part, trust me.

Consumption of HANA Stored Procedure in ABAP

Two methods of consuming HANA Stored Procedure in our ABAP programming are:

i) Calling SAP HANA Stored Procedure through Native SQL
ii) Using Database Procedure Proxy to expose HANA Procedure (already seen above)

Both has pros and cons, but Database proxy has an upper hand over the native SQL.

So-called Advantage of Native SQL process over Database Proxy

a) Easy development and lesser effort. Once we have the stored procedure created in the HANA DB, we just need to write native SQL to access the procedure directly.
b) No extra ABAP artifact means less maintenance. Since there is no other ABAP artifact to be created (like Database Proxy), less maintenance in this case
c) Native SQL Development can be done in SAP GUI as well as ADT, whereas for DB proxy has to be done via ADT only

The advantage of Database Proxy over Native SQL process

a) Native SQL Process is a little tedious and prone to human error.
b) Full advantage of ABAP Development Tool can be taken for Database Proxy.
c) ABAP developers would find Database Procedure Proxy call similar to Function Module/Method calls. Hence more comfortable.
d) In the case of any change in Database Procedure the code changing process is manual. But for proxy it is semi-manual and the proxy can be synchronized (merged/deleted).

We have just scratched the surface. We need to dig a little more deeper to appreciate the power of SQL Script and Store Procedures. We can have a separate post in detail on consumption of Stored Procedures in ABAP. Also, we can check how we can debug the procedures.