SAP HANA SQLScript Procedures: Syntax, Examples, and Troubleshooting

01What a SQLScript procedure does

A SQLScript procedure packages database-side logic into a reusable database object. It can accept scalar or table inputs, return scalar or table outputs, read and change data, and combine SQL statements with SQLScript control logic. Keeping data-intensive work in the database can reduce application round trips and provide a single implementation for several callers.

A procedure is a good fit when an operation has multiple steps, needs explicit input and output parameters, or must be called by several applications. A calculation view is generally better for a reusable analytical model, while a table function is useful when a query needs a tabular result that can participate in other SQL statements. See the related guide to SAP HANA calculation views when the main requirement is semantic modeling rather than procedural execution.

02Create a basic SQLScript procedure

Create procedures in the target SAP HANA database using SAP HANA database explorer, SAP HANA cockpit SQL tools, or hdbsql. The deploying user needs the privileges required to create procedures in the target schema and to access the referenced objects.

The following example returns orders for one customer:

CREATE OR REPLACE PROCEDURE get_customer_orders (
    IN iv_customer_id NVARCHAR(20),
    OUT et_orders TABLE (
        customer_id NVARCHAR(20),
        order_id BIGINT,
        order_total DECIMAL(15, 2)
    )
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    et_orders =
        SELECT customer_id,
               order_id,
               order_total
          FROM sales.orders
         WHERE customer_id = :iv_customer_id;
END;

The IN parameter supplies a value to the procedure, and the OUT parameter describes the returned table structure. The assignment uses = because et_orders receives the result of a query. The colon before iv_customer_id identifies the input parameter reference inside the SQL statement.

SQL SECURITY INVOKER makes the procedure execute with the caller's privileges. This is useful when each caller must be authorized against the underlying objects. SQL SECURITY DEFINER executes with the privileges of the procedure owner and requires careful ownership and privilege design.

03Use SQLScript variables and intermediate results

SQLScript procedures can assign query results to scalar variables or table variables. Intermediate results make multi-step processing easier to inspect and can improve readability when each step has a distinct business purpose.

CREATE OR REPLACE PROCEDURE summarize_customer_orders (
    IN iv_customer_id NVARCHAR(20),
    OUT ev_order_count INTEGER,
    OUT ev_total DECIMAL(15, 2)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    DECLARE lt_orders TABLE (
        order_id BIGINT,
        order_total DECIMAL(15, 2)
    );

    lt_orders =
        SELECT order_id,
               order_total
          FROM sales.orders
         WHERE customer_id = :iv_customer_id;

    SELECT COUNT(*), COALESCE(SUM(order_total), 0)
      INTO ev_order_count, ev_total
      FROM :lt_orders;
END;

A table variable is referenced with a colon when it appears as a data source. Assignments to the variable use its name without a colon. Keep intermediate tables narrow by selecting only the columns needed by later steps.

For logic that can be expressed as one relational statement, prefer a set-based query. This usually gives the optimizer more opportunity to organize the work than row-by-row loops or repeated scalar assignments.

04Call and test the procedure

Call a procedure with the CALL statement. Output parameters can be returned directly by the SQL client:

CALL get_customer_orders('C10042', ?);

For a procedure with scalar outputs, use:

CALL summarize_customer_orders('C10042', ?, ?);

In SAP HANA database explorer, execute the call in a SQL console connected to the database and schema containing the procedure. Confirm the active connection before testing, especially when development, test, and production systems use similar schema names.

Use representative data for testing: a customer with several rows, a customer with no rows, and values at the boundaries of the declared data types. Check null behavior explicitly. For example, COALESCE prevents an aggregate over an empty input from returning a null total when the application expects zero.

When a procedure is called by an application, log the input correlation identifier at the application boundary and capture the database error text. This creates a traceable path from the application request to the failing SQLScript statement without placing diagnostic output inside business tables.

05Handle errors and transaction boundaries

SQLScript supports exception handling with an exception block. Use it when the caller needs a controlled response or when diagnostic information must be recorded before the error is propagated.

CREATE OR REPLACE PROCEDURE validate_order_total (
    IN iv_order_id BIGINT,
    IN iv_expected_total DECIMAL(15, 2),
    OUT ev_status NVARCHAR(20)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    DECLARE lv_actual_total DECIMAL(15, 2);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ev_status = 'ERROR';
    END;

    SELECT order_total
      INTO lv_actual_total
      FROM sales.orders
     WHERE order_id = :iv_order_id;

    IF :lv_actual_total = :iv_expected_total THEN
        ev_status = 'MATCH';
    ELSE
        ev_status = 'MISMATCH';
    END IF;
END;

Choose the handler scope deliberately. A broad handler can hide the original failure if it converts every exception into a normal-looking status. For operational diagnosis, preserve the original error in the calling layer or re-raise it after recording the necessary context.

Define transaction ownership between the procedure and its caller. A procedure that performs data changes should document whether the application commits the surrounding transaction or whether a separate administrative operation controls it. Test rollback behavior with both successful and failing calls.

06Secure procedure execution

Apply least privilege to both the procedure owner and its callers. The caller needs EXECUTE on the procedure, while the procedure's security mode determines how access to referenced objects is evaluated.

Use dedicated schemas for application objects and keep deployment users separate from runtime users. Avoid embedding passwords, credentials, or unrestricted dynamic SQL in procedure source. If dynamic SQL is required, validate object names and constrain values through parameters.

A procedure can expose more data than its underlying query appears to expose if filters are incomplete. Include tenant, organizational, and authorization predicates in the procedure design when the data model requires them. Analytic privileges and application-level authorization may also be relevant for the consuming application.

For related authorization design, see SAP HANA user privileges only when the procedure is being deployed in an environment where database privilege administration is part of the change.

07Troubleshoot common SQLScript failures

Start with the exact database error, the procedure name, the parameter values used for the failing case, and the connection identity. Then isolate whether the failure occurs during compilation, invocation, authorization, data conversion, or resource consumption.

Compilation errors usually point to a syntax issue, an undeclared variable, an incompatible assignment, or an object that cannot be resolved. Compile the smallest affected statement and verify schema qualification for referenced tables and views.

Authorization errors require checking both the caller's EXECUTE privilege and the privileges needed by the procedure's security mode. Confirm the active user and schema in the same connection used for the call.

Conversion errors often arise when a string, numeric value, date, or decimal precision does not match the declared parameter or output type. Test boundary values and make conversions explicit at the point where the data enters the procedure.

Empty results can result from an unexpected schema, a null comparison, an overly restrictive predicate, or a transaction that has not been committed. Run the underlying query independently with the same connection and parameter values.

Slow execution should be investigated with the executed statement, input cardinality, joins, filters, and plan information. Avoid assuming that a procedure boundary itself is the bottleneck. The related SAP HANA developer performance tuning guide provides a broader workflow for locating expensive database operations.

08Deploy procedures safely

Store procedure source in version control and deploy it through a repeatable process. Include dependencies, schema names, required privileges, parameter contracts, test data assumptions, and rollback instructions with the change.

Use CREATE OR REPLACE PROCEDURE only when replacing the object is compatible with existing callers. A changed parameter type, output column, or security mode can break applications even when the procedure name remains the same. Treat the procedure signature as an API contract.

Deploy dependencies before the procedure that references them. After deployment, run a smoke test that checks compilation, authorization, representative results, empty-result behavior, and error handling. Record the deployment identity and timestamp in the change record.

For projects using database artifacts and automated delivery, the SAP HANA development CI/CD basics article covers the surrounding delivery workflow.

09Practical SQLScript checklist

Before promoting a SQLScript procedure, verify the following:

• The procedure has a clear purpose and a stable input and output contract.
• Parameters and output columns use deliberate data types and lengths.
• Set-based SQL is used for relational work wherever practical.
• Table variables contain only the columns needed by subsequent steps.
• The security mode matches the intended privilege model.
• Null, empty-result, boundary-value, and error cases have tests.
• Transaction ownership and rollback behavior are documented.
• Procedure source, dependencies, privileges, and deployment steps are versioned.
• A representative execution has been reviewed for runtime and resource usage.

A disciplined procedure design keeps SQLScript logic testable, secure, and maintainable while leaving analytical modeling to the appropriate database artifacts.