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:

DOBEGIN  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>]ASBEGIN  <procedure_body>END;

3. User-Defined Functions

Scalar UDF - Returns single value:

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

Table UDF - Returns table (read-only):

CREATE FUNCTION <function_name> (<input_parameters>)RETURNS TABLE (<column_definitions>)LANGUAGE SQLSCRIPTREADS SQL DATAASBEGIN  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>];
-- ExamplesDECLARE 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:

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

WHILE Loop

WHILE <condition> DO  <statements>END WHILE;

FOR Loop

-- Numeric rangeFOR i IN 1..10 DO  <statements>END FOR;
-- ReverseFOR i IN REVERSE 10..1 DO  <statements>END FOR;
-- Cursor iterationFOR 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 ASBEGIN  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:

DOBEGIN  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 ASBEGIN  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>;
-- ExampleDECLARE duplicate_key CONDITION FOR SQL_ERROR_CODE 301;DECLARE EXIT HANDLER FOR duplicate_key  SELECT 'Duplicate key error' FROM DUMMY;

SIGNAL and RESIGNAL

Throw user-defined exceptions (codes 10000-19999):

-- Throw exceptionSIGNAL <condition_name> SET MESSAGE_TEXT = '<message>';
-- Re-throw in handlerRESIGNAL [<condition_name>] [SET MESSAGE_TEXT = '<message>'];

Common Error Codes:

AMDP Integration

ABAP Managed Database Procedures allow SQLScript within ABAP classes.

Class Definition

CLASS zcl_my_amdp DEFINITION PUBLIC FINAL CREATE PUBLIC.  PUBLIC SECTION.    INTERFACES if_amdp_marker_hdb.  " Required interface
    TYPES: BEGIN OF ty_result,             id   TYPE i,             name TYPE string,           END OF ty_result,           tt_result TYPE STANDARD TABLE OF ty_result.
    CLASS-METHODS: get_data      IMPORTING VALUE(iv_filter) TYPE string      EXPORTING VALUE(et_result) TYPE tt_result.ENDCLASS.

Method Implementation

CLASS zcl_my_amdp IMPLEMENTATION.  METHOD get_data BY DATABASE PROCEDURE    FOR HDB    LANGUAGE SQLSCRIPT    OPTIONS READ-ONLY    USING ztable.
    et_result = SELECT id, name                FROM ztable                WHERE category = :iv_filter;  ENDMETHOD.ENDCLASS.

AMDP Restrictions

• Parameters must be pass-by-value (no RETURNING)
• Only scalar types, structures, internal tables allowed
• No nested tables or deep structures
• COMMIT/ROLLBACK not permitted
• Must use Eclipse ADT for development
• Auto-created on first invocation

Performance Best Practices

1. Reduce Data Volume Early

-- Good: Filter and project earlylt_filtered = SELECT col1, col2 FROM "BIGTABLE" WHERE status = 'A';lt_result = SELECT a.col1, b.name            FROM :lt_filtered AS a            JOIN "LOOKUP" AS b ON a.id = b.id;
-- Bad: Join then filterlt_result = SELECT a.col1, b.name            FROM "BIGTABLE" AS a            JOIN "LOOKUP" AS b ON a.id = b.id            WHERE a.status = 'A';

2. Prefer Declarative Over Imperative

-- Good: Set-based operationlt_result = SELECT id, amount * 1.1 AS new_amount FROM "ORDERS";
-- Bad: Row-by-row processingFOR row AS cur_orders DO  UPDATE "ORDERS" SET amount = row.amount * 1.1 WHERE id = row.id;END FOR;

3. Avoid Engine Mixing

• Don't mix Row Store and Column Store tables in same query
• Avoid Calculation Engine functions with pure SQL
• Use consistent storage types

4. Use UNION ALL Instead of UNION

-- Faster when duplicates impossible or acceptableSELECT * FROM table1 UNION ALL SELECT * FROM table2;
-- Slower: removes duplicatesSELECT * FROM table1 UNION SELECT * FROM table2;

5. Avoid Dynamic SQL

-- Bad: Re-optimized each executionEXECUTE IMMEDIATE 'SELECT * FROM ' || :lv_table;
-- Good: Static SQL with parametersSELECT * FROM "MYTABLE" WHERE id = :lv_id;

6. Position Imperative Logic Last

Place control structures at the end of procedures to maximize parallel processing of declarative statements.

System Limits

Note: Actual limits may vary by HANA version. Consult SAP documentation for version-specific limits.

Debugging Tools

• SQLScript Debugger - SAP Web IDE / Business Application Studio
• Plan Visualizer - Analyze execution plans
• Expensive Statement Trace - Identify bottlenecks
• SQL Analyzer - Query optimization recommendations

Quick Reference

String Concatenation

lv_result = lv_str1 || ' ' || lv_str2;

NULL Handling

COALESCE(value, default_value)IFNULL(value, default_value)NULLIF(value1, value2)

Date Operations

ADD_DAYS(date, n)ADD_MONTHS(date, n)DAYS_BETWEEN(date1, date2)CURRENT_DATECURRENT_TIMESTAMP

Type Conversion

CAST(value AS datatype)TO_VARCHAR(value)TO_INTEGER(value)TO_DATE(string, 'YYYY-MM-DD')TO_TIMESTAMP(string, 'YYYY-MM-DD HH24:MI:SS')

Related Skills

For comprehensive SAP development, combine this skill with:

Bundled Resources

Reference Documentation

• references/skill-reference-guide.md - Index of all references with quick navigation
• references/glossary.md - SQLScript terminology and concepts
• references/syntax-reference.md - Complete SQLScript syntax reference
• references/built-in-functions.md - Built-in functions catalog
• references/data-types.md - Data types and conversion
• references/exception-handling.md - Exception handling patterns
• references/amdp-integration.md - AMDP integration patterns
• references/performance-guide.md - Optimization techniques
• references/advanced-features.md - Lateral joins, JSON, query hints, currency conversion
• references/troubleshooting.md - Common errors and solutions
• references/edge-cdp-control.md - SQLScript-specific add-on for the shared sap-browser-automation Edge/CDP and authentication layer

Production-Ready Templates

Copy and customize these templates for common patterns:

• templates/simple-procedure.sql - Basic stored procedure with error handling
• templates/procedure-with-error-handling.sql - Comprehensive error handling patterns
• templates/table-function.sql - Table UDF with validation
• templates/scalar-function.sql - Scalar UDF examples
• templates/amdp-class.abap - Complete AMDP class boilerplate
• templates/amdp-procedure.sql - AMDP implementation template
• templates/cursor-iteration.sql - Cursor patterns (classic and FOR loop)
• templates/bulk-operations.sql - High-performance bulk operations

Specialized Agents

• sqlscript-analyzer - Analyze code for performance issues and best practices
• procedure-generator - Generate procedures interactively from requirements
• amdp-helper - Assist with AMDP class creation and debugging

Slash Commands

• /sqlscript-validate - Validate code with auto-fix capability
• /sqlscript-optimize - Performance analysis and optimization suggestions
• /sqlscript-convert - Convert between standalone and AMDP formats

Validation Hooks

Automatic code quality checks on Write/Edit operations:

• Error handling completeness
• Security vulnerabilities
• Performance anti-patterns
• Naming conventions
• AMDP compliance