SAP HANA Tutorial: SQL Script

What is SQL Script?

SQL Script is a collection of extensions to SQL. It can be used in stored procedure in place of plain SQL. It determines the functional and procedural extensions.

In SQL Script user can define local variables for structure and tables that are primarily used for the creation of stored procedure.

SQL script can also be used in Calculation view. In SQL Script, there are two different logic containers-

• Procedure (Procedures allows you to describe a sequence of data transformations on data passed as input and database tables).
• User Defined Function (The User Defined Function container is separated into Scalar User Defined Function and Table User Defined Function).

SQL Script Language elements are as below –

Declarative SQL Script Logic (Functional Extension)

• It allows the definition of table types without referencing database tables.
• Typical Statement like SELECTs.
• Calculation Engine (CE) Functions.

Orchestration SQL Script Logic (Functional Extension)

Orchestration logic is used to implement data flow by using DDL, DML and SQL Query Statements and control flow logic using imperative language constructs such as loops and conditionals.

• Data Definition Language Statement. E.g. Create Schema.
• Data Manipulation Language (E.g. Insert).

Imperative SQL Script Logic (Procedural Extension)

Imperative logic splits the logic among several data flow. E.g. IF, ELSEIF, ELSE, CASE, FOR (Loop) and Exceptions.

Importance of SQL Script

Only SQL Script provides the necessary elements to migrate data-intensive logic or the operation of the application server to the database server.

Key points of SQL Script;

• SQL Script is executed and processed in the calculation engine within the HANA database.
• SQL Script is able to perform complex calculations.
• In SQL Script, a local variable can be declared to hold the interim result.
• SQL Script Procedure can return more result by using "OUTPUT Parameter" while Normal SQL Procedure can return only one.
• In SQL Script, you can define global or local tables types which can be used as parameters.

By using SQL Script, parallel processing mode can be achieved.