--- name: sap-sqlscript 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 - [Overview](#overview) - [Container Types](#container-types) - [Anonymous Blocks](#1-anonymous-blocks) - [Stored Procedures](#2-stored-procedures) - [User-Defined Functions](#3-user-defined-functions) - [Data Types](#data-types) - [Variable Declaration](#variable-declaration) - [Control Structures](#control-structures) - [Table Types](#table-types) - [Cursors](#cursors) - [Exception Handling](#exception-handling) - [AMDP Integration](#amdp-integration) - [Performance Best Practices](#performance-best-practices) - [System Limits](#system-limits) - [Debugging Tools](#debugging-tools) - [Quick Reference](#quick-reference) - [Additional Resources](#additional-resources) --- ## Container Types ### 1. Anonymous Blocks Single-use logic not stored in the database. Useful for testing and ad-hoc execution. ```sql DO [()] BEGIN [SEQUENTIAL EXECUTION] END; ``` **Example:** ```sql 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. ```sql CREATE [OR REPLACE] PROCEDURE ( [IN ], [OUT ], [INOUT ] ) LANGUAGE SQLSCRIPT [SQL SECURITY {DEFINER | INVOKER}] [DEFAULT SCHEMA ] [READS SQL DATA | READS SQL DATA WITH RESULT VIEW ] AS BEGIN END; ``` ### 3. User-Defined Functions **Scalar UDF** - Returns single value: ```sql CREATE FUNCTION () RETURNS LANGUAGE SQLSCRIPT AS BEGIN RETURN ; END; ``` **Table UDF** - Returns table (read-only): ```sql CREATE FUNCTION () RETURNS TABLE () 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 ```sql DECLARE [:= ]; -- 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:** ```sql lt_result = SELECT * FROM "MYTABLE" WHERE status = 'A'; ``` **Explicit declaration:** ```sql DECLARE lt_data TABLE ( id INTEGER, name NVARCHAR(100), amount DECIMAL(15,2) ); ``` **Using TABLE LIKE:** ```sql DECLARE lt_copy TABLE LIKE :lt_original; ``` ### Arrays ```sql 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 ```sql IF THEN [ELSEIF THEN ] [ELSE ] 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 ```sql WHILE DO END WHILE; ``` ### FOR Loop ```sql -- Numeric range FOR i IN 1..10 DO END FOR; -- Reverse FOR i IN REVERSE 10..1 DO END FOR; -- Cursor iteration FOR row AS DO END FOR; ``` ### LOOP with EXIT ```sql LOOP IF THEN BREAK; END IF; END LOOP; ``` --- ## Table Types Define reusable table structures: ```sql CREATE TYPE AS TABLE ( , , ... ); ``` **Usage in procedures:** ```sql 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. ```sql DECLARE CURSOR FOR SELECT FROM [WHERE ]; OPEN ; FETCH INTO ; CLOSE ; ``` **Complete Example:** ```sql 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:** ```sql 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. ```sql DECLARE EXIT HANDLER FOR ; ``` **Condition values:** - `SQLEXCEPTION` - Any SQL exception - `SQL_ERROR_CODE ` - Specific error code **Access error details:** - `::SQL_ERROR_CODE` - Numeric error code - `::SQL_ERROR_MESSAGE` - Error message text **Example:** ```sql 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: ```sql DECLARE CONDITION FOR SQL_ERROR_CODE ; -- Example 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): ```sql -- Throw exception SIGNAL SET MESSAGE_TEXT = ''; -- Re-throw in handler RESIGNAL [] [SET MESSAGE_TEXT = '']; ``` **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 ```abap 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 ```abap 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 ```sql -- 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'; ``` ### 2. Prefer Declarative Over Imperative ```sql -- 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; ``` ### 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 ```sql -- Faster when duplicates impossible or acceptable SELECT * FROM table1 UNION ALL SELECT * FROM table2; -- Slower: removes duplicates SELECT * FROM table1 UNION SELECT * FROM table2; ``` ### 5. Avoid Dynamic SQL ```sql -- Bad: Re-optimized each execution EXECUTE IMMEDIATE 'SELECT * FROM ' || :lv_table; -- Good: Static SQL with parameters 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 ```sql lv_result = lv_str1 || ' ' || lv_str2; ``` ### NULL Handling ```sql COALESCE(value, default_value) IFNULL(value, default_value) NULLIF(value1, value2) ``` ### Date Operations ```sql ADD_DAYS(date, n) ADD_MONTHS(date, n) DAYS_BETWEEN(date1, date2) CURRENT_DATE CURRENT_TIMESTAMP ``` ### Type Conversion ```sql 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 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 ### 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