SAP HANA SQLScript Reference for SAP HANA Platform

https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94
Admin
This reference describes how to use the SQL extension SAP HANA SQLScript to embed data-intensive...
Tokens:29,506
Snippets:341
Trust Score:9.8
Update:2 months ago
Show doc for...
Tokens:
Raw
### CREATE PROCEDURE Example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/042ab4636cf34a9cb88dd61c808861a8.html?locale=en-US

An example of creating a procedure named GET_PROCEDURES that returns all procedure names on the database. This serves as a base for demonstrating ALTER PROCEDURE.

```sql
CREATE PROCEDURE GET_PROCEDURES(OUT procedures TABLE(schema_name NVARCHAR(256), name NVARCHAR(256)))

AS
BEGIN 
   procedures = SELECT schema_name AS schema_name, procedure_name AS name FROM PROCEDURES; 
END;
```

--------------------------------

### Setup Data Table

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7ed0d89b6aa84696a3ce8cf5a8517415.html?locale=en-US

Creates and populates a sample table for use in library demonstrations.

```SQL
create table data_table(col1 int);
 
do begin
  declare idx int = 0;
  for idx in 1..200 do
    insert into data_table values (:idx);
  end for;
end;
```

--------------------------------

### Call a function returning system variables

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0880cff747b249b780ca769e75365477.html?locale=en-US

Example of querying the function defined previously.

```sql
SELECT RETURN_NAME().schema_name, RETURN_NAME().name from dummy
```

--------------------------------

### Procedure Call Example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/659c8ff7cfdb42c89e27c638113bb13b.html?locale=en-US

Example of calling the addDiscount procedure with input and output variables.

```sql
CALL addDiscount (:lt_expensive_books, lt_on_sale);
```

--------------------------------

### Nested WHILE Loop Example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/6b2f29bbddcc431da72d9602a3049d93.html?locale=en-US

Demonstrates incrementing variables using nested WHILE loops.

```sql
CREATE PROCEDURE procWHILE (OUT V_INDEX2 INTEGER) LANGUAGE SQLSCRIPT
READS SQL DATA
AS
BEGIN
	DECLARE v_index1 INT = 0;
	WHILE :v_index1 < 5 DO
		v_index2 = 0;
		WHILE :v_index2 < 5 DO
			v_index2 = :v_index2 + 1;
		END WHILE;
		v_index1 = :v_index1 + 1;
	END WHILE;
END;
```

--------------------------------

### Open Cursor Example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/19f3c2bd8e0b451a9c53fb8c7be6c28d.html?locale=en-US

Opens a cursor named c_cursor1 while passing a specific string value as a parameter.

```sql
OPEN c_cursor1('978-3-86894-012-1');
```

--------------------------------

### Execute Procedure Results

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7ed0d89b6aa84696a3ce8cf5a8517415.html?locale=en-US

Execution examples for the procedure using the library with different input values.

```SQL
call myproc(10);
Result: 
count(*)
10
```

```SQL
call myproc(150);
Result:
count(*)
100
```

--------------------------------

### Dynamic SQL Execution Example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/093c4fd307064f838cb582555c187b9e.html?locale=en-US

Demonstrates using dynamic SQL to perform table operations including deletion, insertion, and selection.

```sql
CREATE TABLE tab (i int);

CREATE PROCEDURE proc_dynamic_result2(i int) AS
BEGIN
    EXEC 'DELETE from tab';
    EXEC 'INSERT INTO tab VALUES (' || :i || ')';
    EXECUTE IMMEDIATE 'SELECT * FROM tab ORDER BY i';
END;
```

--------------------------------

### Declare and Assign Scalar Variables

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/66b38a60ab3b475f925c224038511c51.html?locale=en-US

Example demonstrating variable declaration, initialization, and assignment within a procedure.

```SQL
CREATE PROCEDURE proc (OUT z INT) LANGUAGE SQLSCRIPT READS SQL DATA 
AS
BEGIN
	DECLARE a int;
	DECLARE b int = 0;
	DECLARE c int DEFAULT 0;
      
       t = select * from baseTable ;
       select count(*) into a from :t;
       b = :a + 1;
       z = :b + :c;
end;
```

--------------------------------

### Define Library DDL

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7ed0d89b6aa84696a3ce8cf5a8517415.html?locale=en-US

Example of creating a library containing a constant variable, a function, and a procedure.

```SQL
create library mylib as begin
  public variable maxval constant int = 100;
 
  public function bound_with_maxval(i int) returns x int as begin
    x = case when :i > :maxval then :maxval else :i end;
  end;
 
  public procedure get_data(in size int, out result table(col1 int)) as begin
    result = select top :size col1 from data_table;
  end;
end;
```

--------------------------------

### Handle Case-Sensitive Variable Names

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/66b38a60ab3b475f925c224038511c51.html?locale=en-US

Example showing how to reference case-sensitive variables using double quotes.

```SQL
DO BEGIN
DECLARE myvar INT = 0;
DECLARE “MyVar” INT = 1;
SELECT
:myvar       /* the first variable */,
:”MyVar”  /* the second variable */
from dummy;
END;
```

--------------------------------

### WHILE Loop with BETWEEN Predicate

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/6b2f29bbddcc431da72d9602a3049d93.html?locale=en-US

Example demonstrating the use of the BETWEEN operator within a WHILE loop condition.

```sql
CREATE FUNCTION between_03(x INT)
RETURNS result NVARCHAR(1) AS
BEGIN
  DECLARE idx INT = :x;
  result = 'O';
 
  WHILE :idx BETWEEN 5 AND 15 DO
    idx = :idx + 1;
    result = 'X';
  END WHILE;
END;
```

--------------------------------

### Demonstrate SUDF behavior in SAP HANA procedures

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/6f62839ff7b5413e8388e1f8f3f0921a.html?locale=en-US

Example showing the execution order of implicit SELECT statements and scalar user-defined functions within a procedure.

```sql
CREATE TABLE t1(C1 VARCHAR(20));
CREATE FUNCTION my_count RETURNS v_result INTEGER AS
BEGIN
    SELECT COUNT(*) INTO v_result FROM t1;
END;
CREATE PROCEDURE proc_insert_delete AS
BEGIN
    INSERT INTO t1 VALUES ('test');
    SELECT 'TRACE 1: COUNT AFTER INSERT', COUNT(*) FROM t1;
    SELECT 'TRACE 2: COUNT DURING FUNCTION CALL', my_count() FROM DUMMY;
    DELETE FROM t1;
    SELECT 'TRACE 3: COUNT AFTER DELETE', COUNT(*) FROM t1;
    COMMIT;
END;
CALL proc_insert_delete;
-- ('TRACE 1: COUNT AFTER INSERT', 1),
-- ('TRACE 2: COUNT DURING FUNCTION CALL', 1),
-- ('TRACE 3: COUNT AFTER DELETE', 0),
```

--------------------------------

