This skill should be used when the user asks to "write a SQLScript procedure", "create HANA stored procedure", "implement AMDP method", "optimize SQLScript performance", "handle SQLScript...
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.
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.
:variableName)DUMMY table for single-row operations| Type | Description | Execution |
|---|---|---|
| Declarative | Pure SQL sequences | Converted to data flow graphs, processed in parallel |
| Imperative | Control structures (IF, WHILE, FOR) | Processed sequentially, prevents parallel execution |
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;
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;
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;
SQLScript supports comprehensive data types for different use cases. See references/data-types.md for complete documentation including:
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.
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;
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
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 <condition> DO
<statements>
END WHILE;
-- 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
<statements>
IF <condition> THEN
BREAK;
END IF;
END LOOP;
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 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;
Suspends execution and performs cleanup when exceptions occur.
DECLARE EXIT HANDLER FOR <condition_value>
<statement>;
Condition values:
SQLEXCEPTION - Any SQL exceptionSQL_ERROR_CODE <number> - Specific error codeAccess error details:
::SQL_ERROR_CODE - Numeric error code::SQL_ERROR_MESSAGE - Error message textExample:
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;
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
SELECT 'Duplicate key error' FROM DUMMY;
Throw user-defined exceptions (codes 10000-19999):
-- Throw exception
SIGNAL <condition_name> SET MESSAGE_TEXT = '<message>';
-- Re-throw in handler
RESIGNAL [<condition_name>] [SET MESSAGE_TEXT = '<message>'];
Common Error Codes:
| Code | Description |
|---|---|
| 301 | Unique constraint violation |
| 1299 | No data found |
ABAP Managed Database Procedures allow SQLScript within ABAP classes.
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.
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.
-- Good: Filter and project early
lt_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 filter
lt_result = SELECT a.col1, b.name
FROM "BIGTABLE" AS a
JOIN "LOOKUP" AS b ON a.id = b.id
WHERE a.status = 'A';
-- Good: Set-based operation
lt_result = SELECT id, amount * 1.1 AS new_amount FROM "ORDERS";
-- Bad: Row-by-row processing
FOR row AS cur_orders DO
UPDATE "ORDERS" SET amount = row.amount * 1.1 WHERE id = row.id;
END FOR;
-- Faster when duplicates impossible or acceptable
SELECT * FROM table1 UNION ALL SELECT * FROM table2;
-- Slower: removes duplicates
SELECT * FROM table1 UNION SELECT * FROM table2;
-- Bad: Re-optimized each execution
EXECUTE IMMEDIATE 'SELECT * FROM ' || :lv_table;
-- Good: Static SQL with parameters
SELECT * FROM "MYTABLE" WHERE id = :lv_id;
Place control structures at the end of procedures to maximize parallel processing of declarative statements.
| Limit | Value |
|---|---|
| Table locks per transaction | 16,383 |
| Tables in a statement | 4,095 |
| SQL statement length | 2 GB |
| Procedure size | Bounded by SQL statement length (2 GB) |
Note: Actual limits may vary by HANA version. Consult SAP documentation for version-specific limits.
lv_result = lv_str1 || ' ' || lv_str2;
COALESCE(value, default_value)
IFNULL(value, default_value)
NULLIF(value1, value2)
ADD_DAYS(date, n)
ADD_MONTHS(date, n)
DAYS_BETWEEN(date1, date2)
CURRENT_DATE
CURRENT_TIMESTAMP
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')
For comprehensive SAP development, combine this skill with:
| Skill | Use Case |
|---|---|
| sap-abap | ABAP programming patterns for AMDP context |
| sap-abap-cds | CDS views that consume SQLScript procedures |
| sap-cap-capire | CAP framework database procedures integration |
| sap-hana-cli | HANA CLI for procedure deployment and testing |
| sap-btp-cloud-platform | BTP deployment of HANA artifacts |
references/skill-reference-guide.md - Index of all references with quick navigationreferences/glossary.md - SQLScript terminology and conceptsreferences/syntax-reference.md - Complete SQLScript syntax referencereferences/built-in-functions.md - Built-in functions catalogreferences/data-types.md - Data types and conversionreferences/exception-handling.md - Exception handling patternsreferences/amdp-integration.md - AMDP integration patternsreferences/performance-guide.md - Optimization techniquesreferences/advanced-features.md - Lateral joins, JSON, query hints, currency conversionreferences/troubleshooting.md - Common errors and solutionsCopy and customize these templates for common patterns:
templates/simple-procedure.sql - Basic stored procedure with error handlingtemplates/procedure-with-error-handling.sql - Comprehensive error handling patternstemplates/table-function.sql - Table UDF with validationtemplates/scalar-function.sql - Scalar UDF examplestemplates/amdp-class.abap - Complete AMDP class boilerplatetemplates/amdp-procedure.sql - AMDP implementation templatetemplates/cursor-iteration.sql - Cursor patterns (classic and FOR loop)templates/bulk-operations.sql - High-performance bulk operations/sqlscript-validate - Validate code with auto-fix capability/sqlscript-optimize - Performance analysis and optimization suggestions/sqlscript-convert - Convert between standalone and AMDP formatsAutomatic code quality checks on Write/Edit operations:
