ABAP AMDP: Database Procedures for SAP HANA

Prerequisites

SAP HANA as database
ABAP 7.40 SP05 or higher
Class must implement interface IF_AMDP_MARKER_HDB

Basic Structure

CLASS zcl_amdp_example DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_result,
             field1 TYPE string,
             field2 TYPE i,
           END OF ty_result,
           ty_results TYPE STANDARD TABLE OF ty_result WITH EMPTY KEY.

    CLASS-METHODS: get_data
      IMPORTING VALUE(iv_param) TYPE string
      EXPORTING VALUE(et_result) TYPE ty_results.
ENDCLASS.

CLASS zcl_amdp_example IMPLEMENTATION.
  METHOD get_data BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING ztable.

    et_result = SELECT field1, field2
                FROM ztable
                WHERE field1 = :iv_param;
  ENDMETHOD.
ENDCLASS.

Examples

1. Simple AMDP Procedure

CLASS zcl_customer_amdp DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_customer,
             kunnr TYPE kunnr,
             name1 TYPE name1_gp,
             ort01 TYPE ort01_gp,
             land1 TYPE land1_gp,
           END OF ty_customer,
           ty_customers TYPE STANDARD TABLE OF ty_customer WITH EMPTY KEY.

    CLASS-METHODS: get_customers_by_country
      IMPORTING VALUE(iv_country) TYPE land1_gp
      EXPORTING VALUE(et_customers) TYPE ty_customers.
ENDCLASS.

CLASS zcl_customer_amdp IMPLEMENTATION.
  METHOD get_customers_by_country BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING kna1.

    et_customers = SELECT kunnr, name1, ort01, land1
                   FROM kna1
                   WHERE land1 = :iv_country;
  ENDMETHOD.
ENDCLASS.


" Call
DATA: lt_customers TYPE zcl_customer_amdp=>ty_customers.

zcl_customer_amdp=>get_customers_by_country(
  EXPORTING iv_country = 'DE'
  IMPORTING et_customers = lt_customers
).

LOOP AT lt_customers INTO DATA(ls_cust).
  WRITE: / ls_cust-kunnr, ls_cust-name1, ls_cust-ort01.
ENDLOOP.

2. AMDP with Multiple Tables

CLASS zcl_order_amdp DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_order_detail,
             vbeln     TYPE vbeln_va,
             posnr     TYPE posnr_va,
             matnr     TYPE matnr,
             kwmeng    TYPE kwmeng,
             netwr     TYPE netwr,
             kunnr     TYPE kunnr,
             name1     TYPE name1_gp,
           END OF ty_order_detail,
           ty_order_details TYPE STANDARD TABLE OF ty_order_detail WITH EMPTY KEY.

    CLASS-METHODS: get_order_details
      IMPORTING VALUE(iv_vbeln) TYPE vbeln_va
      EXPORTING VALUE(et_details) TYPE ty_order_details.
ENDCLASS.

CLASS zcl_order_amdp IMPLEMENTATION.
  METHOD get_order_details BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING vbak vbap kna1.

    et_details = SELECT p.vbeln,
                        p.posnr,
                        p.matnr,
                        p.kwmeng,
                        p.netwr,
                        h.kunnr,
                        c.name1
                 FROM vbap AS p
                 INNER JOIN vbak AS h ON p.vbeln = h.vbeln
                 INNER JOIN kna1 AS c ON h.kunnr = c.kunnr
                 WHERE p.vbeln = :iv_vbeln;
  ENDMETHOD.
ENDCLASS.

3. AMDP with Variables and Logic

CLASS zcl_sales_amdp DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_sales_summary,
             kunnr       TYPE kunnr,
             name1       TYPE name1_gp,
             order_count TYPE i,
             total_value TYPE netwr,
           END OF ty_sales_summary,
           ty_sales_summaries TYPE STANDARD TABLE OF ty_sales_summary WITH EMPTY KEY.

    CLASS-METHODS: get_sales_summary
      IMPORTING VALUE(iv_year) TYPE gjahr
      EXPORTING VALUE(et_summary) TYPE ty_sales_summaries.
ENDCLASS.

CLASS zcl_sales_amdp IMPLEMENTATION.
  METHOD get_sales_summary BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING vbak kna1.

    -- Local variable for date range
    DECLARE lv_from_date DATE;
    DECLARE lv_to_date DATE;

    lv_from_date = CONCAT(:iv_year, '0101');
    lv_to_date = CONCAT(:iv_year, '1231');

    -- Aggregated query
    et_summary = SELECT k.kunnr,
                        k.name1,
                        COUNT(*) AS order_count,
                        SUM(v.netwr) AS total_value
                 FROM vbak AS v
                 INNER JOIN kna1 AS k ON v.kunnr = k.kunnr
                 WHERE v.erdat BETWEEN :lv_from_date AND :lv_to_date
                 GROUP BY k.kunnr, k.name1
                 ORDER BY total_value DESC;
  ENDMETHOD.
ENDCLASS.

4. AMDP with IF/CASE Logic

METHOD calculate_discount BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zcustomers.

  et_result = SELECT kunnr,
                     name1,
                     total_sales,
                     CASE
                       WHEN total_sales >= 100000 THEN 15
                       WHEN total_sales >= 50000 THEN 10
                       WHEN total_sales >= 10000 THEN 5
                       ELSE 0
                     END AS discount_percent
              FROM zcustomers;
ENDMETHOD.

5. AMDP with Loops

METHOD process_data BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING ztable.

  DECLARE lv_counter INTEGER;
  DECLARE lv_max INTEGER;

  -- Load data
  lt_data = SELECT * FROM ztable;

  -- Initialize counter
  lv_counter = 1;
  SELECT COUNT(*) INTO lv_max FROM :lt_data;

  -- Loop
  WHILE lv_counter <= lv_max DO
    -- Processing
    lv_counter = lv_counter + 1;
  END WHILE;

  et_result = SELECT * FROM :lt_data;
ENDMETHOD.

6. AMDP with Temporary Tables

METHOD complex_calculation BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING vbak vbap.

  -- First temporary table
  lt_headers = SELECT vbeln, kunnr, erdat, netwr
               FROM vbak
               WHERE erdat >= ADD_DAYS(CURRENT_DATE, -365);

  -- Second temporary table based on first
  lt_items = SELECT p.*
             FROM vbap AS p
             INNER JOIN :lt_headers AS h ON p.vbeln = h.vbeln;

  -- Aggregation
  lt_summary = SELECT h.kunnr,
                      COUNT(DISTINCT h.vbeln) AS order_count,
                      SUM(i.netwr) AS item_total
               FROM :lt_headers AS h
               INNER JOIN :lt_items AS i ON h.vbeln = i.vbeln
               GROUP BY h.kunnr;

  -- Result
  et_result = SELECT * FROM :lt_summary;
ENDMETHOD.

7. CDS Table Function

" CDS View Definition
@EndUserText.label: 'Sales by Customer (Table Function)'
define table function ZTF_SALES_BY_CUSTOMER
  with parameters
    @Environment.systemField: #SYSTEM_DATE
    p_date : abap.dats
  returns {
    key kunnr : abap.char(10);
    name1     : abap.char(35);
    total     : abap.dec(15,2);
  }
  implemented by method zcl_tf_sales=>get_sales;


" ABAP Implementation
CLASS zcl_tf_sales DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    CLASS-METHODS: get_sales
      FOR TABLE FUNCTION ztf_sales_by_customer.
ENDCLASS.

CLASS zcl_tf_sales IMPLEMENTATION.
  METHOD get_sales BY DATABASE FUNCTION
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING vbak kna1.

    RETURN SELECT k.kunnr,
                  k.name1,
                  SUM(v.netwr) AS total
           FROM vbak AS v
           INNER JOIN kna1 AS k ON v.kunnr = k.kunnr
           WHERE v.erdat <= :p_date
           GROUP BY k.kunnr, k.name1;
  ENDMETHOD.
ENDCLASS.


" Usage in ABAP
SELECT * FROM ztf_sales_by_customer( p_date = @sy-datum )
  INTO TABLE @DATA(lt_sales).

8. AMDP with Error Handling