### Basic ARRAY_AGG Usage

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/6649b3fcc87c4a1db20648aeaed5de13.html?locale=en-US

Example demonstrating the declaration of an array and a table variable, followed by the aggregation of a column into the array.

```sql
DECLARE id NVARCHAR(10) ARRAY; 
DECLARE tab TABLE (A NVARCHAR(10), B INTEGER);
tab = SELECT A , B FROM tab1;
id  = ARRAY_AGG(:tab.A);
```

--------------------------------

### ARRAY_AGG with ORDER BY Clause

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/6649b3fcc87c4a1db20648aeaed5de13.html?locale=en-US

Example showing how to sort the elements within the array using the ORDER BY clause.

```sql
id  = ARRAY_AGG(:tab.A ORDER BY B DESC);
```

--------------------------------

### Signal with Custom Message Text

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/2b525b310e2c42a68a677e2c99e27533.html?locale=en-US

This example illustrates how to signal a user-defined condition and simultaneously set a specific error message using SET MESSAGE_TEXT.

```SQL
SIGNAL invalid_input SET MESSAGE_TEXT = 'Invalid input arguments';
```

--------------------------------

### ARRAY_AGG with NULLS FIRST Ordering

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/6649b3fcc87c4a1db20648aeaed5de13.html?locale=en-US

Example demonstrating how to explicitly control the placement of NULL values in the resulting array using NULLS FIRST.

```sql
CREATE COLUMN TABLE CTAB (A NVARCHAR(10));
INSERT INTO CTAB VALUES ('A1');
INSERT INTO CTAB VALUES (NULL);
INSERT INTO CTAB VALUES ('A2');
INSERT INTO CTAB VALUES (NULL);

DO 
BEGIN
	DECLARE id NVARCHAR(10) ARRAY;
	tab = SELECT A FROM ctab;
	id  = ARRAY_AGG(:tab.A ORDER BY A DESC NULLS FIRST);
	
	tab2 = UNNEST(:id) AS (A);
	
	SELECT * FROM :tab2;
END;
```

--------------------------------

### Implement UPSERT Logic

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/46e875a0d4e446ae9ab08334f13cd1aa.html?locale=en-US

Example of using an IF statement to perform either an INSERT or an UPDATE based on record existence.

```sql
CREATE PROCEDURE upsert_proc (IN v_isbn VARCHAR(20)) 
LANGUAGE SQLSCRIPT AS
BEGIN
	DECLARE found INT = 1;
    SELECT count(*) INTO found FROM books WHERE isbn = :v_isbn;
    IF :found = 0 
	THEN
        INSERT INTO books
        VALUES (:v_isbn, 'In-Memory Data Management', 1, 1,
                '2011', 42.75, 'EUR');
    ELSE
        UPDATE books SET price = 42.75 WHERE isbn =:v_isbn;
    END IF;
END;
```

--------------------------------

### Signal User-Defined Condition

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/2b525b310e2c42a68a677e2c99e27533.html?locale=en-US

This example shows how to raise a previously declared user-defined condition named 'invalid_input'.

```SQL
SIGNAL invalid_input;
```

--------------------------------

### Convert Arrays to Table

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/4f12887c7941410d802a36c3f1a87a59.html?locale=en-US

Example of converting two arrays into a single table output parameter within a procedure.

```SQL
CREATE PROCEDURE ARRAY_UNNEST_SIMPLE(OUT rst TABLE(":ARR_ID" INT, ":ARR_NAME" NVARCHAR(10)))
READS SQL DATA
AS BEGIN
  DECLARE arr_id INTEGER ARRAY = ARRAY(1, 2);
  DECLARE arr_name NVARCHAR(10) ARRAY = ARRAY('name1', 'name2', 'name3');
  rst = UNNEST(:arr_id, :arr_name);
END;
```

--------------------------------

### Signal SQL_ERROR_CODE

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/2b525b310e2c42a68a677e2c99e27533.html?locale=en-US

This example demonstrates how to signal a specific SQL error code, 10000, which falls within the user-defined range.

```SQL
SIGNAL SQL_ERROR_CODE 10000;
```

--------------------------------

### Using IS_EMPTY in a Procedure

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/3905f921a16f41d6addcf8d7979560c2.html?locale=en-US

Example demonstrating the use of IS_EMPTY within an IF-statement inside a stored procedure.

```sql
CREATE PROCEDURE PROC_IS_EMPTY ( IN tabvar TABLE(ID INTEGER),
					   OUT outtab TABLE(ID INTEGER)
				        )
AS
BEGIN
	IF IS_EMPTY(:tabvar) THEN
          RETURN;	     
	END IF;
    CALL INTERNAL_LOGIC (:tabvar, outtab);
END;
```

--------------------------------

### WHILE Loop with Scalar UDF

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/6b2f29bbddcc431da72d9602a3049d93.html?locale=en-US

Example showing the use of a scalar User Defined Function within a WHILE loop condition.

```sql
CREATE PROCEDURE proc (in input1 INTEGER, out output1 TYPE1)  
AS
BEGIN
    DECLARE i INTEGER DEFAULT :input1;
    DECLARE cnt INTEGER DEFAULT 0;
    WHILE SUDF(:i) > 0 DO
        cnt = :cnt + 1;
        i = :i - 1;
    END WHILE;
    output1 = SELECT value FROM T1 where id = :cnt ;
END;
```

--------------------------------

### Use Scalar UDF in Condition

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/46e875a0d4e446ae9ab08334f13cd1aa.html?locale=en-US

Example showing how to use a scalar User Defined Function (UDF) within an IF condition.

```sql
CREATE PROCEDURE proc (in input1 INTEGER, out output1 TYPE1)
AS
BEGIN
    DECLARE i INTEGER DEFAULT :input1; 
    IF SUDF(:i) = 1 THEN
         output1 = SELECT value FROM T1;
    ELSEIF SUDF(:i) = 2 THEN
         output1 = SELECT value FROM T2;
    ELSE 
         output1 = SELECT value FROM T3;
    END IF;
END;
```

--------------------------------

### Automatic Read-Only DSQL in Procedures

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/093c4fd307064f838cb582555c187b9e.html?locale=en-US

Example showing how DSQL is automatically treated as read-only within a procedure defined with READS SQL DATA.

```sql
CREATE PROCEDURE Proc1(IN A NVARCHAR(12)) READS SQL DATA as
BEGIN
    EXEC 'SELECT * FROM ' || :A;
END
```

--------------------------------

### Using ROUTE_TO and NO_ROUTE_TO Hints

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/79a9f25f2e9a41e0a4cf03dc6b45a111.html?locale=en-US

Demonstrates applying routing hints to SELECT statements and querying the SQL plan cache to verify routing.

