Sap Sqlscript

SAP SQLScript Development Guide

When to Use This Skill

Use this skill when writing SQLScript procedures, anonymous blocks, table/scalar functions, AMDP methods, exception handlers, cursor logic, bulk operations, or HANA performance-sensitive database logic that should run close to the data.

For browser-based Datasphere or HANA Cloud SQL editor triage, use sap-browser-automation for manual in-app authentication, consent-gated Edge profile reuse, fresh Edge/CDP startup, auth-state bootstrap, and recovery. Load local references/edge-cdp-control.md for SQLScript-specific boundaries. Use CDP only for local UI inspection, console diagnostics, deployment messages, and approved screenshots; default database validation still belongs in SQL/HANA tooling.

Overview

SQLScript is SAP HANA's procedural extension to SQL, enabling complex data-intensive logic execution directly within the database layer. It follows the code-to-data paradigm, pushing computation to where data resides rather than moving data to the application layer.

Key Characteristics

• Case-insensitive language
• All statements end with semicolons
• Variables use colon prefix when referenced (:variableName)
• No colon when assigning values
• Use DUMMY table for single-row operations

Two Logic Types

Table of Contents

• Overview
• Container Types
• Anonymous Blocks
• Stored Procedures
• User-Defined Functions
• Data Types
• Variable Declaration
• Control Structures
• Table Types
• Cursors
• Exception Handling
• AMDP Integration
• Performance Best Practices
• System Limits
• Debugging Tools
• Quick Reference
• Additional Resources

Container Types

1. Anonymous Blocks

Single-use logic not stored in the database. Useful for testing and ad-hoc execution.

DO [(<parameter_clause>)]
BEGIN [SEQUENTIAL EXECUTION]
  <body>
END;

Example:

DO
BEGIN
  DECLARE lv_count INTEGER;
  SELECT COUNT(*) INTO lv_count FROM "MYTABLE";
  SELECT :lv_count AS record_count FROM DUMMY;
END;

2. Stored Procedures

Reusable database objects with input/output parameters.

CREATE [OR REPLACE] PROCEDURE <procedure_name>
  (
    [IN <param> <datatype>],
    [OUT <param> <datatype>],
    [INOUT <param> <datatype>]
  )
  LANGUAGE SQLSCRIPT
  [SQL SECURITY {DEFINER | INVOKER}]
  [DEFAULT SCHEMA <schema_name>]
  [READS SQL DATA | READS SQL DATA WITH RESULT VIEW <view_name>]
AS
BEGIN
  <procedure_body>
END;

3. User-Defined Functions

Scalar UDF - Returns single value:

CREATE FUNCTION <function_name> (<input_parameters>)
RETURNS <scalar_type>
LANGUAGE SQLSCRIPT
AS
BEGIN
  <function_body>
  RETURN <value>;
END;

Table UDF - Returns table (read-only):

CREATE FUNCTION <function_name> (<input_parameters>)
RETURNS TABLE (<column_definitions>)
LANGUAGE SQLSCRIPT
READS SQL DATA
AS
BEGIN
  RETURN SELECT ... FROM ...;
END;

Data Types

SQLScript supports comprehensive data types for different use cases. See references/data-types.md for complete documentation including:

• Numeric types (TINYINT, INTEGER, DECIMAL, etc.)
• Character types (VARCHAR, NVARCHAR, CLOB, etc.)
• Date/Time types (DATE, TIME, TIMESTAMP, SECONDDATE)
• Binary types (VARBINARY, BLOB)
• Type conversion functions (CAST, TO_ functions)
• NULL handling patterns

Variable Declaration

Scalar Variables

DECLARE <variable_name> <datatype> [:= <initial_value>];

-- Examples
DECLARE lv_name NVARCHAR(100);
DECLARE lv_count INTEGER := 0;
DECLARE lv_date DATE := CURRENT_DATE;

Note: Uninitialized variables default to NULL.

Table Variables

Implicit declaration:

lt_result = SELECT * FROM "MYTABLE" WHERE status = 'A';

Explicit declaration:

DECLARE lt_data TABLE (
  id INTEGER,
  name NVARCHAR(100),
  amount DECIMAL(15,2)
);

