CDS Table Functions in ABAP: SQLScript as a CDS Entity
CDS views handle most data modeling beautifully — until they don't. You need a recursive hierarchy explosion, a complex procedural calculation, or logic that simply can't be expressed declaratively. Rewriting everything as a raw AMDP call works, but you lose the CDS superpowers: a typed entity, parameters, reusability in other views. CDS table functions give you both: a CDS entity whose rows are produced by an AMDP method.
What a table function is
A CDS table function looks like a CDS entity — it has a name, parameters, and a field list — but instead of a SELECT, its implementation is an AMDP method written in SQLScript. Consumers query it like any table or view:
SELECT FROM ztf_bom_explosion( p_matnr = '100-100' )
FIELDS matnr, component, quantity, level
INTO TABLE @DATA(lt_bom).
The caller sees a table. Underneath, an AMDP procedure ran the recursive logic. That separation — declarative interface, procedural implementation — is the whole idea.
Key takeaway: A CDS table function is a CDS entity implemented by an AMDP method. You get SQLScript's full power behind a clean, reusable, typed CDS interface.
Defining one: the CDS side
The DDL is compact. You declare parameters and the result structure, and point at the implementing class:
define table function ZTF_BOM_EXPLOSION
with parameters p_matnr : matnr
returns {
matnr : matnr;
component : matnr;
quantity : menge_d;
level : int4;
}
implemented by method
zcl_bom_functions=>explode_bom;
Everything a consumer needs — parameter names and types, the exact shape of the result — is declared here. The CDS tooling validates consumers against this contract, so a typo in a field name fails at design time, not at 2 AM in production.
The AMDP side
The implementing method follows the standard AMDP pattern — IF_AMDP_MARKER_HDB, BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT — with parameters matching the CDS declaration. For a BOM explosion, the SQLScript body uses a recursive CTE or a HANA hierarchy function to walk the structure:
METHOD explode_bom BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY.
lt_result = SELECT ... FROM HIERARCHY ( ... );
ENDMETHOD.
(Body simplified — the point is the pattern, not the specific hierarchy syntax.) The method's importing/exporting parameters must line up exactly with the CDS parameters and return fields; the activation will tell you when they don't.
Consuming table functions in CDS views
Table functions really earn their keep when other CDS views consume them. A view can join the table function like any table:
define view entity ZV_BOM_COSTING as select from ztf_bom_explosion( p_matnr = '100-100' ) as bom
inner join zmaterial_price as price
on price.matnr = bom.component
{
bom.component,
bom.quantity,
bom.quantity * price.unit_price as extended_cost
}
Now the procedural BOM walk is a reusable building block inside the declarative CDS world — joinable, filterable, and consumable by OData services and Fiori apps without anyone knowing SQLScript was involved.
Parameters, client handling, and testing
Two practical details catch people out. First, parameters are mandatory — every call must supply every declared parameter; there are no optional parameters with defaults like in some other frameworks. Design the parameter list accordingly: keep it small and meaningful.
Second, client handling is your responsibility. Unlike a CDS view entity, a table function's AMDP body doesn't get automatic client filtering — you must include the client field in your logic, typically by reading the session context (SESSION_CONTEXT('CLIENT')) inside the SQLScript and filtering on it explicitly. Forget this and your function cheerfully returns other clients' data. It's the single most common table-function bug in code reviews, and it's worth a dedicated check.
For testing, start outside ABAP: run SELECT against the table function directly in the HANA SQL console with literal parameter values. If the logic is wrong there, no ABAP wrapper will fix it. Once the SQL is right, test the CDS consumption in a small ABAP report before wiring it into views and services.
When to use them (and when not to)
Use table functions for logic that genuinely needs SQLScript: hierarchies, recursion, complex procedural calculations, or HANA-specific functions with no CDS equivalent.
Don't use them as a shortcut around learning CDS. If a plain view with associations and calculated fields can express it, the plain view wins — better tooling, better extensibility, portable across databases.
And remember the clean-core angle: in ABAP Cloud, table functions are a sanctioned escape hatch for complex data logic. They keep the complexity inside the data model, behind a stable contract, instead of leaking procedural code into every consumer. That's exactly where you want it.
Read more →