```sql
create table mytab(a int);
insert into mytab values(1);
 
select * from mytab with hint(ROUTE_TO('indexserver'));
select * from mytab with hint(ROUTE_TO('indexserver','computeserver'));
  
select * from mytab with hint(NO_ROUTE_TO('indexserver'));
select * from mytab with hint(NO_ROUTE_TO('indexserver','computeserver'));
 
select preferred_routing_volumes, * from sys.m_sql_plan_cache_ where statement_string like '%select * from mytab%';
```

--------------------------------

### Basic String Formatting with FORMAT

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0fd739184bc343dfae6190891965c383.html?locale=en-US

Illustrates basic string formatting using positional and named arguments.

```SQLScript
FORMAT('{} {}', 'one', 'two') => 'one two'
FORMAT('{1} {0}', 1, 2) => '2 1'
```

--------------------------------

### Use BETWEEN Operator

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/46e875a0d4e446ae9ab08334f13cd1aa.html?locale=en-US

Example of using the BETWEEN operator within an IF statement to check a range.

```sql
CREATE FUNCTION between_01(x INT)
RETURNS result NVARCHAR(1) AS
BEGIN
  IF :x BETWEEN 0 AND 100 THEN
    result = 'X';
  ELSE
    result = 'O';
  END IF;
END;
```

--------------------------------

### Procedure Using Library

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7ed0d89b6aa84696a3ce8cf5a8517415.html?locale=en-US

Demonstrates how to reference library members within a procedure using the USING clause.

```SQL
create procedure myproc (in inval int) as begin
  using mylib as mylib;
  declare var1 int = mylib:bound_with_maxval(:inval);
 
  if :var1 > mylib:maxval then
    select 'unexpected' from dummy;
  else
    declare tv table (col1 int);
    call mylib:get_data(:var1, tv);
    select count(*) from :tv;
  end if;
end;
```

--------------------------------

### Formatting to Array and Table

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0fd739184bc343dfae6190891965c383.html?locale=en-US

Shows how to use FORMAT_TO_ARRAY and FORMAT_TO_TABLE to convert table data into formatted strings.

```SQLScript
DO BEGIN
    USING SQLSCRIPT_STRING AS LIB;
    DECLARE arr NVARCHAR(5000) ARRAY;
    declare tv table(result NVARCHAR(5000));
    --tt: [('John', 'Sutherland', 1988), ('Edward','Stark',1960)]
    DECLARE tt TABLE (first_name NVARCHAR(100), last_name NVARCHAR(100), birth_year INT);
    tt.first_name[1] = 'John';
    tt.last_name[1] = 'Sutherland';
    tt.birth_year[1] = 1988;
     
    tt.first_name[2] = 'Edward';
    tt.last_name[2] = 'Stark';
    tt.birth_year[2] = 1960;
     
    arr = LIB:FORMAT_TO_ARRAY('{first_name} {last_name} was born in {birth_year}', :tt);
    --['John Sutherland was born in 1988', 'Edward Stark was born in 1960']
     
    tv = LIB:FORMAT_TO_TABLE('{first_name} {last_name} was born in {birth_year}', :tt);
    --tv: [('John Sutherland was born in 1988'), ('Edward Stark was born in 1960')]
END;
```

--------------------------------

### Create Database Objects for Dependency Testing

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/38608b6773a6423986785de97d0d1ea8.html?locale=en-US

Initializes a schema, a table type, two tables, and two procedures to serve as a basis for dependency queries.

```sql
CREATE SCHEMA deps;
CREATE TYPE mytab_t AS TABLE (id int, key_val int, val int);
CREATE TABLE mytab1 (id INT PRIMARY KEY, key_val int, val INT);
CREATE TABLE mytab2 (id INT PRIMARY KEY, key_val int, val INT);

CREATE PROCEDURE deps.get_tables(OUT outtab1 mytab_t, OUT outtab2 mytab_t) LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
    outtab1 = SELECT * FROM mytab1;
    outtab2 = SELECT * FROM mytab2;
END;

CREATE PROCEDURE deps.my_proc (IN val INT, OUT outtab mytab_t) LANGUAGE SQLSCRIPT READS SQL DATA 
AS
BEGIN
    CALL deps.get_tables(tab1, tab2);
    IF :val > 1 THEN
        outtab = SELECT * FROM :tab1;
    ELSE
        outtab = SELECT * FROM :tab2;
    END IF;
END;
```

--------------------------------

### Create a User-Defined Library

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7cd14f1931404738a05c5e93e22564af.html?locale=en-US

Defines a new library structure with optional language and schema specifications.

```SQL
CREATE [OR REPLACE] LIBRARY <lib_name>
[LANGUAGE SQLSCRIPT] [DEFAULT SCHEMA <default_schema_name>]
AS BEGIN
  [<lib_var_decl_list>]
  [<lib_proc_func_list>]
END;
```

--------------------------------

### Implementing SQLSCRIPT_LOGGING in a Procedure

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/804d96051c14464d8596d2aa9b5ca3c6.html?locale=en-US

Demonstrates creating a procedure that utilizes the SQLSCRIPT_LOGGING library and granting the necessary privileges.

```sql
connect sqlscript_logging_user_a password Dummy1234;
create procedure p1 sql security invoker as begin
    using SQLSCRIPT_LOGGING as LIB;
    call LIB:LOG('error', 'sqlscript', 'hello world');
end;
  
grant execute, sqlscript logging on p1 to SYSTEM;
```

--------------------------------

### Create a Procedure for Plan Analysis

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7aabea5031134d2192f7022bc390fce6.html?locale=en-US

Defines a sample procedure containing conditional and loop logic to be used for plan explanation.

```sql
CREATE PROCEDURE proc_p1(a int) as
begin
    declare i int default 0;    
    create table tab1 (attr1 int);
    if a > 0 then
        select 5 from dummy;
    else
        select 10 from dummy;
    end if;
    while i < 10 do
        insert into tab1 values (1);
        i := i + 1;
    end while;
    drop table tab1;
end;
```

--------------------------------

### EXECUTE IMMEDIATE Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/093c4fd307064f838cb582555c187b9e.html?locale=en-US

The general syntax for the EXECUTE IMMEDIATE statement.

```sql
EXECUTE IMMEDIATE '<sql-statement>' [INTO <var_name_list> [DEFAULT <scalar_expr_list>]] [USING <expression_list>] [READS SQL DATA]
```

--------------------------------

### Truncating and Padding Strings with FORMAT

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0fd739184bc343dfae6190891965c383.html?locale=en-US

Shows how to truncate strings to a specific precision and pad them to a certain width.

```SQLScript
FORMAT('{:.5}', 'xylophone') =>'xylop'
FORMAT('{:10.5}', 'xylophone') => 'xylop '
```

--------------------------------

### Formatting Numbers with FORMAT

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0fd739184bc343dfae6190891965c383.html?locale=en-US

Demonstrates various integer and floating-point number formatting options including decimal, general, and exponent notation.

