Example of a SAP HANA Stored Procedure

Prerequisite

The remote function adapter requires input data which is then processed by the SAP HANA Stored Procedure.

In this example, simple input data is used in the form of a model table, to show the connection and logic of the remote function adapter SAP HANA stored procedure type.

Create a model table.
Name it “RFA Input”, for example.
On the right-hand side of the screen, configure the model table by entering the following:
- Model Table Source: Environment
- Transport Data: Default (No)
Add a field.

Example

In this example, the field JB_PROD (characteristic, length = 50) has been added to show the relationship between input data and the remote function adapter.

Choose Maintain Data and add an entry.

Example

In this example, the added data is PRD01.

Product field (JB_PROD)
PRD01

Now the input has been defined and ready to be used.

Remote Function Adapter

The Remote Function Adapter function provides automated communication capabilities to other applications and systems so that they can be included in calculations and processes.

One of these is HANA STORED PROCEDURE, where an external SAP HANA stored procedure can be called by the function to process the input function in step 6. .

How to set up the remote function adapter

Create a remote function adapter.
Name it “RFA Function”, for example.
On the right-hand side of the screen, configure the header of the remote function adapter by making the following entries:
- Function Type: HANA STORED PROCEDURE
- Supress Initial Result: Default
- Result Model Table: Empty (by default)
- Authoring Schema: <Where your procedure is located>
- Stored Procedure: <The stored procedure created that can process the input data>
- Include Original Input Data: Default
- Result Handling: Default
On the right-hand side of the screen, configure the tabs of the remote function adapter.
- Input Function: <Enter the input function that the SAP HANA stored procedure needs to process>
  For the purposes of this example, it is the prerequisite model table “RFA Input” that was created earlier .
- The Rule tab has two sections:
  1. Template Section (Read Only)
    This is a generated procedure based on the input function that can be used as a template for the called SAP HANA stored procedure.
    
    The configurer must be aware of the fact that this template is a mandatory format, and that it must be closely checked against the called SAP HANA stored procedure
    Example
    Sample Code
    
    CREATE PROCEDURE "<SCHEMA>"."<HANAPROCEDURE>" (IN it_al "<DEFAULT SCHEMA>"."/NXI/TP1AL", IN it_input TABLE(JB_PROD NVARCHAR(50)), OUT ot_result TABLE(JB_PROD NVARCHAR(50), FS_PER_MSG_TEXT_ NVARCHAR(5000), FS_PER_FORMULA_ NVARCHAR(5000)), OUT ot_msg TABLE(MSGTY NVARCHAR(1), MSG_TEXT NVARCHAR(5000)) ) LANGUAGE SQLSCRIPT SQL SECURITY DEFINER AS BEGIN END;
  2. Statement Section (Read Only)
    This reflects the SQL statement(s) of the called SAP HANA stored procedure.
    
    The parameter section of the SAP HANA stored procedure must be completely filled using the respective template format based on the input function.
    
    In this example, it looks something like this:
    Sample Code
```
CREATE PROCEDURE "<SCHEMA>"."<HANAPROCEDURE>" 
(IN it_al "<DEFAULT SCHEMA>"."/NXI/TP1AL", 
IN it_input TABLE(JB_PROD NVARCHAR(50)),
OUT ot_result TABLE(JB_PROD NVARCHAR(50), FS_PER_MSG_TEXT_ NVARCHAR(5000), FS_PER_FORMULA_ NVARCHAR(5000)), 
OUT ot_msg TABLE(MSGTY NVARCHAR(1), MSG_TEXT NVARCHAR(5000))
) LANGUAGE SQLSCRIPT SQL SECURITY DEFINER AS
BEGIN 
ot_result = select 'ABC' as JB_PROD, 'MSG_TEXT' as FS_PER_MSG_TEXT_, 'MS_FORMULA' as FS_PER_FORMULA_ from dummy;
ot_msg = select  'I'  as MSGTY, 'SUCCESS' as MSG_TEXT from dummy;
END;
```
    Note
    
    Explanation
    - ot_result = This is the section where input processing will happen with the help of the SAP HANA stored procedure.
      
      In the above example, the procedure states that the value “ABC” is assigned to the field JB_PROD, assign “MSG_TEXT” to field FS_PERMSG_TEXT_, and add “MS_FORMULA” as a value for FS_PER_FORMULA_ field.
      Since the last two fields are technical fields, the scenario can focus on JB_PROD now getting a value “ABC”.
    - ot_msg = This is the section where run information can be manipulated and set by the SAP HANA stored procedure.
      
      In the above example, the procedure states that the value “I” or “Information” is assigned as MSGTY (msgtype), and have a word “SUCCESS” as MSG_TEXT.
    Additional Note: This is an actual example of a SAP HANA procedure that SAP Profitability and Performance Management can call in the remote function adapter (this is the same as with the Statement section in the Rule tab above).
  3. Check: Making entries on this tab is not mandatory, but if a check is required after processing, you can enter proper check conditions on this tab.
  4. Documentation: Making entries on this tab is not mandatory, but you can use it to document the scenario or provide documentation or instructions about the function being modeled.
Once you have completed the set-up, choose Activate.
Then choose Run.

Example

Here is the result of the remote function adapter function after calling a SAP HANA stored procedure to process the input function:

Here is the application log of the remote function adapter function after calling a SAP HANA stored procedure to process the input function: