| name | sap-sqlscript |
| model | claude-opus-4-7 |
| description | 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 exceptions", "debug HANA procedure", "create table function", or mentions SQLScript, SAP HANA procedures, AMDP, EXIT HANDLER, or code-to-data paradigm.
Comprehensive SQLScript development guidance for SAP HANA database programming including syntax patterns, built-in functions, exception handling, performance optimization, cursor management, and ABAP Managed Database Procedure (AMDP) integration.
|
| license | GPL-3.0 |
| metadata | {"version":"2.1.0","last_verified":"2025-12-27","production_tested":"Yes, referenced in SAP Community projects","sap_hana_version":"2.0 SPS07","hana_cloud_version":"QRC 3/2025","errors_prevented":15} |
SAP SQLScript Development Guide
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
| 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 |
Table of Contents
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>];
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);
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
FOR i IN 1..10 DO
<statements>
END FOR;
FOR i IN REVERSE 10..1 DO
<statements>
END FOR;
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
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 exceptionSQL_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>;
DECLARE 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):
SIGNAL <condition_name> SET MESSAGE_TEXT = '<message>';
RESIGNAL [<condition_name>] [SET MESSAGE_TEXT = '<message>'];
Common Error Codes:
| Code | Description |
|---|
| 301 | Unique constraint violation |
| 1299 | No data found |
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
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;
lt_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
lt_result = SELECT id, amount * 1.1 AS new_amount FROM "ORDERS";
FOR 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
SELECT * FROM table1 UNION ALL SELECT * FROM table2;
SELECT * FROM table1 UNION SELECT * FROM table2;
5. Avoid Dynamic SQL
EXECUTE IMMEDIATE 'SELECT * FROM ' || :lv_table;
SELECT * 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
| 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.
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_DATE
CURRENT_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:
| 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 |
Bundled Resources
Reference Documentation
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 solutions
Production-Ready Templates
Copy 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
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
Cross-Skill References
For detailed coverage of these topics, defer to the canonical skill:
- CAP integration with HANA:
sap-cap-capire skill (canonical for CDS models, HDI deployment)
- BTP deployment:
sap-btp-best-practices skill (canonical for CF/Kyma deployment patterns)
- ABAP AMDP:
sap-abap skill (canonical for AMDP class implementation in ABAP)