```SQLScript
FORMAT('{:d}', 42) => '42'
FORMAT('{:f}', 3.141592653589793) => '3.141593'
FORMAT('{:g}', 123456) => '123456'
FORMAT('{:g}', 1234567) => '1.23456e+06'
FORMAT('{:g}', 0.000123456) => '0.000123456'
FORMAT('{:g}', 0.0000123456) => '1.23456e-05'
```

--------------------------------

### SQL Syntax for ROUTE_TO and Related Hints

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/79a9f25f2e9a41e0a4cf03dc6b45a111.html?locale=en-US

Defines the syntax for routing hints including service types and parameter-based routing.

```sql
<servicetype> ::= 'indexserver' | 'xsengine' | 'scriptserver' | 'dpserver' | 'computeserver'

<hint_with_parameters> ::= ROUTE_TO( <volume_id> [{, <volume_id> }] )

                           | ROUTE_TO( '<servicetype>' [{, '<servicetype>' }] )

                           | NO_ROUTE_TO( <volume_id> [{, <volume_id> }] )

                           | NO_ROUTE_TO( '<servicetype>' [{, '<servicetype>' }] )

                           | ROUTE_BY( <table_name> [{, <table_name>}] )

                           | ROUTE_BY_CARDINALITY( <table_name> [{, <table_name>}] )

                           | DATA_TRANSFER_COST ({0 | 1})
```

--------------------------------

### Splitting Strings to Arrays and Tables

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0fd739184bc343dfae6190891965c383.html?locale=en-US

Shows how to split strings into arrays or single-column tables using delimiters or regular expressions.

```SQLScript
DO BEGIN
    USING SQLSCRIPT_STRING AS LIB;
    DECLARE arr INT ARRAY;
    DECLARE arr2 STRING ARRAY;
    DECLARE tv, tv2 TABLE(RESULT NVARCHAR(5000));

    arr = LIB:SPLIT_TO_ARRAY('10,20,30,40,50',','); --array(10,20,30,40,50)
    arr2 = LIB:SPLIT_REGEXPR_TO_ARRAY('Blake Kelly; Fred Randall; Bell Walsh; Leonard Quinn; Chris McDonald', '\s*;\s*'); --array('Blake Kelly', 'Fred Randall', 'Bell Walsh', 'Leonard Quinn', 'Chris McDonald')
    tv = LIB:SPLIT_TO_TABLE('10,20,30,40,50',','); --table[(10),(20),(30),(40),(50)]
    tv2 = LIB:SPLIT_REGEXPR_TO_TABLE('10+20/30*40-50', '\+|\/|\*|-'); --table[(10),(20),(30),(40),(50)]
END;
```

--------------------------------

### Assign Scalar UDF Results

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/66b38a60ab3b475f925c224038511c51.html?locale=en-US

Examples of assigning scalar User Defined Function results to variables using SQL statements or direct assignment.

```SQL
DECLARE i INTEGER DEFAULT 0;
SELECT SUDF_ADD(:input1, :input2) into i from dummy;
```

```SQL
DECLARE i INTEGER DEFAULT 0;
i = SUDF_ADD(:input1, :input2);
```

```SQL
DECLARE i INTEGER DEFAULT 0;
DECLARE j NVARCHAR(5);
(i,j) = SUDF_EXPR(:input1);

DECLARE a INTEGER DEFAULT 0;
a = SUDF_EXPR(:input1).x;
```

--------------------------------

### Define a function using system variables

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0880cff747b249b780ca769e75365477.html?locale=en-US

Demonstrates assigning system variables to return values within a function.

```sql
CREATE FUNCTION RETURN_NAME ()
RETURNS name        nvarchar(256), 
        schema_name nvarchar(256)
AS
BEGIN
	 name        = ::CURRENT_OBJECT_NAME;
	 schema_name = ::CURRENT_OBJECT_SCHEMA;
END;
```

--------------------------------

### Retrieve EXPLAIN PLAN Results

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7aabea5031134d2192f7022bc390fce6.html?locale=en-US

Queries the EXPLAIN_CALL_PLANS table to view the generated plan information for a specific statement name.

```sql
SELECT * FROM EXPLAIN_CALL_PLANS WHERE STATEMENT_NAME = 'test';
```

--------------------------------

### Formatting Column Values with FORMAT_TO_TABLE

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0fd739184bc343dfae6190891965c383.html?locale=en-US

Shows how to format values from table columns using FORMAT_TO_TABLE with different format specifiers.

```SQLScript
tv = select 1 as first, 2 as last from dummy;
FORMAT_TO _TABLE('{first} {last}', :tv) => [('1 2')]
FORMAT_TO _TABLE('{first:04d} {last:02d}', :tv) => [('0001 02')]
```

--------------------------------

### Input Parameters and Nested Embedded Functions

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/6f1260eff10a4ba7a159b44848b16ab4.html?locale=en-US

Shows how to pass input parameters to an embedded function and how to nest them.

```sql
-- input parameter
select a from
  sql function (in a int => 1)
  returns table (a int)
  begin
    return select :a as a from dummy;
  end;
 
-- nested SQL FUNCTION clause
select a from
  sql function
  returns table (a int)
  begin
    return select * from
      sql function
      returns table (a int)
      begin
        return select 1 as a from dummy;
      end;
  end;
```

--------------------------------

### CREATE LIBRARY

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7cd14f1931404738a05c5e93e22564af.html?locale=en-US

Creates a new user-defined library containing variables, procedures, and functions.

```APIDOC
## CREATE LIBRARY

### Description
Creates a new user-defined library in the database.

### Syntax
```sql
CREATE [OR REPLACE] LIBRARY <lib_name>
[LANGUAGE SQLSCRIPT] [DEFAULT SCHEMA <default_schema_name>]
AS BEGIN
  [<lib_var_decl_list>]
  [<lib_proc_func_list>]
END;
```
```

--------------------------------

### Splitting Strings with SPLIT

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0fd739184bc343dfae6190891965c383.html?locale=en-US

Demonstrates splitting a string by a delimiter and assigning parts to variables. Handles cases with incorrect number of values.

```SQLScript
DO BEGIN
    USING SQLSCRIPT_STRING AS LIB;
    DECLARE a1, a2, a3 INT;
    (a1, a2, a3) = LIB:SPLIT('10, 20, 30', ', '); --(10, 20, 30)
END;
```

```SQLScript
DO BEGIN
    USING SQLSCRIPT_STRING AS LIB;
    DECLARE first_name, last_name STRING;
    DECLARE area_code, first_num, last_num INT;
    first_name = LIB:SPLIT('John Sutherland', ','); --('John Sutherland')
    (first_name, last_name) = LIB:SPLIT('John Sutherland', ' '); --('John','Sutherland')
    first_name = LIB:SPLIT('Brian', ' '); --('Brian')
    (first_name, last_name) = LIB:SPLIT('Brian', ' '); -- throw SQL_FEW_VALUES
    (first_name, last_name) = LIB:SPLIT('Michael Forsyth Jr', ' ');--throw SQL_MANY_VALUES
    (first_name, last_name) = LIB:SPLIT('Michael Forsyth Jr', ' ', 1); --('Michael', 'Forsyth Jr')
    (area_code, first_num, last_num) = LIB:SPLIT_REGEXPR('02)2143-5300', '\(|\)|-'); --(02, 2143, 5300)
END;
```

