SAP HANA SQL Script Tutorial: Syntax & Examples

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.

SQL Script Procedure Example

A procedure is the most common container. The example below reads employee data, filters it, and returns the result through an output table parameter.

CREATE PROCEDURE DHK_SCHEMA.GET_HIGH_EARNERS (
  IN  im_min_salary DECIMAL(15,2),
  OUT ex_employees  TABLE (EMP_NAME NVARCHAR(100), SALARY DECIMAL(15,2)),
  OUT ex_row_count  INTEGER
)
LANGUAGE SQLSCRIPT
READS SQL DATA
AS
BEGIN
  -- Declarative logic: a table variable, not a physical table
  lt_all = SELECT EMP_NAME, SALARY
             FROM DHK_SCHEMA.EMPLOYEE
            WHERE SALARY >= :im_min_salary;

  -- Imperative logic: branch on the result
  SELECT COUNT(*) INTO ex_row_count FROM :lt_all;

  IF :ex_row_count = 0 THEN
    ex_employees = SELECT '' AS EMP_NAME, 0 AS SALARY FROM DUMMY WHERE 1 = 0;
  ELSE
    ex_employees = SELECT * FROM :lt_all ORDER BY SALARY DESC;
  END IF;
END;

Three conventions are worth noting. A colon prefixes any read of a variable, so :lt_all reads the table variable while lt_all on the left assigns to it. READS SQL DATA tells the optimiser the procedure has no side effects, which permits more aggressive parallelisation. And a procedure returning several output parameters is exactly the capability plain SQL lacks.

Calling it is straightforward:

CALL DHK_SCHEMA.GET_HIGH_EARNERS (50000, ?, ?);

Scalar and Table User Defined Functions

Where a procedure performs actions, a function returns a value and can be used inside a query. SQL Script offers two forms and they are not interchangeable.

-- Scalar function: returns one value
CREATE FUNCTION DHK_SCHEMA.ADD_BONUS (im_salary DECIMAL(15,2))
  RETURNS total DECIMAL(15,2)
  LANGUAGE SQLSCRIPT AS
BEGIN
  total := :im_salary * 1.10;
END;

SELECT EMP_NAME, DHK_SCHEMA.ADD_BONUS(SALARY) AS WITH_BONUS
  FROM DHK_SCHEMA.EMPLOYEE;

A table function is defined with RETURNS TABLE and is then queried in the FROM clause, which makes it the natural way to build a reusable, parameterised data set that a calculation view or report can consume.

SQL Script Best Practices

SQL Script performs well when written to let the engine parallelise, and badly when written like application code. Six habits make the difference.

1. Prefer declarative over imperative. A single set based statement outperforms a loop that processes rows one at a time, often by orders of magnitude. Reach for a FOR loop only when the logic genuinely cannot be expressed as a set operation.
2. Avoid cursors. They force row by row processing and defeat the calculation engine entirely. Almost every cursor can be rewritten as a join or an aggregation.
3. Keep statements independent. Two table variables that do not reference each other are computed in parallel. Chaining every step through the previous one serialises the whole procedure.
4. Declare READS SQL DATA where true. It signals that the procedure has no side effects, which allows more optimisation.
5. Filter as early as possible. Reducing the row count in the first table variable shrinks everything downstream, whereas filtering at the end has already paid the cost.
6. Migrate away from CE_ functions. They are deprecated, cannot be mixed freely with SQL in one statement, and the SQL optimiser now handles the same work better.

Statement level syntax is covered in SAP HANA SQL, the functions available inside a procedure in SAP HANA SQL functions, and the modelling objects that consume these procedures in the SAP HANA modeling tutorial.

FAQs