METHOD process_with_error BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING ztable.

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Error handling
    et_errors = SELECT ::SQL_ERROR_CODE AS error_code,
                       ::SQL_ERROR_MESSAGE AS error_message
                FROM DUMMY;
  END;

  -- Normal processing
  et_result = SELECT * FROM ztable;
ENDMETHOD.

9. AMDP USING Other AMDP

CLASS zcl_amdp_chain DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES: if_amdp_marker_hdb.

    CLASS-METHODS: helper_method
      IMPORTING VALUE(iv_input) TYPE string
      EXPORTING VALUE(et_result) TYPE ty_results.

    CLASS-METHODS: main_method
      EXPORTING VALUE(et_final) TYPE ty_finals.
ENDCLASS.

CLASS zcl_amdp_chain IMPLEMENTATION.
  METHOD helper_method BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING ztable.

    et_result = SELECT * FROM ztable WHERE field = :iv_input;
  ENDMETHOD.

  METHOD main_method BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING zcl_amdp_chain=>helper_method.

    -- Call other AMDP
    CALL "ZCL_AMDP_CHAIN=>HELPER_METHOD"(
      iv_input => 'VALUE',
      et_result => lt_temp
    );

    et_final = SELECT * FROM :lt_temp;
  ENDMETHOD.
ENDCLASS.

10. Performance: AMDP vs. Open SQL

" === Open SQL (Standard) ===
" Data transferred from DB server to app server
SELECT * FROM vbak INTO TABLE @DATA(lt_orders).

LOOP AT lt_orders INTO DATA(ls_order).
  " Processing on app server
  IF ls_order-netwr > 10000.
    " ...
  ENDIF.
ENDLOOP.


" === AMDP (optimized) ===
" Processing directly on database
zcl_order_amdp=>get_high_value_orders(
  EXPORTING iv_min_value = 10000
  IMPORTING et_orders = lt_high_value_orders
).
" Only relevant data is transferred

11. AMDP with Window Functions

METHOD get_ranked_sales BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING vbak.

  et_result = SELECT kunnr,
                     vbeln,
                     netwr,
                     ROW_NUMBER() OVER (
                       PARTITION BY kunnr
                       ORDER BY netwr DESC
                     ) AS rank_per_customer,
                     SUM(netwr) OVER (
                       PARTITION BY kunnr
                     ) AS total_per_customer
              FROM vbak;
ENDMETHOD.

12. Practical Example: Hierarchy Resolution

METHOD resolve_hierarchy BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zorg_units.

  -- Recursive hierarchy resolution with HANA
  et_hierarchy = SELECT node_id,
                        parent_id,
                        name,
                        HIERARCHY_LEVEL AS level
                 FROM HIERARCHY (
                   SOURCE ( SELECT * FROM zorg_units )
                   START WHERE parent_id IS NULL
                   NO ORPHANS
                 );
ENDMETHOD.

SQLScript Basics

-- Variables
DECLARE lv_var INTEGER;
DECLARE lv_text NVARCHAR(100);

-- Assignment
lv_var = 42;
lv_text = 'Hello';

-- Table variable
lt_data = SELECT * FROM table;

-- IF condition
IF :lv_var > 10 THEN
  -- ...
ELSEIF :lv_var > 5 THEN
  -- ...
ELSE
  -- ...
END IF;

-- WHILE loop
WHILE :lv_counter < 100 DO
  lv_counter = lv_counter + 1;
END WHILE;

-- FOR loop
FOR i IN 1..10 DO
  -- ...
END FOR;

-- Cursor
FOR row AS cursor_name DO
  -- row.field
END FOR;

Important Notes / Best Practice

AMDP only for SAP HANA – not portable to other databases.
READ-ONLY for read operations (default, recommended).
USING declares all used database objects and AMDPs.
Use Table Functions for usage in CDS Views.
Process large data volumes on DB rather than transfer.
IF_AMDP_MARKER_HDB must be implemented.
Test in HANA Studio or SQL Console.
Debugging possible via HANA debugger (from 7.50).
Avoid too many loops – think set-based.
Combine with CDS Views for optimal data model.