--------------------------------

### OPEN Cursor Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/19f3c2bd8e0b451a9c53fb8c7be6c28d.html?locale=en-US

Basic syntax for opening a cursor with an optional argument list.

```sql
OPEN <cursor_name>[(<argument_list>)]
```

--------------------------------

### Parameter Type Definitions

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/659c8ff7cfdb42c89e27c638113bb13b.html?locale=en-US

Definitions for various input and output parameter types.

```sql
<param>::= <in_table_param> | <in_scalar_param> |<out_scalar_param> | <out_table_param>| <inout_scalar_param>
```

```sql
<in_table_param> ::= <in_param>
<in_scalar_param> ::= <in_param>|<scalar_value>|<expression>
```

```sql
<in_param> ::= :<identifier>
```

```sql
<out_param> ::= <identifier>
```

```sql
<out_scalar_param> ::= <out_param>
<out_table_param> ::= <out_param>
```

```sql
<inout_scalar_param> ::= <out_param>
```

--------------------------------

### Library Member Syntax Definitions

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7ed0d89b6aa84696a3ce8cf5a8517415.html?locale=en-US

Formal BNF-style syntax definitions for library members, assignments, and procedure calls within SQLScript.

```SQL
<procedure_body> ::= [<proc_using_list>] [<proc_handle_list>] <proc_stmt_list>
<proc_using_list> ::= {<proc_using>}...
<proc_using> ::= USING <lib_name> AS <lib_alias> ;
<lib_name> ::= [<schema_name>.]<identifier>
<lib_alias> ::= <identifier>
<lib_member_ref> ::= [ <schema_name> . ] <identifier> ':' <member_name>
 
 
<proc_assign> ::= <variable_name> = { <expression> | <array_function> | <lib_member_func_call>} ;
                | <variable_name> '[' <expression> ']' = { <expression> | <lib_member_func_call> } ;
                | <lib_member_ref> = { <expression> | <lib_member_func_call> } ;
<lib_member_func_call> ::= <lib_member_ref> ( [<expression> [ {, <expression> }...] ] )
 
 
<proc_call> ::= CALL <proc_name> ( <param_list> ) ;
              | CALL <lib_member_ref> ( <param_list> ) ;
```

--------------------------------

### Comparison of SQL Query Approaches

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/6f1260eff10a4ba7a159b44848b16ab4.html?locale=en-US

Comparison between standard SQL, traditional SQLScript functions, and embedded SQLScript functions.

```sql
SELECT
        A, B, SUM(C)
FROM
   (SELECT 1 as A, 2 as B, 3 as C FROM DUMMY
UNION ALL
    SELECT 1 as A, 2 as B, 4 as C FROM DUMMY
UNION ALL
    SELECT 2 as A, 3 as B, 2 as C FROM DUMMY
UNION ALL
    SELECT 2 as A, 3 as B, 4 as C FROM DUMMY
UNION ALL
    SELECT 2 as A, 5 as B, 7 as C FROM DUMMY)
GROUP BY A, B
ORDER BY A, B;
```

```sql
CREATE FUNCTION TEMP_FUNC()
RETURNS TABLE (A INT, B INT, C INT)
AS BEGIN
        DECLARE buffer TABLE (A INT, B INT, C INT);
        :buffer.insert((1, 2, 3));
        :buffer.insert((1, 2, 4));
        :buffer.insert((2, 3, 2));
        :buffer.insert((2, 3, 4));
        :buffer.insert((2, 5, 7));
        RETURN :buffer;
END;

SELECT
        A, B, SUM(C)
FROM
        TEMP_FUNC()
GROUP BY A, B
ORDER BY A, B;
```

```sql
SELECT
        A, B, SUM(C)
FROM
        SQL FUNCTION
        RETURNS TABLE (A INT, B INT, C INT)
        BEGIN
               DECLARE buffer TABLE (A INT, B INT, C INT);
               :buffer.insert((1, 2, 3));
               :buffer.insert((1, 2, 4));
               :buffer.insert((2, 3, 2));
               :buffer.insert((2, 3, 4));
               :buffer.insert((2, 5, 7));
               RETURN :buffer;
        END
GROUP BY A, B
ORDER BY A, B;
```

--------------------------------

### Create message_box table

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/4f169bfccedc4e6ea100216652f84370.html?locale=en-US

Initializes the table required to store messages and timestamps for the ins_msg_proc procedure.

```sql
CREATE TABLE message_box (p_msg VARCHAR(200), tstamp TIMESTAMP);
```

--------------------------------

### Execute EXPLAIN PLAN for a Procedure Call

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7aabea5031134d2192f7022bc390fce6.html?locale=en-US

Generates the execution plan for a specific procedure call and assigns it a statement name.

```sql
EXPLAIN PLAN SET STATEMENT_NAME = 'test' FOR CALL proc_p1(1);
```

--------------------------------

### Syntax for BIND_AS_PARAMETER and BIND_AS_VALUE

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0b2958ee0426496f9c084c92b14993f1.html?locale=en-US

Syntax definition for explicitly controlling parameterization.

```SQL
<bind_as_function> ::= BIND_AS_PARAMETER ( <scalar_variable> )|
                       BIND_AS_VALUE(<scalar_variable> )
```

--------------------------------

### As Column Names Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5fff250e99f6.html?locale=en-US

Syntax for specifying column names for the returned table.

```SQL
<as_col_names>     ::= AS [table_name] ( <column_name_list> ) 
<column_name_list> ::= <column_name>[{, <column_name>}...]
<column_name>      ::= <identifier>
```

--------------------------------

### Padding and Signed Number Formatting with FORMAT

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0fd739184bc343dfae6190891965c383.html?locale=en-US

Illustrates padding numbers with leading zeros or spaces and displaying signed numbers.

```SQLScript
FORMAT('{:4d}', 42) => ' 42'
FORMAT('{:06.2f}', 3.141592653589793) => '003.14'
FORMAT('{:04d}', 42) => '0042'
FORMAT('{:+d}', 42) => '+42'
FORMAT('{: d}', -23) => '-23'
FORMAT('{: d}', 42) => ' 42'
```

--------------------------------

### EXECUTE IMMEDIATE

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/093c4fd307064f838cb582555c187b9e.html?locale=en-US

Executes a dynamic SQL statement provided as a string, with support for input/output variables and read-only constraints.

