Stored Procedure in SAP HANA (SQL Tutorial)

By : Mason Garcia Mason Garcia
Hours Updated

⚡ Smart Summary

SAP HANA SQL Stored Procedure is a reusable, named block of SQLScript that performs a specific data operation. It accepts IN, OUT, and INOUT parameters, can be marked read-only with READS SQL DATA, and is created at schema or package level using the CREATE PROCEDURE syntax.

  • 🧱 Pick where to create: Define procedures at schema (Catalog) or package (Content) level depending on where they belong in the SAP HANA data model.
  • 📥 Use parameters intentionally: IN passes input, OUT returns output, INOUT does both — name them for clarity in every signature.
  • 🔐 Choose SQL SECURITY carefully: DEFINER runs with the creator’s privileges; INVOKER runs with the caller’s — pick based on data-access policy.
  • 📚 Mark read-only when possible: READS SQL DATA documents intent, optimises planning, and allows the procedure to be exposed through a result view.
  • 🤖 Use AI to draft procedures: AI assistants turn plain-English logic into CREATE PROCEDURE templates with parameters, exception handlers, and result views.
Read More

SAP HANA SQL Stored Procedure

What is an SQL Stored Procedure?

A stored procedure is a named, reusable unit of code that performs a specific task. Procedures can be composed into larger programs, which is the foundation of modular design. One procedure can call another — the caller is referred to as the calling program.

Procedures are reusable processing blocks with a defined sequence of data transformations. They can accept multiple input and output parameters and can be declared read-only or read-write.

An SQL procedure in SAP HANA can be created at:

  • Schema level (Catalog node) — managed through the catalog and visible to every consumer with the right privileges.
  • Package level (Content node) — managed as a design-time artefact inside an application package.

SAP HANA Stored Procedure Syntax

The general syntax for creating a procedure in SAP HANA is shown below.

CREATE PROCEDURE <proc_name> [(<parameter_clause>)] [LANGUAGE <lang>]
    [SQL SECURITY <mode>] [DEFAULT SCHEMA <default_schema_name>]
    [READS SQL DATA [WITH RESULT VIEW <view_name>]] AS
    { BEGIN [SEQUENTIAL EXECUTION]
        <procedure_body>
      END
    | HEADER ONLY }

The CREATE PROCEDURE statement creates a procedure using the programming language specified by <lang>. The default language is SQLScript.

Syntax Elements Explained

The table below describes every clause used in the CREATE PROCEDURE syntax.

Element Description
<proc_name> Name of the procedure.
<parameter_clause> List of parameters, each marked IN, OUT, or INOUT:
• IN — read-only input passed to the procedure.
• OUT — output returned from the procedure.
• INOUT — both passes a value in and returns the modified value out.
LANGUAGE <lang> Programming language used in the procedure. Default: SQLSCRIPT.
SQL SECURITY <mode> Security mode (Default: DEFINER):
• DEFINER — runs with the privileges of the procedure’s creator.
• INVOKER — runs with the privileges of the caller.
DEFAULT SCHEMA <default_schema_name> Schema used to resolve unqualified objects in the procedure body. If omitted, the current session schema is used.
READS SQL DATA Marks the procedure as read-only. It cannot modify data or schema, contains no DDL or DML, and can only call other read-only procedures.
WITH RESULT VIEW <view_name> Defines the result view used as the procedure’s output. When a result view is present, the procedure can be queried by another SQL statement as if it were a table or a view.
SEQUENTIAL EXECUTION Forces the procedure body to execute sequentially with no parallelism.
<procedure_body> Main body of the procedure, written in the selected programming language.
HEADER ONLY Creates only the procedure’s properties with an OID, without compiling the body. Useful for forward declarations.

Example: Create a Simple Stored Procedure

The example below creates a read-only procedure that returns sales total for a given customer.

CREATE PROCEDURE GET_CUSTOMER_SALES (
    IN  iv_customer_id NVARCHAR(10),
    OUT ev_total       DECIMAL(15,2)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA AS
BEGIN
    SELECT SUM(NET_VALUE) INTO ev_total
    FROM   "SALES"."ORDERS"
    WHERE  CUSTOMER_ID = :iv_customer_id;
END;

Call the procedure with:

CALL GET_CUSTOMER_SALES ('C0001', ?);

SAP HANA Studio (or any JDBC/ODBC client) returns the value of ev_total in the output parameter.

RELATED ARTICLES

Calling a Stored Procedure

Once created, a stored procedure is invoked through the CALL statement, optionally inside another procedure, an SQLScript function, or an external client. The four most common invocation patterns are:

  • From SQL: CALL my_proc(p1, p2, ?); — placeholders return OUT values.
  • From another procedure: CALL another_proc(:in1, :out1);
  • From an application: bind input parameters and read output through JDBC, ODBC, or the Cloud SDK.
  • As a virtual table: when a result view is declared, query the procedure with a SELECT against the view name.

Best Practices for HANA Stored Procedures

The habits below keep HANA procedures readable, performant, and easy to maintain:

  • Name parameters with prefixes: iv_ for input, ev_ for export, and iov_ for input/output.
  • Mark intent with READS SQL DATA wherever the procedure is genuinely read-only.
  • Use SQL SECURITY INVOKER for shared procedures that should respect the caller’s privileges.
  • Wrap each block in BEGIN/END and keep the body short — split heavy logic into helper procedures.
  • Handle exceptions: use DECLARE EXIT HANDLER for graceful error reporting.
  • Avoid SELECT * inside procedures — name the columns you actually need so plans stay stable.

FAQs

A procedure can have multiple OUT parameters, can issue DML, and is invoked with CALL. A function returns a single scalar or table value, runs without side effects, and is used inside SELECT, WHERE, or other expressions.

The default and most common language is SQLScript. HANA also supports R for procedures that need statistical libraries and L for low-level scripting. SQLScript is the right choice for almost every business procedure.

DEFINER runs the procedure with the privileges of the user who created it — useful when the procedure should access tables the caller cannot see directly. INVOKER runs with the caller’s privileges, which is safer for shared utility procedures.

Use READS SQL DATA whenever the procedure only reads data and never writes it. It documents intent, allows result views, and lets the optimiser plan the procedure more aggressively.

Use the CALL statement: CALL proc_name (in_val, out_param);. From an application, bind inputs and outputs through the JDBC, ODBC, or Cloud SDK driver, or expose the procedure through OData for REST clients.

Drop a procedure with DROP PROCEDURE proc_name; and recreate it with CREATE OR REPLACE PROCEDURE. SAP HANA does not support ALTER PROCEDURE; the standard pattern is CREATE OR REPLACE.

AI assistants generate CREATE PROCEDURE templates, suggest parameter names, write exception handlers, and translate plain-English logic into SQLScript. They also point out missing schema qualifications and risky DML inside read-only contexts.

Yes. AI tools annotate SQLScript line by line — explaining what each parameter does, why a particular join was chosen, and where the procedure might fail with bad inputs — making code review and onboarding much faster.

Summarize this post with: