AMDP Part 2: Create Your First AMDP Method

📌 KEY POINTS
✅ Flow: class with if_amdp_marker_hdb → method signature → BY DATABASE PROCEDURE → USING → activate → call from ABAP.
✅ AMDP parameters must be passed by VALUE; EXPORTING tables are filled by assignment in SQLScript.
✅ Use :name to read a parameter inside SQLScript.
⚠️ Always validate the result against an ABAP SQL version with the same input.

▶ SAP AMDP TUTORIAL: CREATE YOUR FIRST AMDP METHOD WITH SQLSCRIPT

━━━━━━━━━━━━━━━━━━━━
▶ WHAT YOU WILL BUILD

Part 1 explained when AMDP is justified.

Now you will create one.

We will build an AMDP method that receives a company code and returns customer sales totals from an illustrative table.

The goal is not to show clever SQLScript. The goal is to understand the full development flow:

ABAP class
→ AMDP marker interface
→ AMDP method signature
→ SQLScript implementation
→ USING dependency list
→ activation
→ ABAP caller
→ result validation

━━━━━━━━━━━━━━━━━━━━
▶ PREREQUISITES / PREVIOUS PART

You need:

• ADT in Eclipse;
• an ABAP system on SAP HANA with AMDP support;
• basic ABAP Objects knowledge;
• basic SQL understanding.

Use AMDP only after you have a real reason for database pushdown.

━━━━━━━━━━━━━━━━━━━━
▶ 1. CREATE THE ABAP CLASS

Create a global class in ADT.

For example:

┏━━ CODE ━━━━━━━━━━━━
┃ CLASS zcl_sales_amdp DEFINITION
┃ PUBLIC
┃ FINAL
┃ CREATE PUBLIC.
┃
┃ PUBLIC SECTION.
┃ INTERFACES if_amdp_marker_hdb.
┃
┃ TYPES:
┃ BEGIN OF ty_result,
┃ customer TYPE kunnr,
┃ amount TYPE p LENGTH 16 DECIMALS 2,
┃ END OF ty_result,
┃ tt_result TYPE STANDARD TABLE OF ty_result WITH EMPTY KEY.
┃
┃ CLASS-METHODS get_sales_totals
┃ IMPORTING
┃ VALUE(iv_company_code) TYPE bukrs
┃ EXPORTING
┃ VALUE(et_result) TYPE tt_result.
┃ ENDCLASS.
┗━━━━━━━━━━━━━━━━━━━

The important line is:

┏━━ CODE ━━━━━━━━━━━━
┃ INTERFACES if_amdp_marker_hdb.
┗━━━━━━━━━━━━━━━━━━━

That marks the class as an AMDP container for SAP HANA.

━━━━━━━━━━━━━━━━━━━━
▶ 2. IMPLEMENT THE METHOD WITH SQLSCRIPT

The implementation can look like this:

┏━━ CODE ━━━━━━━━━━━━
┃ CLASS zcl_sales_amdp IMPLEMENTATION.
┃
┃ METHOD get_sales_totals
┃ BY DATABASE PROCEDURE
┃ FOR HDB
┃ LANGUAGE SQLSCRIPT
┃ OPTIONS READ-ONLY
┃ USING zsales_item.
┃
┃ et_result =
┃ SELECT customer,
┃ SUM( net_amount ) AS amount
┃ FROM zsales_item
┃ WHERE company_code = :iv_company_code
┃ GROUP BY customer;
┃
┃ ENDMETHOD.
┃ ENDCLASS.
┗━━━━━━━━━━━━━━━━━━━

The method is still part of an ABAP class, but the method body is SQLScript executed on HANA.

━━━━━━━━━━━━━━━━━━━━
▶ 3. UNDERSTAND EACH AMDP CLAUSE

━━━━━━━━━━━━━━━━━━━━
▶ BY DATABASE PROCEDURE

The method is implemented as a database procedure.