```APIDOC
## EXECUTE IMMEDIATE

### Description
Executes the SQL statement passed in a string argument. Results of queries are appended to the procedure's result iterator unless assigned via the INTO clause.

### Syntax
EXECUTE IMMEDIATE '<sql-statement>' [INTO <var_name_list> [DEFAULT <scalar_expr_list>]] [USING <expression_list>] [READS SQL DATA]

### Parameters
- **<sql-statement>** (string) - Required - The SQL statement to be executed.
- **INTO <var_name_list>** (list) - Optional - Assigns result sets to variables.
- **USING <expression_list>** (list) - Optional - Passes scalar and table values into the statement.
- **READS SQL DATA** (keyword) - Optional - Declares the statement as read-only for optimization and context validation.
```

--------------------------------

### Library Name Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7cd14f1931404738a05c5e93e22564af.html?locale=en-US

Defines the naming convention for libraries including optional schema qualification.

```SQL
<lib_name> ::= [<schema_name>.]<identifier>;
```

--------------------------------

### Query function via synonym

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0880cff747b249b780ca769e75365477.html?locale=en-US

Demonstrates that system variables return the original object name even when accessed via a synonym.

```sql
CREATE SYNONYM SYN_FOR_FUNCTION FOR RETURN_NAME;

SELECT SYNONYM_FOR_FUNCTION().schema_name, SYNONYM_FOR_FUNCTION().name FROM dummy;
```

--------------------------------

### Converting Table to Formatted String with TABLE_SUMMARY

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0fd739184bc343dfae6190891965c383.html?locale=en-US

Demonstrates using TABLE_SUMMARY to serialize a table into a human-friendly formatted string, with an option to limit the number of records.

```SQLScript
CREATE TABLE SAMPLE1(NAME nvarchar(32), AGE INT);
INSERT INTO SAMPLE1 VALUES ('John Bailey', 28);
INSERT INTO SAMPLE1 VALUES ('Kevin Lawrence', 56);
INSERT INTO SAMPLE1 VALUES ('Leonard Poole', 31);
INSERT INTO SAMPLE1 VALUES ('Vanessa Avery', 16);
 
DO
BEGIN
  USING SQLSCRIPT_STRING AS STRING;
  USING SQLSCRIPT_PRINT AS PRINT;
  T1 = SELECT * FROM SAMPLE1;
  PRINT:PRINT_LINE(STRING:TABLE_SUMMARY(:T1, 3));
END;

------------------------
NAME,AGE
John Bailey,28
Kevin Lawrence,56
Leonard Poole,31
```

--------------------------------

### EXPLAIN PLAN

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7aabea5031134d2192f7022bc390fce6.html?locale=en-US

Analyzes the execution plan of a procedure call and stores the results in the EXPLAIN_CALL_PLANS table.

```APIDOC
## EXPLAIN PLAN

### Description
Provides information about the compiled plan of a given procedure. The results are saved in the EXPLAIN_CALL_PLANS table.

### Syntax
EXPLAIN PLAN [SET STATEMENT_NAME = <statement_name>] FOR <explain_plan_entry>

### Parameters
- **statement_name** (string_literal) - Optional - Specifies the name of a specific execution plan in the output table.
- **explain_plan_entry** (call_statement | SQL PLAN CACHE ENTRY <plan_id>) - Required - Specifies the entry to explain, either a procedure call or a plan cache ID.

### Example
EXPLAIN PLAN SET STATEMENT_NAME = 'test' FOR CALL proc_p1(1);

### Retrieving Results
SELECT * FROM EXPLAIN_CALL_PLANS WHERE STATEMENT_NAME = 'test';
```

--------------------------------

### SQL Data Type Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5fff250e99f6.html?locale=en-US

Lists the supported primitive SQL types for variables.

```SQL
<sql_type> ::= DATE | TIME | TIMESTAMP | SECONDDATE | TINYINT | SMALLINT | INTEGER | BIGINT | DECIMAL | SMALLDECIMAL | REAL | DOUBLE | 
              | VARCHAR | NVARCHAR | ALPHANUM | VARBINARY | CLOB | NCLOB | BLOB | ST_GEOMETRY
```

--------------------------------

### Parameter Clause Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5fff250e99f6.html?locale=en-US

Defines the structure for input and output parameters.

```SQL
<parameter_clause> ::= <parameter> [{, <parameter>}...]
```

--------------------------------

### Create Procedure with Explicit Binding Functions

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0b2958ee0426496f9c084c92b14993f1.html?locale=en-US

Uses BIND_AS_PARAMETER and BIND_AS_VALUE to override optimizer decisions.

```SQL
CREATE PROCEDURE PROC (IN var1 INT, IN var2 INT, OUT tab mytab)
 AS
 BEGIN
    tab = SELECT * FROM MYTAB WHERE MYCOL > BIND_AS_PARAMETER(:var1) 
                                 OR MYCOL = BIND_AS_VALUE(:var2);
 END;
```

--------------------------------

### Unnest Table Variables

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/4f12887c7941410d802a36c3f1a87a59.html?locale=en-US

Demonstrates unnesting table variables with projection aliasing and column selection.

```SQL
create column table tab0(a int);
insert into tab0 values(1);
insert into tab0 values(2);
insert into tab0 values(3);
 
do begin
  t0 = select * from tab0 order by a asc;
  t1 = select * from tab0 order by a desc;
  lt = unnest(:t0, :t1) as (*, (a as b));
  select * from :lt;
end;
 
-- expected result {1, 3}, {2, 2}, {3, 1}
 
do begin
  t0 = select * from tab0 order by a asc;
  t1 = select * from tab0 order by a desc;
  lt = unnest(:t0, :t1) as (*, (a as b, a as c));
  select * from :lt;
end;
 
-- expected result {1, 3, 3}, {2, 2, 2}, {3, 1, 1}
```

--------------------------------

### CREATE PROCEDURE Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5fff250e99f6.html?locale=en-US

The primary syntax for defining a new procedure in SAP HANA.

```SQL
CREATE [OR REPLACE] PROCEDURE <proc_name> [(<parameter_clause>)] [LANGUAGE <lang>] [SQL SECURITY <mode>] [DEFAULT SCHEMA <default_schema_name>]
 [READS SQL DATA ] [<variable_cache_clause>] [ DETERMINISTIC ] [WITH ENCRYPTION] [AUTOCOMMIT DDL { ON|OFF } ]
 AS
 { BEGIN [ SEQUENTIAL EXECUTION | PARALLEL EXECUTION ]
  <procedure_body>
 END | HEADER ONLY }
```

--------------------------------

### Formatting Strings with Scalar Arguments

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0fd739184bc343dfae6190891965c383.html?locale=en-US

Demonstrates using the FORMAT function with scalar variables for string interpolation and type-specific formatting.

