Explain HANA SQLScript & Stored Procedures in SAP

43 / 65

HANA DBadvanced

HANA SQLScript & Stored Procedures

Writing procedural logic natively in HANA using SQLScript, including table types, procedures, and control-flow constructs.

SQLScript is HANA's procedural language. It is what AMDP methods and scripted calculation views are written in.

Writing a cursor-based row-by-row loop in SQLScript is like unloading a delivery truck one box at a time by hand when a forklift (set-based SQL) could move the whole pallet at once - HANA's engine is built and optimized for the forklift approach, and procedural loops should be reserved for the rare cases genuinely requiring one box at a time.

Key Concepts

    CREATE PROCEDURE calc_margin (
      IN  iv_year   NVARCHAR(4),
      OUT et_result TABLE (product NVARCHAR(40), margin DECIMAL(15,2))
    ) AS BEGIN
      lt_sales = SELECT product, SUM(amount) AS revenue
                   FROM sales WHERE year = :iv_year GROUP BY product;
      et_result = SELECT s.product, s.revenue - c.cost AS margin
                    FROM :lt_sales s JOIN costs c ON c.product = s.product;
    END;

Table variables are the distinctive feature. lt_sales holds a result set, is passed to the next statement, and can be returned. That is how an AMDP hands structured results back to ABAP.

lt_sales

Note what this is not: it is not a cursor. Each assignment is a set operation, and the engine can optimise across them.

Set-based, not row-by-row, and here it matters more than usual. SQLScript has IF, WHILE, FOR and cursors. A cursor loop processing a million rows one at a time throws away everything HANA is built for — parallelism, vectorised operations, and column-store access.

IFWHILEFOR

    cursor loop over 1,000,000 rows   -> minutes, single-threaded
    one set-based statement            -> seconds, across all cores

The same rule applies in every database. In HANA the penalty is larger, because the alternative is faster.

Recursive CTEs for hierarchies. Bill-of-material explosion and organisational trees are naturally recursive, and used to be coded in the application layer with a loop per level:

    WITH RECURSIVE bom_tree AS (
      SELECT component, qty, 1 AS level FROM bom WHERE parent = :iv_material
      UNION ALL
      SELECT b.component, b.qty * t.qty, t.level + 1
        FROM bom b JOIN bom_tree t ON b.parent = t.component
    )
    SELECT * FROM bom_tree;

The whole explosion happens inside the database, at any depth, without a round trip per level.

Where it silently goes wrong. Some constructs force the engine to materialise an intermediate result, which blocks the optimisations that make the rest fast. Imperative logic between set statements is the usual cause.

So native SQLScript does not guarantee good performance — it guarantees the work happens in the right place. How it is written still decides how fast it is.

Which means: verify it. EXPLAIN PLAN and PlanViz show what the procedure actually does. A procedure assumed to be fast because it is "native" is the thing worth checking, since the assumption is common and often wrong.

EXPLAIN PLAN

What the interviewer is probing.1. "What is the main rule for writing fast SQLScript?" Probing: the principle. Stalls: "Short code." Moves up: think in sets — declarative table variables the optimiser can combine, not imperative loops over single rows.

2. "When is an imperative loop unavoidable?" Probing: the honest exception. Stalls: "Never." Moves up: genuinely sequential logic such as iterative calculations or dynamic SQL construction — and even then keep it outside the data-heavy part.

3. "What is the difference between a scalar and a table function?" Probing: the detail. Stalls: "Nothing." Moves up: a table function can be consumed in a FROM clause and is inlined by the optimiser; a scalar function called per row blocks parallelisation.

4. "How do you find out why a procedure is slow?" Probing: the tooling. Stalls: "Read it." Moves up: PlanViz on the call, which shows which node dominates and whether the optimiser inlined or materialised each step.