When to Use AMDP? Choosing Between ABAP SQL, CDS and AMDP

HANA brought the idea of "code pushdown" into the ABAP world: doing calculations next to the data, in the database, rather than on the application server. AMDP (ABAP Managed Database Procedures) is the most direct tool for this. A procedure written in SQLScript lives as a method of an ABAP class, is transported like ABAP, and runs on HANA when called.

That power turned AMDP into the default tool in some projects. Yet SAP's ABAP documentation and the official cheat sheet repository say the opposite: if the same task can be done with ABAP SQL, AMDP is not recommended. This article covers the decision order, where AMDP is really needed, and the responsibilities you take on when you use it. Syntax is based on SAP's ABAP Keyword Documentation and official sample code; details can vary by release.

The "AMDP Is Faster" Myth

ABAP SQL runs in the database too. A SELECT ... GROUP BY has HANA do the aggregation; only the result reaches the application server. Moving a simple query to AMDP doesn't make it faster by itself; most of the time it runs the same SQL in a place that is harder to maintain.

AMDP's performance advantage appears when it keeps the intermediate results of a multi-step calculation from travelling to the application server: when the result of one query feeds a second, which feeds a third, and those steps cannot be expressed in a single ABAP SQL statement (or a CDS view). Measure first to find where the performance problem really is; the ST05 and SAT steps in the ABAP performance analysis article come before any AMDP decision.

Decision Order: ABAP SQL, CDS, AMDP

Where AMDP Is Really Needed

• Procedural flow: Calculations that branch on an intermediate result, proceed in several steps over table variables, and where each step depends on the previous one's result.
• Database-specific capabilities: Functions or HANA libraries available in SQLScript with no counterpart in ABAP SQL and CDS. Check your release's ABAP SQL and CDS documentation first; a counterpart may have been added.
• A data source that doesn't fit the CDS model: Data whose calculation takes several steps but whose result should be usable like a CDS entity. The right tool for this is a CDS table function implemented by an AMDP function.

What they have in common: in each case AMDP is an exception, while the rest of the application stands on ABAP SQL and CDS.

AMDP Class and Procedure

To see the syntax, let's start with a simple example: a procedure that totals approved expense reports by cost center for one company code. (A task this simple does not need AMDP; I'll show that below.)

CLASS zcl_exp_amdp DEFINITION PUBLIC FINAL CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_cost_center_total,
             company_code TYPE zexp_report-company_code,
             cost_center  TYPE zexp_report-cost_center,
             report_count TYPE i,
             total_amount TYPE zexp_report-total_amount,
             currency     TYPE zexp_report-currency,
           END OF ty_cost_center_total,
           tt_cost_center_total TYPE STANDARD TABLE OF ty_cost_center_total WITH EMPTY KEY.

    METHODS get_cost_center_totals
      AMDP OPTIONS READ-ONLY CDS SESSION CLIENT DEPENDENT
      IMPORTING VALUE(iv_company_code) TYPE zexp_report-company_code
      EXPORTING VALUE(et_totals)       TYPE tt_cost_center_total
      RAISING   cx_amdp_execution_error.

ENDCLASS.

CLASS zcl_exp_amdp IMPLEMENTATION.

  METHOD get_cost_center_totals
    BY DATABASE PROCEDURE FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING zi_expensereport.

    approved = SELECT CompanyCode, CostCenter, TotalAmount, Currency
                 FROM zi_expensereport
                 WHERE CompanyCode = :iv_company_code
                   AND Status = 'A';

    et_totals = SELECT CompanyCode      AS company_code,
                       CostCenter       AS cost_center,
                       COUNT(*)         AS report_count,
                       SUM(TotalAmount) AS total_amount,
                       Currency         AS currency
                  FROM :approved
                  GROUP BY CompanyCode, CostCenter, Currency;

  ENDMETHOD.

ENDCLASS.

Rules from the documentation:

• Class: The IF_AMDP_MARKER_HDB interface is mandatory. The class may contain regular methods alongside AMDP methods. AMDP classes can only be edited in ADT.
• Procedure parameters: All are passed by value (VALUE(...)); pass by reference is not allowed. Types cannot be generic; use elementary types or table types with a structured row type whose components are elementary. RETURNING is not allowed, and only input parameters can be optional.
• Implementation: BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT marks the method as a procedure. The USING list names the ABAP objects the SQLScript accesses (tables, CDS views, other AMDP methods).
• The body is not ABAP. Between METHOD and ENDMETHOD is SQLScript; there are no ABAP variables, and parameters are used as :iv_company_code.

Calling it from ABAP is a normal method call:

TRY.
    NEW zcl_exp_amdp( )->get_cost_center_totals(
      EXPORTING iv_company_code = '1000'
      IMPORTING et_totals       = DATA(totals) ).
  CATCH cx_amdp_execution_error INTO DATA(amdp_error).
    " write amdp_error->get_text( ) to a log
ENDTRY.

The same task in ABAP SQL

SELECT CompanyCode, CostCenter,
       COUNT(*)           AS ReportCount,
       SUM( TotalAmount ) AS TotalAmount,
       Currency
  FROM zi_expensereport
  WHERE CompanyCode = @company_code
    AND Status = 'A'
  GROUP BY CompanyCode, CostCenter, Currency
  INTO TABLE @DATA(totals).

This single statement also aggregates on HANA. On top of that, it handles the client automatically, applies the DCL role of zi_expensereport, is easy to test with ABAP Unit, and is readable by anyone who knows ABAP. The following sections show that the AMDP version carries none of these four properties by itself. In this example, the right choice is clearly ABAP SQL.

CDS Table Functions

If you want to use an AMDP result as a data source in ABAP SQL and other CDS views, the right tool is a CDS table function. A table function is a CDS entity whose body is an AMDP function.

@EndUserText.label: 'Cost center totals'
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.type: #CLIENT_DEPENDENT
@ClientHandling.algorithm: #SESSION_VARIABLE
define table function ZTF_ExpenseCostCenterTotal
  with parameters
    p_company_code : abap.char(4)
  returns
  {
    client      : abap.clnt;
    CompanyCode : abap.char(4);
    CostCenter  : abap.char(10);
    ReportCount : abap.int4;
    TotalAmount : abap.dec(15,2);
    Currency    : abap.cuky;
  }
  implemented by method zcl_exp_amdp=>cost_center_totals;

The implementing method is declared in a special form: in the public section, static and without a parameter interface. Parameters and the return type are derived from the table function:

CLASS-METHODS cost_center_totals FOR TABLE FUNCTION ztf_expensecostcentertotal.

METHOD cost_center_totals
  BY DATABASE FUNCTION FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING zi_expensereport.

  RETURN SELECT mandt            AS client,
                CompanyCode      AS CompanyCode,
                CostCenter       AS CostCenter,
                COUNT(*)         AS ReportCount,
                SUM(TotalAmount) AS TotalAmount,
                Currency         AS Currency
           FROM zi_expensereport
           WHERE CompanyCode = :p_company_code
             AND Status = 'A'
           GROUP BY mandt, CompanyCode, CostCenter, Currency;

ENDMETHOD.

The table function can now be read in ABAP SQL:

SELECT * FROM ztf_expensecostcentertotal( p_company_code = '1000' )
  INTO TABLE @DATA(totals).

In a client-dependent table function, the first return element is the client field, and the column names returned by the SQLScript must match the names in the returns list.

Client Safety

This is AMDP's most easily overlooked responsibility. ABAP SQL handles the client implicitly: a SELECT only reads the data of the session's client. AMDP works like Native SQL and has no implicit client handling. If you read a table in SQLScript without a filter, you read the data of all clients.

That is why AMDP methods in ABAP Cloud must be client-safe:

• A method accessing client-dependent objects is declared with AMDP OPTIONS ... CDS SESSION CLIENT DEPENDENT; a method accessing only client-independent objects with CLIENT INDEPENDENT.
• Everything in the USING list must be client-safe: CDS views filtered by the session client, client-dependent tables (with this addition, the framework reads them through a view that filters by the session client), client-safe table functions, and other AMDP methods with the same addition.
• Client-dependent table functions must carry @ClientHandling.type: #CLIENT_DEPENDENT and @ClientHandling.algorithm: #SESSION_VARIABLE.
• SQLScript may not access database objects that are not in the USING list or not managed by ABAP, and may not call HANA functions that take a client ID parameter.

In ABAP Cloud, only reads are allowed (READ-ONLY is mandatory), only SQLScript can be used, and the data sources accessed must be released for ABAP Cloud. In classic ABAP (Standard ABAP) these rules are looser, but the responsibility doesn't go away: an AMDP that doesn't handle the client explicitly is a bug that goes unnoticed in the test system and returns another client's data in production.

AMDP and Authorization

CDS access control (DCL) only applies to CDS entities read with ABAP SQL. Roles are not evaluated for tables and views read inside SQLScript. The procedure above reads zi_expensereport; even if that view has a company code role, the role is not applied inside the AMDP, and the procedure can return totals for company codes the user is not authorized for.

There are two solutions:

• Write a role for the table function: When a table function is read with ABAP SQL, its own DCL role is applied. The example above has @AccessControl.authorizationCheck: #CHECK; once a pfcg_auth role on CompanyCode is written, the result is filtered by the user's authorizations.
• Check authorization explicitly around the procedure: Check the company code in ABAP with AUTHORITY-CHECK before the call. This requires remembering the check at every call site.

For writing roles and the other places where DCL does not apply, see the CDS access control article.

Errors, Debugging and Testing

• Errors: Errors raised while the SQLScript runs arrive in ABAP as AMDP exceptions such as CX_AMDP_EXECUTION_ERROR. The procedure should declare the exception with RAISING, and the caller should catch it. Table functions, on the other hand, cannot declare RAISING.
• Debugging: ADT offers an AMDP debugger that lets you set breakpoints in the SQLScript body and inspect table variables.
• Testing: AMDP methods can be called from ABAP Unit. My recommendation: write a test that compares the AMDP result on known data with a simple ABAP SQL version of the same calculation. If the two differ, the bug is either in the AMDP or in your assumption. For the general setup of ABAP Unit, see the ABAP Unit testing article.
• Client and authorization testing: In a test system with several clients, check the result in each client, and with a user with restricted authorizations too. Most AMDP bugs show up right there.

The Maintenance Cost of AMDP

• HANA only: AMDP currently supports only SAP HANA database procedures and functions. That is not a problem on S/4HANA, but the code is tied to the database.
• Two languages: If nobody on the team knows SQLScript, AMDP becomes a black box only its author can maintain.
• No implicit guarantees: Client, authorization and error handling have to be set up deliberately in every method.
• Tool dependency: AMDP classes can only be edited in ADT.
• ABAP Cloud restrictions: Read-only, released sources only, client safety mandatory.

None of these costs make AMDP bad; they make it a justified exception rather than a default. In code review, the answer to "why isn't this ABAP SQL or CDS?" for every AMDP should sit as a comment at the top of the class.

Common Mistakes

Conclusion

AMDP is one of the sharpest tools an ABAP developer has, and sharp tools need care. The decision order is simple: ABAP SQL first, CDS for reusable models, and AMDP, preferably as a CDS table function, for multi-step or database-specific work neither of them can solve. When you move to AMDP, set up client safety, authorization and testing deliberately; these were the things ABAP SQL gave you for free.

For data modeling with CDS and the service side, see the OData V4 and CDS article; for the development scope I offer, see the SAP development services page.