SAP Procedures

A stored procedure is reusable SQLScript logic stored in HANA and callable by name. Procedures are used when logic is too complex for a single view or must run in steps, complex calculations, data transformations, or operations that update data, all executed close to the data for performance.

The distinction that decides everything else is between read only and read write. A read only procedure returns data and changes nothing, which lets HANA parallelise it aggressively and lets you call it from a view. A read write procedure can insert, update and delete, which rules that out. Declaring a procedure read only when it genuinely is costs nothing and buys real optimisation, and it is the single most commonly skipped declaration.

The second distinction is procedure versus function. A function returns a value or a table and can be used inside a query, so it composes. A procedure is called on its own and can return several result sets. If you find yourself calling a procedure and then querying its output, a table function was probably what you wanted.

Where they live is worth knowing before you write one. A procedure is a database object inside a schema, created with CREATE PROCEDURE, listed in the system views alongside tables and views, and secured with grants like anything else. In an SAP application system there is a second home: procedures defined in the ABAP repository, which are transported and versioned with the rest of the code. Which of the two you are looking at decides who can change it and how it reaches production, and confusing them is how a working development system ships nothing.

• Input/output parameters and table parameters.
• Local variables and table variables.
• Control flow (IF, loops) alongside set-based SQL.
• Read-only vs read-write procedures.

The feature to understand first is the table variable, because it is what makes SQLScript different from writing a long single statement. A table variable holds an intermediate result, and the reason to use one is that it lets the optimiser see the whole chain: it can reorder, it can parallelise independent branches, and it can drop a branch whose result nobody uses.

That last point surprises people. Assigning a table variable does not necessarily execute anything. The work happens when the result is needed, so a procedure with ten assignments and one output may only ever run the three branches that feed the output. This is why sprinkling counts into a procedure to see what it is doing changes what it does.

Imperative logic is available too: variables, IF, WHILE, cursors. All of it is legal and all of it forces the engine to execute step by step rather than as a set. The working rule is that imperative constructs are for control flow, not for data. A cursor looping over rows to compute a total is the classic case of a statement written as a program.

An hour, and it makes the declarative behaviour concrete rather than theoretical.

1. Create a read only procedure taking one input parameter and returning one table output, with three table variables: a filtered read, an aggregation over it, and a join to a master data table.
2. Call it and check the result is what you expect.
3. Now add a fourth table variable that nobody uses, doing something expensive, and call it again. The runtime should barely change, because the branch is pruned.
4. Change the filter so the input parameter is wrapped in a function. Call it again and compare. The predicate no longer pushes down and the read gets much larger.
5. Run the whole thing through PlanViz and read where each table variable landed.

Step four is the one worth repeating until it is automatic, because it is the mistake that survives code review: the code reads correctly and the plan is wrong. See HANA performance.

Prefer views (calculation/CDS) for read/query models, they are optimised and reusable. Use procedures when you need procedural steps, side effects (writes), or logic that does not fit a declarative view. Favour set-based operations inside procedures over row loops for performance.

Put plainly, the decision is about whether the consumer needs to filter. A view is queried, so the consumer's WHERE clause reaches the data and the same view serves many purposes. A procedure is called with fixed parameters, so it does what it was written to do and nothing else. Modelling something as a procedure because it felt like a program is how teams end up with forty procedures that are each one report. See calculation views and HANA modeling.

The cases where a procedure is genuinely right: the logic needs several steps that cannot be expressed as one query, the operation writes data, or the result depends on control flow that a view cannot express.

Security is part of this decision and it is easy to miss. A procedure runs either with the caller's rights or with the definer's, and that choice is made at creation time. Definer rights let a procedure read tables the caller cannot, which is either exactly what you wanted or a hole, depending on whether anyone thought about it. The safe default is caller rights, and definer rights should be a deliberate decision with a reason somebody wrote down.

ABAP can call HANA procedures (via AMDP, ABAP-managed database procedures) to push heavy logic down. AMDP lets developers write SQLScript within ABAP classes, combining ABAP lifecycle with HANA-side execution.

Version note: on S/4HANA the strategic path for this kind of logic is CDS with ABAP managed database procedures where SQLScript is genuinely needed, rather than procedures created directly in the database and called from ABAP. Procedures created outside the ABAP repository are invisible to transport, to where-used and to the upgrade tooling, which is fine on a standalone HANA and a problem inside an SAP application. See the HANA database.

Two operational points that only appear once something is live. Procedures are compiled, so the first call after a change pays a compilation cost and later ones do not, which makes a single timing measurement misleading. And a procedure that fails leaves its caller to handle the error, so a procedure called from ABAP without exception handling produces a short dump rather than a message.

• Row loops in SQLScript instead of set operations.
• Procedures where a view would do (less reusable).
• Heavy logic in ABAP that should be pushed down (AMDP).

• Cursors doing set work. The most expensive habit carried over from other databases.
• Omitting the read only declaration. Free optimisation, left on the table.
• Debugging by inserting counts. It forces branches the engine would otherwise prune, so you measure something other than what runs.
• Procedures created outside the ABAP repository in an SAP system, which then do not transport.

Version note: SQLScript itself has been stable for a long time, and what has changed is where SAP expects this logic to live. Older material treats database procedures as the normal way to push logic down. Current guidance puts as much as possible in CDS and reserves procedures for what CDS cannot express, which is a narrower set than it used to be.

Writing a procedure is an afternoon, and knowing which logic belongs in one rather than in a view is the judgement the course builds.