SAP HANA SQL Stored Procedure Tutorial

By : Mason Garcia Mason Garcia
Hours Updated

What are SQL Stored Procedures?

A procedure is a unit/module that perform a specific task. This procedure can be combined to form larger programs. This basically forms the ‘Modular Design’. A procedure can be invoked by another procedure which is called the calling program.

Procedures are re-useable processing block with a specific sequence of data transformation. The procedure can have multi-input/output parameters. The procedure can be created as read-only or read-write.

An SQL Procedure can be created at –

  • At Schema Level(Catalog Node)
  • At Package Level(Content Node)

Stored Procedure syntax in SAP HANA is as shown below –

SYNTAX

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 mention programming language <lang>.

SYNTAX ELEMENTS

ELEMENTS DESCRIPTION
<proc_name> Procedure Name
<parameter_clause> The parameter is defined here. IN, OUT, INOUT parameter is there. Each parameter is marked using the keywords IN/OUT/INOUT

• IN – Used for Pass Value To procedure as INPUT. It is Read Only parameter.

• OUT – Used for Return Value from Procedure as OUTPUT.

• INOUT – Used for Pass and Return Value To Procedure by same parameter.
LANGUAGE <Lang> Defines the programming language used in the procedure. Default: SQLSCRIPT
SQL SECURITY <mode> Specifies the security mode of the procedure. Default: DEFINER

• DEFINER – Specifies that the execution of the procedure is performed with the privileges of the definer of the procedure.

• INVOKER – Specifies that the execution of the procedure is performed with the privileges of the invoker of the procedure.
<default_schema_name> It defines the schema for unqualified objects in the procedure body. If nothing is define, then the current schema of the session is used for the procedure.
READS SQL DATA It marks the procedure as being read-only, it means the procedure does not modify the database data or its structure and that the procedure does not contain DDL or DML statements. This procedure only calls other read-only procedures.
WITH RESULT VIEW <view_name> It defines the result view to be used as the output of a read-only procedure.
If a result view is specified for a procedure, then it can be called by an SQL statement in the same process as a table or view.
SEQUENTIAL EXECUTION This statement will force sequential execution of the procedure logic. No parallelism takes place.
<procedure body> It defines the main body of the procedure based on the programming language selected.
HEADER ONLY If Header Only is used, then only procedure properties are created with OID.

You Might Like: