ABAP on SAP HANA. Part VII. SQL Script and SAP HANA Stored Procedure

Consume Stored Procedures in SAP HANA
Share on Facebook17Share on LinkedIn57Tweet about this on TwitterShare on Google+0
Please Share!!

Introduction to SQL Script and SAP HANA Stored Procedure

In the previous post (New Age SQL for ABAP), 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.
SELECT 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.

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 languages in this post).

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). 😛

SQL Script in SAP HANA

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. Read more about ACID properties here.

Stored Procedures in HANA

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.

You can read more about Normalization here.

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. This is a separate topic altogether which we have covered in SAP HANA from Space Level.

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.

Code to Data Paradigm

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.

4 SQL SELECT

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

Read more about primitive data types here.

Table Creation and Alteration

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

a) Create using SQL Statement

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

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

Consume Stored Procedure in ABAP

 

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

7

Hit execute button 8

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

9

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.

10

Some examples of ALTER TABLE
a) Adding new field

b) Altering/Changing field type

Changed type from 10 VARCHAR to 30 VARCHAR.

c) Altering Table Type

3

Insert Data into table

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

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

11

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

12

c) A join example

4

 

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. 🙂

Also Read : All you wanted to know about CDS Views

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.

5

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

SAP HANA

15

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.

16176
Click on save and validate  19

Click on activate   
20

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

For our example

7

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

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.

8

Provide the HANA Procedure name.

HANA Stored Procedure

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

24
Click on finish

25

Click on Activate button as shown below

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

27

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

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

Let us test it.

Consuming HANA Procedure

Output

Calling Database Procedure in ABAP
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.

Next Post: SAP HANA ABAP Tutorial. Part VIII. ADBC – ABAP DataBase Connectivity

If you want to get such useful articles directly to your inbox, please SUBSCRIBE. We respect your privacy and take protecting it seriously.

If you liked this post, please hit the share buttons and like us on facebook.

Please add your comments and criticism below and help us improve. Please do not hold back!!

Useful Tutorials in SAPYard

1. ABAP for SAP HANA Tutorials
2. ABAP Web Dynpro Tutorials
3. GOS Tutorial
4. OOPs ABAP Tutorial
5. HANA Tutorial
6. SAP Netweaver and OData Tutorial
7. SAP Adobe Form Tutorial
8. SAP Fiori Tutorial
9. SAPUI5 Tutorial

Call for Guest Authors and Contributors to write SAP Articles on our page and get noticed and also receive cool Gifts.

Do you have any tips or tricks to share? Do you want to write some articles at SAPYard? Please REGISTER and start posting and sharing your knowledge to the SAP world and get connected to your readers. Please check our simple guidelines for contributing your articles and receiving the gifts.

Share on Facebook17Share on LinkedIn57Tweet about this on TwitterShare on Google+0
Please Share!!

About the Author

Mohsin Ahmed
Mohsin Ahmed

Mohsin is a Computer Science Engineer by education and profession, with more than 10 years of experience in SAP. Currently, he is working at Capgemini as a Senior Technical Consultant.

He loves watching movies and his favorites in the recent past are Whiplash, Bajirao Mastani. He is very fond of long drives. Not only does he experiment with ABAP, HANA, OData etc at work but also experiments at his kitchen with new and crazy recipes.

Find more about him on LinkedIn.

7 Comments on "ABAP on SAP HANA. Part VII. SQL Script and SAP HANA Stored Procedure"

  1. Dear folks,

    How can we call Stored procedures from non sap to sap.
    My stored procedure is created in other database, need to call that from ABAP program.

    How can we achieve this…please guide me with your thoughts.

    Regards,
    Venubabu Divvi

    • Dear Venu,

      To be honest, our team has not come across such requirement. We do not have the exact answer.

      The alternative which we can think is to use ADBC. ADBC should meet your requirement. Concatenate SQL strings in a program in ECC and connect to the non-sap system and let the SQL execute in that system. For little more info on ADBC, please check the below link.

      http://www.sapyard.com/abap-on-sap-hana-part-viii/

      We would request the experts to respond to it more accurately.

      Do let us know, if you have any answer.

      Regards,
      Team SAPYard.

    • Dear Venu – Some more info for you.

      Source : SAP HANA HA400 training material.

      Stored procedures are not HANA specific and can be created and used in any database. How to call the stored procedures in ABAP code?
      Ans: Before ABAP 7.4 is: native SQL.
      Using the ABAP Database Connectivity (ADBC) API for this purpose is preferable because it supports where-used analyses, exception handling and more. Make sure to double check the SQL syntax.

      You could decide for the “top-down” approach and create the stored procedure using the ABAP stack, that is also using native
      SQL in an ABAP program. Just concatenate the SQL statements in the ABAP program and call the class SQL query/methods.

      Let us know if you need more info.

      Regards,
      Team SAPYard.

      • Actual requiremnet is i need to get the latitude and longitude information by calling stored procedures.

        For this i have a program with google maps but compared bingo maps google maps is 200% costly and my client is already using bingo maps in their database to get latitude and longitude.

        So if i can call the stored procedure to the other database (Ex: SqL Database) that will give the result with latitude and longitude.

        Please give me your support hands to achieve the above…

        Regards,
        Venubabu Divvi

        • Dear Venu – You can achieve this using native SQL or ADBC after connecting to the other database (eg SQL Database). Assuming that your SAP system already has link to the other database. Else you system infrastructure team need to build this connection to other database first (t-code SM59).

          We do not recommend to use string to define sql connexion (i.e. not EXEC SQL — ENDEXEC), due to issue maintenance. Use classes instead, it will be more professional. ADBC has some classes which can be easily used. Just build the SQL statement to SELECT data from the Map tables and pass it to ADBC class.

          We have a working code on ADBC in the next article. Please check this link.

          SAP ABAP for HANA. Part VIII ADBC : ABAP DataBase Connectivity

          All the best. Do let us know if you still have any question.

          Regards,
          Team SAPYard.

  2. Hello
    Very interesting.

    As an expert in ABAP I do not appreciate this demo. Not due to a bad quality, I is an excellent demo.
    My concern is about SAP concepts: from the begining there was a will from SAP to be fulle independant of DATABASE. By the way an ABAP coding could to any database. Thanks a lot to OPENSQL.
    With stored procedures I have the feeling to come back 40 years ago: all stored procedures impact the consistency of the database, they have an impact on it. How to maintain them? How to make changes easily?

    I may recommend stored procedures when tables are out of SAP data dictionnary. When it is a work to be done by a schedule jobs. Not for a requirement asked by endusers on SAP data. What about authorizations?

    Interesting but the focus of this kind of need has to be more accurate.

    Best regards
    Yann SZWEC
    French SAP author in ABAP

    • Dear Yann – We are really honoured to see the comment from an expert like you. Thank you very much for stopping by and putting your thoughts.

      We completely agree with your points. Bottom Up Approach is not recommended.

      We just wanted to deliver the concept so that when we are in catch-22 situation where we need to consume SAP/Partner delivered standard procedures/tables, we know how to consume them using DB Procedure Proxy.

      Also some clients may by in SP2 ABAP 7.4 where they cannot use AMDP. In such rare situation (though I cannot imagine why would they be not able to upgrade above SP2), for the sake of theory, they would be compelled to make use for procedures.

      Thank you again for your insightful feedback.

      For the knowledge and benefit of our Team and Readers, can you please explain in some more details, why Stored Procedures should not be used in this modern world.

      With Regards,
      Team SAPYard.

Leave a Reply to SAP Yard Cancel reply

Your email address will not be published.


*