Using TABLE LIKE:

DECLARE lt_copy TABLE LIKE :lt_original;

Arrays

DECLARE arr INTEGER ARRAY := ARRAY(1, 2, 3, 4, 5);
-- Access: arr[1], arr[2], etc. (1-based index)
-- Note: Arrays cannot be returned from procedures

Control Structures

IF-ELSE Statement

IF <condition1> THEN
  <statements>
[ELSEIF <condition2> THEN
  <statements>]
[ELSE
  <statements>]
END IF;

Comparison Operators: | Operator | Meaning | |----------|---------| | = | Equal to | | > | Greater than | | < | Less than | | >= | Greater than or equal | | <= | Less than or equal | | !=, <> | Not equal |

Important: IF-ELSE cannot be used within SELECT statements. Use CASE WHEN instead.

WHILE Loop

WHILE <condition> DO
  <statements>
END WHILE;

FOR Loop

-- Numeric range
FOR i IN 1..10 DO
  <statements>
END FOR;

-- Reverse
FOR i IN REVERSE 10..1 DO
  <statements>
END FOR;

-- Cursor iteration
FOR row AS <cursor_name> DO
  <statements using row.column_name>
END FOR;

LOOP with EXIT

LOOP
  <statements>
  IF <condition> THEN
    BREAK;
  END IF;
END LOOP;

Table Types

Define reusable table structures:

CREATE TYPE <type_name> AS TABLE (
  <column1> <datatype>,
  <column2> <datatype>,
  ...
);

Usage in procedures:

CREATE PROCEDURE get_employees (OUT et_result MY_TABLE_TYPE)
LANGUAGE SQLSCRIPT AS
BEGIN
  et_result = SELECT * FROM "EMPLOYEES";
END;

Cursors

Cursors handle result sets row by row. Pattern: Declare → Open → Fetch → Close

Performance Note: Cursors bypass the database optimizer and process rows sequentially. Use primarily with primary key-based queries. Prefer set-based operations when possible.

DECLARE CURSOR <cursor_name> FOR
  SELECT <columns> FROM <table> [WHERE <condition>];

OPEN <cursor_name>;

FETCH <cursor_name> INTO <variables>;

CLOSE <cursor_name>;

Complete Example:

DO
BEGIN
  DECLARE lv_id INTEGER;
  DECLARE lv_name NVARCHAR(100);
  DECLARE CURSOR cur_employees FOR
    SELECT id, name FROM "EMPLOYEES" WHERE dept = 'IT';

  OPEN cur_employees;
  FETCH cur_employees INTO lv_id, lv_name;
  WHILE NOT cur_employees::NOTFOUND DO
    -- Process row
    SELECT :lv_id, :lv_name FROM DUMMY;
    FETCH cur_employees INTO lv_id, lv_name;
  END WHILE;
  CLOSE cur_employees;
END;

FOR Loop Alternative:

FOR row AS cur_employees DO
  SELECT row.id, row.name FROM DUMMY;
END FOR;

Exception Handling

EXIT HANDLER

Suspends execution and performs cleanup when exceptions occur.

DECLARE EXIT HANDLER FOR <condition_value>
  <statement>;

Condition values:

• SQLEXCEPTION - Any SQL exception
• SQL_ERROR_CODE <number> - Specific error code

Access error details:

• ::SQL_ERROR_CODE - Numeric error code
• ::SQL_ERROR_MESSAGE - Error message text

Example:

CREATE PROCEDURE safe_insert (IN iv_id INTEGER, IN iv_name NVARCHAR(100))
LANGUAGE SQLSCRIPT AS
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT ::SQL_ERROR_CODE AS err_code,
           ::SQL_ERROR_MESSAGE AS err_msg FROM DUMMY;
  END;

  INSERT INTO "MYTABLE" VALUES (:iv_id, :iv_name);
END;

CONDITION

Associate user-defined names with error codes:

DECLARE <condition_name> CONDITION FOR SQL_ERROR_CODE <number>;

-- Example
DECLARE duplicate_key CONDITION FOR SQL_ERROR_CODE 301;
DECLARE EXIT HANDLER FOR duplicate_key