ABAP Managed Database Procedures – Introduction

What are AMDPs…

ABAP Managed Database Procedures are a new feature in AS ABAP allowing developers to write database procedures directly in ABAP. You can think of a Database Procedure as a function stored and executed in the database. The implementation language varies from one database system to another. In SAP HANA it is SQL Script. Using AMDP allows developers to create and execute those database procedures in the ABAP environment using ABAP methods and ABAP data types. This video will show you more details…

AMDPs are introduced to easily enable you to get the best out of ABAP for HANA following the “code push-down” paradigm first introduced with SAP NetWeaver AS ABAP 7.4 last year. More information on this paradigm can be found here: ABAP for HANA and “Code Push-Down”.

… and why should I use them ?

As you can imagine (and hopefully have already experienced) calculations executed while retrieving the data from the database can significant reduce the run-time of an ABAP program. Especially when the calculations can be palatalized.

Using SQL and all of its possibilities is one example of this. But this has limitations. A SQL statement always has one result set and the calculation must be done in one single step. If you have complex calculations which you want to execute on the database you need other possibilities. In those cases and many more, database procedures come into the game. And the vehicle of choice to implement database procedures as an ABAP geek are AMDPs.

Sounds great – How do I create an AMDP ?

Creating an AMDP is as easy as it can get in ABAP. You can simply implement SQLScript in an ABAP method:

METHOD <meth> BY DATABASE PROCEDURE
    FOR <db>
    LANGUAGE <db_lang>
    [OPTIONS <db_options>]
    [USING <db_entities>].

< Insert SQLScript code here >

ENDMETHOD.

Also important to know is that every class containing an AMDP must contain the interface IF_AMDP_MARKER_HDB. But that’s all – You don’t need anything else. For more details on the syntax and the different options just take a look at our ABAP Documentation.

Enough talking – Show us an example !

Here we go – a very very simple example but it should give you an idea. A real life example is of course much more complex and normally has more in- and/or output parameters.

In this example we have created a database procedure for SAP HANA (FOR HDB) using SQLScript (LANGUAGE SQLSCRIPT) which simply selects the company name for our top business partners. The number of business partners to be selected is passed as an input parameter (iv_number) which is used in the SQL Script statement.

AMDPs are available with SAP NetWeaver AS ABAP 7.4 SP5. Currently AMDPs are only available with SAP HANA as primary persistence and can only be written in SQLScript.

To create an AMDP you need to use at least ABAP Development Tools in Eclipse 2.19 ( see Get ABAP Development Tools 2.19 and find out what’s new). By the way it is of course still possible to open an AMDP with se80 /se24, but you won’t be able to edit it,

All set and ready but are there any limitations ?

Yes, there are some limitations, but only based on the parameters:

The parameters have to meet the following prerequisites:

• Exporting, importing and changing parameters are allowed
• Methods with returning parameters cannot be implemented as AMDPs
• Method parameters have to be tables or scalar types
• Method parameters have to be passed as values

Come on – I want to know more…Call Anubhav www.anubhavtrainings.com

As already mentioned we will continue with more technical deep dives (e.g. debugging an AMDP) and a more complex example to show you more benefits of AMDPs.

I hope you enjoyed this introduction and stay tuned.

Keep Learning :)