━━━━━━━━━━━━━━━━━━━━
▶ FOR HDB

The implementation targets SAP HANA.

━━━━━━━━━━━━━━━━━━━━
▶ LANGUAGE SQLSCRIPT

The method body uses SQLScript rather than ABAP syntax.

━━━━━━━━━━━━━━━━━━━━
▶ OPTIONS READ-ONLY

The example is read-only.

Use the option that matches the supported design and requirement.

━━━━━━━━━━━━━━━━━━━━
▶ USING

The USING list declares database objects referenced by the AMDP.

In this example:

┏━━ CODE ━━━━━━━━━━━━
┃ USING zsales_item.
┗━━━━━━━━━━━━━━━━━━━

This dependency declaration matters for activation and lifecycle handling.

━━━━━━━━━━━━━━━━━━━━
▶ 4. HOST VARIABLES USE A COLON

Inside SQLScript, ABAP method parameters are referenced as host variables.

Example:

┏━━ CODE ━━━━━━━━━━━━
┃ WHERE company_code = :iv_company_code
┗━━━━━━━━━━━━━━━━━━━

The colon is significant.

This is one of the first syntax differences that ABAP developers notice.

━━━━━━━━━━━━━━━━━━━━
▶ 5. RETURN A SET, NOT A ROW-BY-ROW LOOP

The strength of HANA pushdown is set processing.

This is good:

┏━━ CODE ━━━━━━━━━━━━
┃ SELECT customer,
┃ SUM( net_amount ) AS amount
┃ FROM zsales_item
┃ WHERE company_code = :iv_company_code
┃ GROUP BY customer;
┗━━━━━━━━━━━━━━━━━━━

This is usually a poor reason to use AMDP:

• read all rows;
• loop row by row in SQLScript;
• perform simple calculations that Open SQL could already do cleanly.

Push down work that the database can perform efficiently as a set.

━━━━━━━━━━━━━━━━━━━━
▶ 6. CALL THE AMDP FROM ABAP

From ABAP, call the method like a normal static class method.

┏━━ CODE ━━━━━━━━━━━━
┃ DATA lt_result TYPE zcl_sales_amdp=>tt_result.
┃
┃ zcl_sales_amdp=>get_sales_totals(
┃ EXPORTING
┃ iv_company_code = '1000'
┃ IMPORTING
┃ et_result = lt_result
┃ ).
┗━━━━━━━━━━━━━━━━━━━

The caller does not execute native SQL directly.

The AMDP class is the managed ABAP repository object.

━━━━━━━━━━━━━━━━━━━━
▶ 7. ACTIVATE AND VALIDATE

When the class activates, the ABAP/HANA runtime manages the generated database procedure.

Do not manually create a separate unmanaged HANA procedure with the same logic.

After activation:

1. call the method with a known company code;
2. compare result totals with a trusted source;
3. test zero-row scenario;
4. test multiple customers;
5. test realistic data volume.

Correctness comes before performance claims.

━━━━━━━━━━━━━━━━━━━━
▶ 8. NULL BEHAVIOR IS NOT ABAP INITIAL-VALUE BEHAVIOR

SQLScript follows SQL NULL semantics.

ABAP developers can make mistakes when they assume:

• empty string;
• zero;
• initial date;
• NULL

all mean the same thing.

They do not.

Handle nullable expressions deliberately.

━━━━━━━━━━━━━━━━━━━━
▶ 9. CLIENT HANDLING

Client behavior can differ depending on data source and design.

Do not assume AMDP automatically behaves like every Open SQL statement.

Check the client-handling rules for your release and consumed objects.

This matters especially when using client-dependent persistence.

━━━━━━━━━━━━━━━━━━━━
▶ 10. TRANSACTION RESPONSIBILITY

Do not treat AMDP as an excuse to manage commits independently.

The surrounding ABAP application owns the business transaction.

For read-only calculation AMDPs this is simple.