```SQLScript
DO BEGIN
    USING SQLSCRIPT_STRING AS LIB;
    DECLARE your_name STRING = LIB:FORMAT('{} {}', 'John', 'Sutherland'); --'John Sutherland'
    DECLARE name_age STRING = LIB:FORMAT('{1} {0}', 30, 'Sutherland'); --'Sutherland 30'
    DECLARE pi_str STRING = LIB:FORMAT('PI: {:06.2f}', 3.141592653589793); --'PI: 003.14'
DECLARE ts STRING = LIB:FORMAT('Today is {}', TO_VARCHAR (current_timestamp, 'YYYY/MM/DD')); --'Today is 2017/10/18'
    DECLARE scores double ARRAY = ARRAY(1.4, 2.1, 40.3);
    DECLARE score_str STRING = LIB:FORMAT('{}-{} -{}', :scores); --'1.4-2.1-40.3'
END;
```

--------------------------------

### CREATE PROCEDURE

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5fff250e99f6.html?locale=en-US

Creates a new procedure using the specified programming language.

```APIDOC
## CREATE PROCEDURE

### Description
The CREATE PROCEDURE statement creates a procedure by using the specified programming language <lang>.

### Syntax
CREATE PROCEDURE <proc_name> LANGUAGE <lang> AS BEGIN ... END;

### Example
```sql
CREATE PROCEDURE orchestrationProc
 LANGUAGE SQLSCRIPT AS
 BEGIN
   DECLARE v_id BIGINT;
   DECLARE v_name VARCHAR(30);
   DECLARE  v_pmnt BIGINT;
   DECLARE v_msg VARCHAR(200);
   DECLARE CURSOR c_cursor1 (p_payment BIGINT) FOR
     SELECT id, name, payment FROM control_tab
       WHERE payment > :p_payment ORDER BY id ASC;
   CALL init_proc();
   OPEN c_cursor1(250000);
   FETCH c_cursor1 INTO v_id, v_name, v_pmnt; v_msg = :v_name || ' (id ' || :v_id || ') earns ' || :v_pmnt || ' $.';
   CALL ins_msg_proc(:v_msg);
   CLOSE c_cursor1;
 END;
```
```

--------------------------------

### Dynamic SQL Execution

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5fff250e99f6.html?locale=en-US

Syntax for executing dynamic SQL strings.

```SQL
<proc_exec> ::= {EXEC | EXECUTE IMMEDIATE} <proc_expr> ;
```

--------------------------------

### Parameter List Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/659c8ff7cfdb42c89e27c638113bb13b.html?locale=en-US

Syntax for defining the parameter list in a procedure call.

```sql
<param_list> ::= <param>[{, <param>}...]
```

--------------------------------

### IF Statement Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/46e875a0d4e446ae9ab08334f13cd1aa.html?locale=en-US

The basic structure for conditional branching in SQLScript.

```sql
IF <bool_expr1> THEN
    <then_stmts1>;
[{ELSEIF <bool_expr2> THEN
    <then_stmts2>;}...]
[ELSE
    <else_stmts3>;]
END IF;
```

--------------------------------

### Cursor Operations

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5fff250e99f6.html?locale=en-US

Syntax for opening, fetching from, and closing cursors.

```SQL
<proc_open>  ::= OPEN <cursor_name> [ <open_param_list>] ;

<proc_fetch> ::= FETCH <cursor_name> INTO <column_name_list> ;

<proc_close> ::= CLOSE <cursor_name> ;
```

--------------------------------

### Default Schema Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5fff250e99f6.html?locale=en-US

Specifies the default schema for unqualified objects.

```SQL
DEFAULT SCHEMA <default_schema_name>
<default_schema_name> ::= <unicode_name>
```

--------------------------------

### Query All Object Dependencies

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/38608b6773a6423986785de97d0d1ea8.html?locale=en-US

Retrieves all direct and indirect base objects associated with the specified procedure.

```sql
SELECT * FROM OBJECT_DEPENDENCIES WHERE dependent_object_name = 'MY_PROC' and dependent_schema_name = 'DEPS';
```

--------------------------------

### Library Variable Declaration Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7cd14f1931404738a05c5e93e22564af.html?locale=en-US

Specifies the syntax for declaring public or private variables within a library.

```SQL
<lib_var_decl_list> ::= <lib_var_decl> [{<lib_var_decl>}...]
<lib_var_decl> ::= <access_mode> <var_decl> ;
<var_decl> ::= VARIABLE <member_name> [CONSTANT] <sql_type> [NOT NULL] [<proc_default>]
 
<access_mode> ::= PUBLIC | PRIVATE
<member_name> ::= <identifier>
<proc_default> ::= { DEFAULT | '=' } <expression>
```

--------------------------------

### Table Type Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5fff250e99f6.html?locale=en-US

References a previously defined table type.

```SQL
<table_type> ::= <identifier>
```

--------------------------------

### Library Procedure and Function Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7cd14f1931404738a05c5e93e22564af.html?locale=en-US

Defines the syntax for including procedures and functions within a library with access control.

```SQL
<lib_proc_func_list> ::= <lib_proc_func> [{<lib_proc_func>}...]
<lib_proc_func> ::= <access_mode> <proc_func_def> ;
<proc_func_def> ::= <proc_def> | <func_def>
 
<proc_def> ::= PROCEDURE <member_name> [<parameter_clause>] [<proc_property>] AS BEGIN [SEQUENTIAL EXECUTION] <procedure_body> END
<proc_property> ::= [LANGUAGE <lang>] [SQL SECURITY <mode>] [READS SQL DATA]
 
<func_def> ::= FUNCTION <member_name> [<parameter_clause>] RETURNS <return_type> [<func_property>] AS BEGIN <function_body> END
<func_property> ::= [LANGUAGE <lang>] [SQL SECURITY <mode>] [READS SQL DATA]
```

--------------------------------

### Call Procedure with Query Parameters

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0b2958ee0426496f9c084c92b14993f1.html?locale=en-US

Executes the procedure using query parameters, allowing for query plan reuse.

```SQL
CALL PROC (var1=>?, var2=>?, mytab=>?)
```

--------------------------------

### Prepared Nested Query

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0b2958ee0426496f9c084c92b14993f1.html?locale=en-US

The resulting query string when using query parameters.

```SQL
SELECT * FROM MYTAB WHERE MYCOL >? OR MYCOL =?
```

--------------------------------

### Define Procedure Blocks

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5fff250e99f6.html?locale=en-US

Syntax for nesting sections of procedures using BEGIN and END.

```SQL
<proc_block> ::= BEGIN <proc_block_option>
			      [<proc_decl_list>]
		      	[<proc_handler_list>]
		      	<proc_stmt_list>
                 END ;

<proc_block_option> ::=  [SEQUENTIAL EXECUTION ]| [AUTONOMOUS TRANSACTION] | [PARALLEL EXECUTION]
```

--------------------------------

### Pass system variables as arguments

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0880cff747b249b780ca769e75365477.html?locale=en-US

Shows passing system variables into another function or procedure call.

