Sap Hana Sqlscript

SAP HANA SQLScript

Prerequisites

• SAP HANA 2.0 SPS05+ (or HANA Cloud) with SQLScript enabled
• HANA Database Explorer or HANA Studio for debugging/profiling
• HDI container access (for CAP/HDI deployments) or direct schema access
• ABAP 7.4 SP05+ on HANA for AMDP (requires IF_AMDP_MARKER_HDB interface)
• ADT (ABAP Development Tools) for AMDP class editing

Stored Procedure

• Host variables use colon prefix (:iv_matnr) inside SQL statements
• SELECT assigns to variables via expression (lv_count = SELECT COUNT(*)...) — no INTO clause
• SESSION_CONTEXT('CLIENT') retrieves session variables

CREATE PROCEDURE ZSP_MATERIAL_STOCK (
  IN  iv_plant  NVARCHAR(4),
  OUT et_result TABLE(MATNR NVARCHAR(18), MAKTX NVARCHAR(40), LABST DECIMAL(13,3))
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
  et_result = SELECT
    m.MATNR, m.MAKTX, COALESCE(s.LABST, 0) AS LABST
  FROM "MARA" AS m
  LEFT JOIN "MARD" AS s ON m.MATNR = s.MATNR AND s.WERKS = :iv_plant
  WHERE m.LVORM = '';
END;

Table Function

CREATE FUNCTION ZTF_PRODUCT_ACTIVE(p_plant NVARCHAR(4))
RETURNS TABLE(MATNR NVARCHAR(18), MAKTX NVARCHAR(40))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
  RETURN SELECT m.MATNR, m.MAKTX
    FROM "MARA" AS m
    JOIN "MARD" AS s ON m.MATNR = s.MATNR
    WHERE s.WERKS = :p_plant AND m.LVORM = '';
END;

Declarative vs Imperative

Rule: prefer declarative. The SQLScript optimizer rewrites declarative code at runtime for superior execution plans. Imperative logic (loops, cursors) bypasses the optimizer.

-- Declarative (preferred — optimizer can inline, push down, reorder)
CREATE PROCEDURE ZSP_DECLARATIVE(IN iv_year INT, OUT et_result TABLE(...))
AS BEGIN
  et_result = SELECT a.*, b.text
    FROM accounting AS a
    JOIN texts AS b ON a.id = b.id AND a.year = :iv_year;
END;

-- Imperative (only when logic truly requires row-by-row processing)
CREATE PROCEDURE ZSP_IMPERATIVE(IN iv_limit INT, OUT et_result TABLE(...))
AS BEGIN
  DECLARE CURSOR cur FOR SELECT * FROM mara LIMIT :iv_limit;
  FOR r AS cur DO
    IF r.LVORM = '' THEN
      -- row-level processing
    END IF;
  END FOR;
END;

AMDP (ABAP-Managed Database Procedures)

AMDP wraps SQLScript inside ABAP class methods — lifecycle managed via transport, debuggable from ADT, extensible via SAP Notes.

CLASS zcl_amdp_material DEFINITION PUBLIC FINAL.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    METHODS get_stock
      IMPORTING VALUE(iv_plant) TYPE werks_d
      EXPORTING VALUE(et_result) TYPE ANY TABLE.
ENDCLASS.

CLASS zcl_amdp_material IMPLEMENTATION.
  METHOD get_stock
    BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING mara mard.

    et_result = SELECT m.matnr, COALESCE(s.labst, 0) AS labst
      FROM mara AS m
      LEFT JOIN mard AS s ON m.matnr = s.matnr
      WHERE s.werks = :iv_plant;
  ENDMETHOD.
ENDCLASS.

• BY DATABASE PROCEDURE FOR HDB — declares AMDP method
• OPTIONS READ-ONLY — marks method as read-only (recommended when no DML)
• USING — mandatory when referencing ABAP dictionary objects (tables, views, other AMDPs)

CE Functions (Calculation Engine)

-- CE functions bypass SQL optimizer — use only for proven hot paths
SELECT ce_aggregation(:lt_data, [SUM("AMOUNT")], ["REGION"]) AS result
FROM dummy;

Performance Analysis

Plan Visualizer

EXPLAIN PLAN FOR SELECT matnr, maktx FROM mara WHERE matnr = 'MAT001';
-- HANA Database Explorer → Plan Visualizer tab
-- Look for: TABLE SCAN (missing index), high join cardinality, materialization

AMDP Profiler

1. Window → Preferences → ABAP Development → Profiling → Enable AMDP trace
2. Execute AMDP method via ADT or ABAP program
3. Right-click result → Profile As → ABAP Profiling
4. Inspect per-statement execution time and row counts

Profiler limitations: does not track CE functions, loops, or ABAP CDS table functions called via ABAP SQL.

HDI Container Deployment

db/src/
├── procedures/
│   └── get_material_stock.hdbprocedure
├── functions/
│   └── get_active_products.hdbtablefunction
└── .hdinamespace

Deploy via cds deploy (CAP) or hdi-deploy (standalone HDI).

Pitfalls

• Imperative loops kill performance — the SQLScript optimizer cannot rewrite cursor-based logic. Always try declarative first.
• Missing USING clause in AMDP causes runtime dump CX_AMDP_NO_ABAP_TABLE_IN_DBPROC — list every referenced DDIC object.
• Table variables consume default memory pool — large intermediate results can exhaust $TMPTABLES. Filter early with WHERE before materializing.
• SELECT without LIMIT in cursors materializes the full result set into memory — add LIMIT or paginate.
• CE functions are not optimizer-friendly — they bypass the SQL plan. Benchmark against declarative SQL before adopting.
• SQL SECURITY DEFINER grants procedure the owner's privileges — prefer INVOKER for least-privilege unless explicitly required.
• AMDP Profiler gaps — no profiling data for CE functions, fine-grained SQLScript elements (loops, IF), or CDS table functions consumed via ABAP SQL.
• Quoted identifiers — use "MARA" (double quotes) for schema objects in native HANA; unquoted identifiers are uppercased implicitly.

Verification

# Validate HDI artefact syntax (local, from skill root)
python scripts/hdi_lint.py --path db/src/procedures/get_material_stock.hdbprocedure
# or: npm run hana:hdi-lint -- db/src/procedures/

# Check AMDP activation in ABAP (requires ADT CLI)
python scripts/sap_router.py adt activate ZCL_AMDP_MATERIAL

-- Verify procedure/function exist and are valid
SELECT PROCEDURE_NAME, STATUS FROM SYS.PROCEDURES WHERE PROCEDURE_NAME = 'ZSP_MATERIAL_STOCK';
SELECT FUNCTION_NAME, STATUS FROM SYS.FUNCTIONS WHERE FUNCTION_NAME = 'ZTF_PRODUCT_ACTIVE';