---
description: A procedure is a unit/module that perform a specific task. This procedure can be combined to form larger programs. This basically forms the &#039;Modular Design&#039;. A procedure can be invoked by another proc
title: Stored Procedure in SAP HANA (SQL Tutorial)
image: https://www.guru99.com/images/sap-hana-sql-stored-procedure.png
---

[Skip to content](#main)

**⚡ 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](https://www.guru99.com/sql.html) procedure in [SAP HANA](https://www.guru99.com/sap-hana-tutorial.html) 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:<br>• **IN** — read-only input passed to the procedure.<br>• **OUT** — output returned from the procedure.<br>• **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):<br>• **DEFINER** — runs with the privileges of the procedure’s creator.<br>• **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](https://www.guru99.com/sql.html) 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.

**Don't Miss:**

- [SAP HANA Modeling: Views, Tables & Data Models](https://www.guru99.com/sap-hana-modeling.html)
- [SAP DS (Data Services) in HANA](https://www.guru99.com/sap-ds-sap-data-services-in-sap-hana.html)
- [SAP HANA Direct Extractor Connection (DXC) Tutorial](https://www.guru99.com/sap-hana-direct-extractor-connection-dxc-in-sap-hana.html)
- [Top 50 SAP BO Interview Questions and Answers (2026)](https://www.guru99.com/sap-bo-interview-questions.html)

## 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

⚡ What is the difference between a procedure and a function in HANA?

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.

🚀 What languages can a HANA procedure use?

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.

💡 What is the difference between DEFINER and INVOKER security?

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.

🔒 When should I use READS SQL DATA?

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.

📐 How do I call a HANA stored procedure?

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.

⏱️ How do I drop or alter a stored procedure?

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.

🤖 How can AI help write HANA stored procedures?

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.

✍️ Can AI explain an existing HANA procedure?

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:

ChatGPTPerplexityGrokGoogle AI

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.**

##### Sign up for the newsletter

Subscribe for Free

You have successfully subscribed.  
Please check your inbox.

![AI-Newsletter]() Chosen by over **350,000+** professionals

[Scroll to top](#wrapper)

×

Search for:

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/sap-hana-sql-stored-procedure.png","url":"https://www.guru99.com/images/sap-hana-sql-stored-procedure.png","width":"200","height":"200","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/sap-hana-sql-stored-procedure.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sap-hana","name":"SAP-HANA"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/sap-hana-sql-stored-procedure.html","name":"Stored Procedure in SAP HANA (SQL Tutorial)"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/sap-hana-sql-stored-procedure.html#webpage","url":"https://www.guru99.com/sap-hana-sql-stored-procedure.html","name":"Stored Procedure in SAP HANA (SQL Tutorial)","dateModified":"2026-10-09T13:25:54+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/sap-hana-sql-stored-procedure.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/sap-hana-sql-stored-procedure.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/editorial","name":"Editorial Team","url":"https://www.guru99.com/author/editorial","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/favicon-new-logo.png","url":"https://www.guru99.com/images/favicon-new-logo.png","caption":"Editorial Team","inLanguage":"en-US"},"sameAs":["https://www.guru99.com/editorial-team"],"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"SAP-HANA","headline":"Stored Procedure in SAP HANA (SQL Tutorial)","description":"A procedure is a unit/module that perform a specific task. This procedure can be combined to form larger programs. This basically forms the &#039;Modular Design&#039;. A procedure can be invoked by another proc","keywords":"sap-hana","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/editorial","name":"Editorial Team"},"dateModified":"2026-10-09T13:25:54+05:30","image":{"@id":"https://www.guru99.com/images/sap-hana-sql-stored-procedure.png"},"copyrightYear":"2026","name":"Stored Procedure in SAP HANA (SQL Tutorial)","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is the difference between a procedure and a function in HANA?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"What languages can a HANA procedure use?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"What is the difference between DEFINER and INVOKER security?","acceptedAnswer":{"@type":"Answer","text":"DEFINER runs the procedure with the privileges of the user who created it \u2014 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."}},{"@type":"Question","name":"When should I use READS SQL DATA?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"How do I call a HANA stored procedure?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"How do I drop or alter a stored procedure?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"How can AI help write HANA stored procedures?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can AI explain an existing HANA procedure?","acceptedAnswer":{"@type":"Answer","text":"Yes. AI tools annotate SQLScript line by line \u2014 explaining what each parameter does, why a particular join was chosen, and where the procedure might fail with bad inputs \u2014 making code review and onboarding much faster."}}]}],"@id":"https://www.guru99.com/sap-hana-sql-stored-procedure.html#schema-1111642","isPartOf":{"@id":"https://www.guru99.com/sap-hana-sql-stored-procedure.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/sap-hana-sql-stored-procedure.html#webpage"}}]}
```
