sap-sqlscript
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.
What this skill does
# 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_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_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:**
```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_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;
```
### SIGNAL and RESIGNAL
Throw user-defined exceptions (codes 10000-19999):
```sql
-- 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 |
---
## 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 Related in Code Review
gstack
IncludedFast headless browser for QA testing and site dogfooding. Navigate pages, interact with elements, verify state, diff before/after, take annotated screenshots, test responsive layouts, forms, uploads, dialogs, and capture bug evidence. Use when asked to open or test a site, verify a deployment, dogfood a user flow, or file a bug with screenshots. (gstack)
startup-due-diligence
IncludedLegal due diligence review for seed-stage and Series A startups (US, Delaware C-Corp focus). Supports both investor and founder perspectives. Capabilities include: (1) Interactive document review and issue spotting; (2) Document request list generation; (3) Cap table and SAFE/convertible note analysis; (4) Red flag identification with severity ratings; (5) Diligence report generation. TRIGGERS: due diligence, DD, startup investment, cap table review, Series A, seed round, investor diligence, legal review startup, SAFE analysis, convertible note, 409A, founder vesting.
interview-master
IncludedThis skill should be used when the user asks to "generate interview questions", "prepare for interview", "optimize resume", "conduct mock interview", "analyze git commits for resume", "generate resume from code", "review my resume", or mentions interview preparation, career assistance, or extracting project experience from git history. Provides comprehensive interview and career development guidance for both job seekers and interviewers.
fix-issue
IncludedFixes GitHub issues using parallel analysis agents for root cause investigation, code exploration, and regression detection. Reads issue context from gh CLI, searches codebase and memory for related patterns, generates a fix with tests, and links the resolution back to the issue via PR. Includes prevention analysis to avoid recurrence. Use when debugging errors, resolving regressions, fixing bugs, or triaging issues.
sf-apex
IncludedGenerates and reviews Salesforce Apex code with 150-point scoring. TRIGGER when: user writes, reviews, or fixes Apex classes, triggers, test classes, batch/queueable/schedulable jobs, or touches .cls/.trigger files. DO NOT TRIGGER when: LWC JavaScript (use sf-lwc), Flow XML (use sf-flow), SOQL-only queries (use sf-soql), or non-Salesforce code.
swift-development
IncludedComprehensive Swift development for building, testing, and deploying iOS/macOS applications. Use when Claude needs to: (1) Build Swift packages or Xcode projects from command line, (2) Run tests with XCTest or Swift Testing framework, (3) Manage iOS simulators with simctl, (4) Handle code signing, provisioning profiles, and app distribution, (5) Format or lint Swift code with SwiftFormat/SwiftLint, (6) Work with Swift Package Manager (SPM), (7) Implement Swift 6 concurrency patterns (async/await, actors, Sendable), (8) Create SwiftUI views with MVVM architecture, (9) Set up Core Data or SwiftData persistence, or any other Swift/iOS/macOS development tasks.