sap-sqlscript
SAP SQLScript is a procedural extension to SQL for SAP HANA that executes data-intensive logic directly in the database using the code-to-data paradigm. Use this skill when developing stored procedures, functions, and anonymous blocks for HANA databases, particularly when optimizing performance through declarative SQL sequences or implementing complex control flow with imperative statements that require sequential processing.
git clone --depth 1 https://github.com/secondsky/sap-skills /tmp/sap-sqlscript && cp -r /tmp/sap-sqlscript/plugins/sap-sqlscript/skills/sap-sqlscript ~/.claude/skills/sap-sqlscriptSKILL.md
# 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 [(<parameter_clause>)]
BEGIN [SEQUENTIAL EXECUTION]
<body>
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 <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:
```sql
CREATE FUNCTION <function_name> (<input_parameters>)
RETURNS <scalar_type>
LANGUAGE SQLSCRIPT
AS
BEGIN
<function_body>
RETURN <value>;
END;
```
**Table UDF** - Returns table (read-only):
```sql
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
```sql
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:**
```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 <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
```sql
WHILE <condition> DO
<statements>
END WHILE;
```
### FOR Loop
```sql
-- 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
```sql
LOOP
<statements>
IF <condition> THEN
BREAK;
END IF;
END LOOP;
```
---
## Table Types
Define reusable table structures:
```sql
CREATE TYPE <type_name> AS TABLE (
<column1> <datatype>,
<column2> <datatype>,
...
);
```
**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 <cursor_name> FOR
SELECT <columns> FROM <table> [WHERE <condition>];
OPEN <cursor_name>;
FETCH <cursor_name> INTO <variables>;
CLOSE <cursor_name>;
```
**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_emAnalyze a codebase and recommend Claude Code automations (hooks, subagents, skills, plugins, MCP servers). Use when user asks for automation recommendations, wants to optimize their Claude Code setup, mentions improving Claude Code workflows, asks how to first set up Claude Code for a project, or wants to know what Claude Code features they should use.
Audit and improve CLAUDE.md files in repositories. Use when user asks to check, audit, update, improve, or fix CLAUDE.md files. Scans for all CLAUDE.md files, evaluates quality against templates, outputs quality report, then makes targeted updates. Also use when the user mentions "CLAUDE.md maintenance" or "project memory optimization".
Secure dependency upgrades with supply chain protection, cooldowns, and staged rollout. Use when upgrading deps, configuring security policies, or preventing supply chain attacks.
Interview the user relentlessly about a plan or design until reaching shared understanding, resolving each branch of the decision tree. Use when user wants to stress-test a plan, get grilled on their design, or mentions "grill me".
Comprehensive SAP ABAP CDS (Core Data Services) reference for data modeling, view development, and semantic enrichment. Use when creating CDS views or view entities, defining data models with annotations, working with associations and cardinality, implementing input parameters, using built-in functions, writing CASE expressions, implementing access control with DCL, handling CURR/QUAN data types, troubleshooting CDS errors, querying CDS views from ABAP, or displaying data with SALV IDA. Covers ABAP 7.4+ through ABAP Cloud.
|
|
|