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 native procedural language, and interviewers ask about it specifically to test whether a candidate can write genuine database-layer logic rather than only ever pushing computation up into an application layer (ABAP, Java, or otherwise) - a skill increasingly relevant given how much SAP's own tooling (AMDP, calculation view scripted logic) relies on it under the hood.

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

A SQLScript procedure is created with CREATE PROCEDURE, declaring typed input/output parameters (which can be scalar values or full table types) and a body that mixes declarative SQL with procedural constructs - IF/WHILE/FOR loops, local variable declarations, and cursors for row-by-row iteration when genuinely necessary (though, as in most database-procedural languages, set-based operations should be strongly preferred over row-by-row cursor loops wherever the logic can be expressed that way, since HANA's engine is optimized for bulk set operations, not iterative row processing).

CREATE PROCEDUREIFWHILEFOR

Common table expressions (WITH ... AS (...)) and recursive CTEs are the standard technique for hierarchical or graph-like logic - bill-of-material explosion, organizational hierarchy traversal - directly in SQLScript, letting these normally application-layer-coded algorithms run natively inside the database at native performance rather than looping row-by-row from an external application layer. SQLScript also supports table variables and table types as first-class values that can be passed between procedures, built up incrementally, and returned as a procedure's or table function's result set, which is the mechanism underlying AMDP's ability to return proper structured table results back to calling ABAP code.

WITH ... AS (...)

A senior-level answer should mention performance-conscious SQLScript practice: preferring declarative set-based SQL over explicit cursors and loops wherever possible (the classic "row-by-row is slow-by-slow" principle applies inside SQLScript exactly as it does anywhere else), being mindful of how procedure logic interacts with HANA's optimizer (some constructs can inadvertently force materialization of intermediate results, blocking certain optimizations), and using the EXPLAIN PLAN/PlanViz visual tools to verify a procedure is actually executing efficiently rather than assuming native SQLScript automatically guarantees good performance regardless of how it's written.

EXPLAIN PLAN