Implement HANA Procedure in ABAP-Simple ABAP Scenario On SAP HANA


The procedure looks like:-



Step-by-Step Procedure


Step -1 Create and implement a SAP HANA Procedure with Input and Output Parameters

Start the SAP HANA Studio and go the Modeler perspective

a. Start the SAP HANA Studio and open the Modeler perspective.

Select menu entry Window > Open perspective > Others…, choose  from the appearing dialog box and press OK.


The procedure looks like:-


Step-by-Step Procedure
Step -1 Create and implement a SAP HANA Procedure with Input and Output
Parameters

Start the SAP HANA Studio and go the Modeler perspective

a. Start the SAP HANA Studio and open the Modeler perspective.
Select menu entry Window > Open perspective > Others…, choose  from the appearing dialog box and press OK.


b. Optional: 

First create a new package (e.g. demo) for your demo objects under package systemlocal.private which is located under the Content folder.

Now create a new database procedure by right-clicking on package system-local.private.demo  and selecting menu path New -> Procedure …

Enter  a Name  (e.g. ZDP_OIA_TOPANDFLOP )  and  description (“OIAdemo:Top and flop Customers”)

Select the required Schema from the Default Schema dropdown list, for unqualified access in SQL.

Select ‘Invoker’s Rights’ from the Run With dropdown list.

Press on Finish.



c. Create the output parameter ET_TOP 

Go to the Output Pane, right-click on the root folder Output Parameters and select New… from the context menu. Now enter the name “ET_TOP” and add two columns (by clicking on the icon) with following values and confirm: 

       Column 1
                 Name        : COMPANY_NAME
                 Data Type : NVARCHAR
                 Length       : 80 
       Column 2
                    Name         : GROSS_AMOUNT
                    Data Type  : DECIMAL
                     Length        : 15 
                     Scale          : 2 



d. Create the output parameter ET_FLOP  

Just repeat the steps performed previously for ET_TOP. 
e. Create the input parameter IV_NUMBER 
Go to the Input Pane, right-click on the root folder Input Parameters and select New Scalar Parameter from the context menu. Enter following values and confirm 

Name        :  IV_NUMBER
Data Type : INTEGER



f. Implement the procedure.

  Copy & paste the SQLScript below into the Procedure Script View Editor


g. Save and activate the procedure



Step 2: Create a Database Procedure Proxy in the ABAP Dictionary to expose the HANA Procedure
We will now expose the created HANA procedure DP_OIA_TOPANDFLOP as Database Procedure Proxy in  the application server using the new ABAP dictionary features.

a. First switch to the ABAP perspective



b. Select the package of your choice (e.g. your $TMP package under Favorite Packages), right-click on it and select New -> Other ABAP Repository Object from the context menu.

Filter for “Database Procedure Proxy", select it and press on Next.



c. Enter a name (e.g. ZDPP_OIA_TOPANDFLOP) and a description (e.g. “Database Procedure Proxy for ZDP_OIA_TOPANDFLOP”) for the new Database Procedure Proxy. 

Enter the name of the SAP HANA Procedure previously created in Step 1 (e.g. systemlocal.private.demo.ZDP_OIA_TOPANDFLOP).
A name is proposed for the Parameter Type Interface which contains the data type definition of the input and output parameters defined in the HANA Procedures. The interface will automatically be created together with the database procedure proxy.

Now press on Next and then on Finish on the next dialog step.



d. Look at the created objects: 

Database Procedure Proxy and its interface. Activate both of them (  or Ctrl+Shift+F3).  

Step 3: Create and implement an ABAP Report consuming Procedure

We will now call the database procedure proxy from an ABAP program. We will implement a simple report which will just output the result with a WRITE statement.

The following steps are performed in the ABAP perspective. 

a. Create a new ABAP Program in the package of your choice by just right-clicking on it and selecting “New -> ABAP Program" from the context menu.
Enter a name (e.g. ZR_OIA_TOPANDFLOP) and a description (e.g. “Display Top and Flop Customers”).

Press on Next and then on Finish on the dialog step.


b. Implement the report. 

Copy & paste the source code below into the ABAP Editor.


REPORT ZR_OIA_TOPANDFLOP.

* input parameter: how many tops/flops should be displayed
PARAMETER pnumber TYPE i DEFAULT 10.

* define parameters using the created DPP interface ZIF_ZDPP_OIA_TOPANDFLOP
DATA: lv_number TYPE zif_zdpp_oia_topandflop=>iv_number,
             lt_top TYPE STANDARD TABLE OF zif_zdpp_oia_topandflop=>et_top,
             lt_flop TYPE STANDARD TABLE OF zif_zdpp_oia_topandflop=>et_flop.

* set the value of the procedure input parameter
lv_number = pnumber.

* call the created database procedure proxy
CALL DATABASE PROCEDURE zdpp_oia_topandflop
    EXPORTING 
           iv_number = lv_number
    IMPORTING 
           et_top = lt_top
           et_flop = lt_flop .

* display the returned itab with TOP customers
WRITE: / 'Tops:' COLOR COL_POSITIVE.

LOOP AT lt_top ASSIGNING FIELD-SYMBOL(<f>).
 WRITE:/ <f>-company_name , <f>-gross_amount .
ENDLOOP.

* display the returned itab with FLOP customers
ULINE. WRITE: 'Flops:' COLOR COL_NEGATIVE.

LOOP AT lt_flop ASSIGNING FIELD-SYMBOL(<g>).
 WRITE:/ <g>-company_name , <g>-gross_amount .
ENDLOOP.


c. Save and Activate  the report 

d. Execute the report by pressing F8


The high level architecture for this example is