```sql
CREATE FUNCTION GET_FULL_QUALIFIED_NAME (schema_name nvarchar(256),name nvarchar(256))
RETURNS fullname nvarchar(256)
AS
BEGIN
	 fullname = schema_name || '.' || name ;
END;

CREATE PROCEDURE MAIN_PROC (IN INPUT_VALUE INTEGER)
AS
BEGIN
	 DECLARE full_qualified_name NVARCHAR(256);
	 DECLARE error_text NVARCHAR(256);
	 full_qualified_name = get_full_qualified_name (::CURRENT_OBJECT_SCHEMA,    
                                                      ::CURRENT_OBJECT_NAME);
	 
	 IF :input_value > 1 OR :input_value < 0 THEN
	 	SIGNAL SQL_ERROR_CODE 10000 SET MESSAGE_TEXT =  'ERROR IN ' 
            || :full_qualified_name || ': invalid input value ';
	 END IF;

END;
```

--------------------------------

### Embedded Function Usage and Read-Only Constraints

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/6f1260eff10a4ba7a159b44848b16ab4.html?locale=en-US

Demonstrates basic usage and the read-only constraint that prevents DDL operations like CREATE TABLE inside the function.

```sql
select sum(a) from
sql function
returns table (a int, b int)
begin
  declare t table(a int, b int);
  :t.insert((1, 2));
  :t.insert((1, 3));
  :t.insert((2, 2));
  :t.insert((3, 3));
  return :t;
end
 
-- fails, because it is read-only
select a from
sql function
returns table (a int)
begin
  create column table temptable(a int);
  return select 1 as a from dummy;
end
```

--------------------------------

### Drop a User-Defined Library

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7cd14f1931404738a05c5e93e22564af.html?locale=en-US

Removes an existing library from the database.

```SQL
DROP LIBRARY <lib_name>;
```

--------------------------------

### WHILE Loop Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/6b2f29bbddcc431da72d9602a3049d93.html?locale=en-US

Basic structure for defining a WHILE loop in SQLScript.

```sql
WHILE <condition> DO
	<proc_stmts>
END WHILE
```

--------------------------------

### Handle Nested Block Exceptions

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/60088457716e46889c78662700737118.html?locale=en-US

Shows how exception handlers can be scoped to nested blocks within a procedure.

```sql
CREATE TABLE MYTAB (I INTEGER PRIMARY KEY);

CREATE PROCEDURE MYPROC AS
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL SET MESSAGE_TEXT = 'level 1';
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL SET MESSAGE_TEXT = 'level 2';
        INSERT INTO MYTAB VALUES (1);
        BEGIN
            DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL SET MESSAGE_TEXT = 'level 3';
            INSERT INTO MYTAB VALUES (1);  -- expected unique violation error: 301
            -- will not be reached
        END;
    END;
END;

CALL MYPROC;
```

--------------------------------

### Supported Aggregation Functions

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/02ab89e0c1624b59b0ceec16e5711fc9.html?locale=en-US

Lists the supported aggregation functions for use in aggregate expressions within CE_OLAP_VIEW.

```sql
<aggregate_func> ::= COUNT | SUM | MIN | MAX
```

--------------------------------

### Syntax Elements for Conditionals

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/46e875a0d4e446ae9ab08334f13cd1aa.html?locale=en-US

Definitions for boolean expressions, comparisons, and null checks.

```sql
<bool_expr1>  ::= <condition>
<bool_expr2>  ::= <condition>
<condition>   ::= <comparison> | <null_check>
<comparison>  ::= <comp_val> <comparator> <comp_val>
<null_check>  ::= <comp_val> IS [NOT] NULL
```

```sql
<comparator>  ::= < | > | = | <= | >= | != 
<comp_val>    ::= <scalar_expression>|<scalar_udf>
<scalar_expression> ::=<scalar_value>[{operator}<scalar_value>…]
<scalar_value> ::= <numeric_literal> | <exact_numeric_literal>| <unsigned_numeric_literal>
<operator>::=+|-|/|* 
```

```sql
<then_stmts1> ::= <proc>
<then_stmts2> ::= <proc_stmts>
<else_stmts3> ::= <proc_stmts>
<proc_stmts> ::= !! SQLScript procedural statements
```

--------------------------------

### Syntax for IS_EMPTY Predicate

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/3905f921a16f41d6addcf8d7979560c2.html?locale=en-US

The basic syntax for checking the emptiness of a table or table variable.

```sql
IS_EMPTY( <table_name> | <table_variable> )
```

--------------------------------

### EXEC

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/8462312b31d04879b124acb6b0275247.html?locale=en-US

Executes a provided SQL statement string. Note that this does not return result sets for SELECT statements; use EXECUTE IMMEDIATE for that purpose.

```APIDOC
## EXEC

### Description
Executes the SQL statement passed in a string argument. This command does not return a result set if the provided statement is a SELECT statement.

### Syntax
EXEC '<sql-statement>' [INTO <var_name_list> [DEFAULT <scalar_expr_list>]] [USING <expression_list>] [READS SQL DATA]
```

--------------------------------

### ROUTE_TO Hint

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/79a9f25f2e9a41e0a4cf03dc6b45a111.html?locale=en-US

The ROUTE_TO hint routes a query to a specified volume ID or service type. It can also be used with NO_ROUTE_TO, ROUTE_BY, ROUTE_BY_CARDINALITY, and DATA_TRANSFER_COST.

```APIDOC
## ROUTE_TO Hint

### Description
The ROUTE_TO hint directs a query to specific volume IDs or service types. It supports routing to specific nodes associated with a service type or specific volumes.

### Syntax
- ROUTE_TO( <volume_id> [{, <volume_id> }] )
- ROUTE_TO( '<servicetype>' [{, '<servicetype>' }] )
- NO_ROUTE_TO( <volume_id> [{, <volume_id> }] )
- NO_ROUTE_TO( '<servicetype>' [{, '<servicetype>' }] )
- ROUTE_BY( <table_name> [{, <table_name>}] )
- ROUTE_BY_CARDINALITY( <table_name> [{, <table_name>}] )
- DATA_TRANSFER_COST ({0 | 1})

### Supported Service Types
- indexserver
- xsengine
- scriptserver
- dpserver
- computeserver

### Example
```sql
select * from mytab with hint(ROUTE_TO('indexserver'));
select * from mytab with hint(ROUTE_TO('indexserver','computeserver'));
select * from mytab with hint(NO_ROUTE_TO('indexserver'));
```
```

--------------------------------

### Return Statement Syntax

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/7a2da744ce544db1814a5fff250e99f6.html?locale=en-US

Syntax for returning a value from a procedure.

```SQL
<proc_return> ::= RETURN [<proc_expr>] ;
```

--------------------------------

### Prepared Query with Explicit Binding

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/0b2958ee0426496f9c084c92b14993f1.html?locale=en-US

The resulting query string when using explicit binding functions.

```SQL
SELECT * FROM MYTAB WHERE MYCOL >? OR MYCOL = 2;
```
Tokens:
Raw