For other patterns, follow the supported transaction semantics and avoid hidden side effects.

━━━━━━━━━━━━━━━━━━━━
▶ 11. WHEN OPEN SQL OR CDS IS BETTER

Before keeping the AMDP, ask:

Can this be expressed clearly with:

• modern Open SQL;
• CDS view entity;
• CDS aggregation;
• CDS table function only where needed?

If yes, the higher-level option is often easier to maintain.

AMDP is valuable when SQLScript genuinely adds value.

━━━━━━━━━━━━━━━━━━━━
▶ 12. PERFORMANCE TEST

Do not say “AMDP is faster” without evidence.

Compare:

1. original implementation;
2. AMDP implementation;
3. same business result;
4. same realistic data volume;
5. database/runtime measurements.

An AMDP that returns millions of unnecessary rows can still be slower overall.

━━━━━━━━━━━━━━━━━━━━
▶ 13. DEBUGGING APPROACH

When the method returns the wrong result:

1. confirm ABAP input parameter;
2. inspect the SQLScript logic;
3. check data source;
4. check filters;
5. check grouping;
6. check NULL/client behavior;
7. compare a small known data set;
8. use database/AMDP debugging tools supported by your environment when needed.

━━━━━━━━━━━━━━━━━━━━
▶ CHECKPOINT

You are ready for Part 3 when you can explain and create:

• an AMDP class;
• the HANA marker interface;
• an AMDP method;
• SQLScript parameters;
• USING dependencies;
• a set-based result;
• an ABAP caller.

━━━━━━━━━━━━━━━━━━━━
▶ COMMON MISTAKES

• Using AMDP for logic that simple Open SQL handles well.
• Forgetting the USING list.
• Forgetting the colon for host variables.
• Writing row-by-row SQLScript.
• Assuming NULL behaves like ABAP initial.
• Claiming performance improvement without measurement.
• Accessing unreleased SAP internals in a clean-core design.

━━━━━━━━━━━━━━━━━━━━
▶ TRY IT YOURSELF

Create an AMDP that returns total sales by material for one company code.

Then create a normal Open SQL version.

Compare:

• readability;
• runtime;
• database time;
• result correctness.

Keep the AMDP only if the pushdown is justified.

━━━━━━━━━━━━━━━━━━━━
▶ INTERVIEW TAKEAWAY

A strong answer is:

“An AMDP is an ABAP class method implemented in database language such as HANA SQLScript. The class implements the HANA AMDP marker interface, and the method declares database procedure, language, options and USING dependencies. I use AMDP only when database-specific set processing provides a clear benefit over Open SQL or CDS, and I validate performance with evidence.”

━━━━━━━━━━━━━━━━━━━━
▶ NEXT PART

Part 3 compares AMDP procedures, database functions and CDS table-function implementations.

A custom report reads several million rows into ABAP and aggregates them by customer.

ST05/SAT evidence shows most time is spent transferring and processing data.

The team first tests a set-based Open SQL solution. If that already solves the problem, AMDP is unnecessary. If specialized HANA processing is still needed, the team implements an AMDP and compares the same workload before adopting it.

The decision is based on measured benefit, not on the idea that database code is automatically faster.

• AMDP without a measurable need.
• Missing USING dependencies.
• Confusing ABAP and SQLScript variable syntax.
• Ignoring NULL semantics.
• Returning excessive data back to ABAP.
• Hiding business transaction control inside database logic.

• Measure first.
• Prefer set operations.
• Keep the AMDP contract small.
• Return only required columns/rows.
• Validate client and NULL behavior.
• Compare against Open SQL/CDS before accepting HANA-specific coupling.

Be ready to write the skeleton of an AMDP class and explain the marker interface, BY DATABASE PROCEDURE, FOR HDB, LANGUAGE SQLSCRIPT, OPTIONS and USING clauses. Also explain why you would choose Open SQL or CDS before AMDP for simpler pushdown.