SkillHub ClubAnalyze Data & AIFull StackBackendData / AI

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.

Packaged view

This page reorganizes the original catalog entry around fit, installability, and workflow context first. The original raw source lives below.

Stars

150

Hot score

Updated

March 20, 2026

Overall rating

C4.1

Composite score

4.1

Best-practice grade

C64.8

Install command

npx @skill-hub/cli install secondsky-sap-skills-sap-sqlscript

Repository

secondsky/sap-skills

Skill path: plugins/sap-sqlscript/skills/sap-sqlscript

Open repository

Best for

Primary workflow: Analyze Data & AI.

Technical facets: Full Stack, Backend, Data / AI, Testing, Integration.

Target audience: everyone.

License: GPL-3.0.

Original source

Catalog source: SkillHub Club.

Repository owner: secondsky.

This is still a mirrored public skill entry. Review the repository before installing into production workflows.

What it helps with

Install sap-sqlscript into Claude Code, Codex CLI, Gemini CLI, or OpenCode workflows
Review https://github.com/secondsky/sap-skills before adding sap-sqlscript to shared team environments
Use sap-sqlscript for development workflows

Works across

Claude CodeCodex CLIGemini CLIOpenCode

Favorites: 0.

Sub-skills: 0.

Aggregator: No.

Original source / Raw SKILL.md

---
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 [(<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   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


---

## Referenced Files

> The following files are referenced in this skill and included for context.

### references/data-types.md

```markdown
# SQLScript Data Types Reference

## Numeric Types

| Type | Description | Range |
|------|-------------|-------|
| `TINYINT` | 8-bit integer | 0 to 255 |
| `SMALLINT` | 16-bit integer | -32,768 to 32,767 |
| `INTEGER` / `INT` | 32-bit integer | -2,147,483,648 to 2,147,483,647 |
| `BIGINT` | 64-bit integer | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
| `DECIMAL(p,s)` | Fixed-point decimal | p = precision (1-34), s = scale (0-p) |
| `DOUBLE` | 64-bit floating point | 15-digit precision |
| `REAL` | 32-bit floating point | 7-digit precision |

### Usage Examples
```sql
-- Decimal with precision and scale
DECLARE lv_price DECIMAL(15,2) := 12345.67;

-- Integer types
DECLARE lv_count INTEGER := 0;
DECLARE lv_id BIGINT;

-- Floating point
DECLARE lv_percentage REAL := 0.95;
DECLARE lv_measurement DOUBLE := 123.456789;
```

## Character Types

| Type | Description | Max Length |
|------|-------------|------------|
| `VARCHAR(n)` | Variable-length ASCII string | 5,000 bytes |
| `NVARCHAR(n)` | Variable-length Unicode string | 5,000 characters |
| `ALPHANUM(n)` | Alphanumeric string | 127 characters |
| `CLOB` | Character large object | 2 GB |
| `NCLOB` | Unicode character large object | 2 GB |
| `NLOB` | National character large object | 2 GB |

### Usage Examples
```sql
-- String variables
DECLARE lv_name NVARCHAR(100) := 'John Doe';
DECLARE lv_description VARCHAR(500);
DECLARE lv_code ALPHANUM(10) := 'ABC123';

-- Large objects
DECLARE lt_document CLOB;
```

### String Operations
```sql
-- Concatenation
lv_full_name = lv_first_name || ' ' || lv_last_name;

-- Common string functions
lv_upper = UPPER(:lv_string);
lv_length = LENGTH(:lv_string);
lv_trimmed = TRIM(:lv_string);
lv_substring = SUBSTRING(:lv_string, 1, 10);
```

## Date/Time Types

| Type | Format | Range |
|------|--------|-------|
| `DATE` | 'YYYY-MM-DD' | 0001-01-01 to 9999-12-31 |
| `TIME` | 'HH:MI:SS' | 00:00:00 to 23:59:59 |
| `TIMESTAMP` | 'YYYY-MM-DD HH:MI:SS.FF' | Up to 7 fractional digits |
| `SECONDDATE` | 'YYYY-MM-DD HH:MI:SS' | Same as TIMESTAMP without fractions |

### Usage Examples
```sql
-- Date/time declarations
DECLARE lv_order_date DATE := CURRENT_DATE;
DECLARE lv_created_time TIMESTAMP := CURRENT_TIMESTAMP;
DECLARE lv_start_time TIME;

-- Date literals
lv_date = DATE '2025-11-26';
lv_time = TIME '14:30:00';
lv_timestamp = TIMESTAMP '2025-11-26 14:30:00.123';

-- Date operations
lv_future_date = ADD_DAYS(:lv_date, 30);
lv_days_between = DAYS_BETWEEN(:lv_date1, :lv_date2);
```

## Binary Types

| Type | Description | Max Length |
|------|-------------|------------|
| `VARBINARY(n)` | Variable-length binary | 5,000 bytes |
| `BLOB` | Binary large object | 2 GB |

### Usage Examples
```sql
-- Binary data
DECLARE lv_image VARBINARY(1000);
DECLARE lb_file BLOB;

-- Hexadecimal literals
lv_binary = X'48656C6C6F'; -- 'Hello' in hex
```

## Type Conversion

### CAST Function
```sql
-- Explicit type conversion
lv_string = CAST(:lv_number AS VARCHAR(20));
lv_date = CAST(:lv_string AS DATE);
lv_decimal = CAST(:lv_number AS DECIMAL(10,2));
```

### TO_ Functions
```sql
-- String to number
lv_integer = TO_INTEGER(:lv_string);
lv_decimal = TO_DECIMAL(:lv_string, '999999.99');

-- String to date/time
lv_date = TO_DATE(:lv_string, 'YYYY-MM-DD');
lv_timestamp = TO_TIMESTAMP(:lv_string, 'YYYY-MM-DD HH24:MI:SS');

-- Number/date to string
lv_string = TO_VARCHAR(:lv_number);
lv_string_date = TO_VARCHAR(:lv_date, 'YYYY-MM-DD');

-- ABAP date/time conversion
lv_abap_date = TO_DATS(:lv_date);
lv_abap_time = TO_TIMS(:lv_time);
```

## NULL Handling

### Functions for NULL Values
```sql
-- COALESCE: Return first non-NULL value
lv_result = COALESCE(:lv_value1, :lv_value2, 'DEFAULT');

-- IFNULL: Return default if NULL
lv_result = IFNULL(:lv_value, 'DEFAULT');

-- NULLIF: Return NULL if values equal
lv_null_result = NULLIF(:lv_value1, :lv_value2);
```

### NULL Comparisons
```sql
-- Check for NULL
IF :lv_value IS NULL THEN
  -- Handle NULL
END IF;

-- Cannot use = NULL, must use IS NULL
-- Wrong: WHERE column = NULL
-- Right: WHERE column IS NULL
```

## Special Considerations

### Character Set Considerations
- `VARCHAR` stores ASCII (1 byte per character)
- `NVARCHAR` stores Unicode (UTF-8, variable bytes per character)
- Use `NVARCHAR` for multilingual data
- `ALPHANUM` removes leading/trailing spaces automatically

### Performance Considerations
- Use smallest appropriate data type for better performance
- `DECIMAL` is preferred over `FLOAT` for precise calculations
- `VARCHAR`/`NVARCHAR` with defined length performs better than CLOB
- Date types store more efficiently than string representations

### Storage Engine Differences
- Column Store: Optimized for analytical queries, compression
- Row Store: Optimized for transactional queries
- Choose appropriate storage type based on usage pattern

## Best Practices

1. **Use appropriate data types** - Choose the smallest type that fits your needs
2. **Prefer specific over generic** - Use `DECIMAL` instead of `DOUBLE` for money
3. **Consider Unicode** - Use `NVARCHAR` for any text that might contain non-ASCII
4. **Handle NULLs explicitly** - Don't assume values are non-NULL
5. **Use functions for conversion** - Prefer `TO_` functions over `CAST` for dates/times
6. **Document assumptions** - Comment on data type choices and constraints

```

### references/skill-reference-guide.md

```markdown
# SQLScript Skill Reference Guide

This guide provides quick navigation to all reference documentation in the sap-sqlscript skill.

## Reference Files Overview

| File | Description | Lines | Use When |
|------|-------------|-------|----------|
| [syntax-reference.md](syntax-reference.md) | Complete SQLScript syntax patterns | ~564 | Looking up CREATE PROCEDURE, function syntax, control flow |
| [built-in-functions.md](built-in-functions.md) | All built-in function categories | ~518 | Finding string, date, numeric, aggregate, window functions |
| [data-types.md](data-types.md) | Data type documentation | ~187 | Choosing correct data types, type conversion |
| [exception-handling.md](exception-handling.md) | Error handling patterns | ~491 | Implementing EXIT HANDLER, SIGNAL, RESIGNAL |
| [amdp-integration.md](amdp-integration.md) | AMDP implementation guide | ~527 | Creating AMDP classes, method types, ABAP integration |
| [performance-guide.md](performance-guide.md) | Optimization techniques | ~406 | Optimizing procedures, avoiding anti-patterns |
| [advanced-features.md](advanced-features.md) | Advanced SQLScript features | ~846 | Lateral joins, JSON, query hints, CE functions |
| [troubleshooting.md](troubleshooting.md) | Common errors and solutions | ~540 | Debugging errors, resolving common issues |

---

## Quick Navigation by Topic

### Getting Started
- **Syntax basics**: [syntax-reference.md](syntax-reference.md) - Procedure, function, anonymous block syntax
- **Data types**: [data-types.md](data-types.md) - All supported types and conversion functions
- **Built-in functions**: [built-in-functions.md](built-in-functions.md) - String, date, numeric functions

### Core Development
- **Stored Procedures**: [syntax-reference.md](syntax-reference.md) - CREATE PROCEDURE patterns
- **User-Defined Functions**: [syntax-reference.md](syntax-reference.md) - Scalar and table UDFs
- **Variables & Table Types**: [syntax-reference.md](syntax-reference.md) - Declaration patterns
- **Control Structures**: [syntax-reference.md](syntax-reference.md) - IF, WHILE, FOR, LOOP
- **Cursors**: [syntax-reference.md](syntax-reference.md) - DECLARE, OPEN, FETCH, CLOSE

### Error Handling
- **EXIT HANDLER**: [exception-handling.md](exception-handling.md) - Basic and advanced patterns
- **CONDITION**: [exception-handling.md](exception-handling.md) - Named conditions
- **SIGNAL/RESIGNAL**: [exception-handling.md](exception-handling.md) - User-defined exceptions
- **Error codes**: [exception-handling.md](exception-handling.md) - Common SQL error codes
- **Error logging**: [exception-handling.md](exception-handling.md) - Logging patterns

### ABAP Integration
- **AMDP Classes**: [amdp-integration.md](amdp-integration.md) - Class structure and interface
- **AMDP Methods**: [amdp-integration.md](amdp-integration.md) - Procedures, functions, CDS
- **Data Type Mapping**: [amdp-integration.md](amdp-integration.md) - ABAP to SQLScript types
- **AMDP Restrictions**: [amdp-integration.md](amdp-integration.md) - Limitations and workarounds
- **Debugging AMDP**: [amdp-integration.md](amdp-integration.md) - Eclipse ADT debugging

### Performance Optimization
- **Code-to-Data**: [performance-guide.md](performance-guide.md) - Fundamental paradigm
- **Declarative vs Imperative**: [performance-guide.md](performance-guide.md) - When to use each
- **Engine Mixing**: [performance-guide.md](performance-guide.md) - Avoiding Row/Column store issues
- **Cursor Performance**: [performance-guide.md](performance-guide.md) - When cursors are acceptable
- **Memory Management**: [performance-guide.md](performance-guide.md) - Large result sets
- **Index Strategies**: [performance-guide.md](performance-guide.md) - Index usage in SQLScript

### Functions Reference
- **String Functions**: [built-in-functions.md](built-in-functions.md) - SUBSTRING, CONCAT, TRIM, etc.
- **Numeric Functions**: [built-in-functions.md](built-in-functions.md) - ROUND, ABS, MOD, etc.
- **Date/Time Functions**: [built-in-functions.md](built-in-functions.md) - ADD_DAYS, EXTRACT, etc.
- **Conversion Functions**: [built-in-functions.md](built-in-functions.md) - CAST, TO_VARCHAR, etc.
- **Aggregate Functions**: [built-in-functions.md](built-in-functions.md) - SUM, COUNT, AVG, etc.
- **Window Functions**: [built-in-functions.md](built-in-functions.md) - ROW_NUMBER, RANK, LAG, LEAD
- **NULL Handling**: [built-in-functions.md](built-in-functions.md) - COALESCE, IFNULL, NULLIF

### Advanced Features
- **Lateral Joins**: [advanced-features.md](advanced-features.md) - CROSS APPLY, OUTER APPLY
- **JSON Support**: [advanced-features.md](advanced-features.md) - JSON_VALUE, JSON_TABLE
- **Query Hints**: [advanced-features.md](advanced-features.md) - INDEX, LOOKUPS, NO_ROW_LOCK
- **Currency Conversion**: [advanced-features.md](advanced-features.md) - CONVERT_CURRENCY
- **Unit Conversion**: [advanced-features.md](advanced-features.md) - CONVERT_UNIT
- **CE Functions**: [advanced-features.md](advanced-features.md) - Calculation Engine functions
- **Array Functions**: [advanced-features.md](advanced-features.md) - Array aggregation
- **Pragmas**: [advanced-features.md](advanced-features.md) - Compiler directives

### Troubleshooting
- **Common Errors**: [troubleshooting.md](troubleshooting.md) - Error messages and solutions
- **Invalid Column/Table**: [troubleshooting.md](troubleshooting.md) - Name resolution issues
- **Type Conversion**: [troubleshooting.md](troubleshooting.md) - Implicit/explicit conversion
- **Performance Issues**: [troubleshooting.md](troubleshooting.md) - Memory and timeout errors
- **Security Errors**: [troubleshooting.md](troubleshooting.md) - Privilege and authorization
- **AMDP Errors**: [troubleshooting.md](troubleshooting.md) - AMDP-specific issues
- **Debugging Strategies**: [troubleshooting.md](troubleshooting.md) - Step-by-step debugging

---

## Common Use Case Mapping

| I want to... | Reference File |
|--------------|----------------|
| Create a stored procedure | [syntax-reference.md](syntax-reference.md) |
| Create a table function | [syntax-reference.md](syntax-reference.md) |
| Handle errors in my procedure | [exception-handling.md](exception-handling.md) |
| Create an AMDP class | [amdp-integration.md](amdp-integration.md) |
| Optimize slow procedure | [performance-guide.md](performance-guide.md) |
| Work with JSON data | [advanced-features.md](advanced-features.md) |
| Convert currency values | [advanced-features.md](advanced-features.md) |
| Fix a specific error | [troubleshooting.md](troubleshooting.md) |
| Find a specific function | [built-in-functions.md](built-in-functions.md) |
| Choose the right data type | [data-types.md](data-types.md) |

---

## Search Patterns

Use these patterns to search within reference files:

```
# Find all function signatures
grep -E "^[A-Z_]+\(" references/*.md

# Find all error codes
grep -E "SQL_ERROR_CODE|error code" references/*.md

# Find all examples
grep -A5 "Example:" references/*.md

# Find AMDP patterns
grep -E "BY DATABASE|AMDP" references/*.md
```

---

## Version Information

- **SAP HANA Platform**: 2.0 SPS07
- **SAP HANA Cloud**: QRC 3/2025
- **AMDP Support**: NetWeaver 7.40 SP05+
- **Last Updated**: 2025-12-27

---

## Related Skills

For comprehensive SAP development, combine this skill with:

- **sap-abap** - ABAP programming patterns for AMDP context
- **sap-cap-capire** - CAP framework database procedures integration
- **sap-hana-cli** - HANA CLI for procedure deployment and testing
- **sap-abap-cds** - CDS views that consume SQLScript procedures
- **sap-btp-cloud-platform** - BTP deployment of HANA artifacts

```

### references/glossary.md

```markdown
# SQLScript Glossary

Comprehensive glossary of SQLScript and SAP HANA database terminology.

---

## A

### AMDP (ABAP Managed Database Procedure)
A technology that allows SQLScript procedures to be written and managed within ABAP classes. Requires the `IF_AMDP_MARKER_HDB` interface and NetWeaver 7.40 SP05+.

### Anonymous Block
A SQLScript code block executed immediately without being stored in the database. Uses `DO BEGIN ... END;` syntax. Useful for testing and ad-hoc operations.

### Array
A one-dimensional ordered collection of elements of the same type. Declared with `ARRAY` keyword. Uses 1-based indexing. Cannot be returned from procedures.

---

## B

### Binding
The process of assigning host variables to SQL statements at runtime. In SQLScript, variables are bound using the colon prefix (`:variable_name`).

### Block Statement
A compound statement enclosed in `BEGIN ... END` that groups multiple statements. Can contain declarations, executable statements, and exception handlers.

---

## C

### Calculation Engine (CE)
SAP HANA's internal execution engine optimized for analytical processing. CE functions (e.g., `CE_CALC`, `CE_JOIN`) provide direct access to this engine but are deprecated in favor of SQL-based approaches.

### Code-to-Data Paradigm
The fundamental principle of pushing computation to where data resides (in the database) rather than moving data to the application layer. This minimizes data transfer and leverages HANA's in-memory processing.

### Column Store
SAP HANA's primary storage format where data is stored by columns rather than rows. Optimized for analytical queries, aggregations, and compression. Most tables should use column store.

### Condition
A named association with a specific SQL error code. Declared with `DECLARE condition_name CONDITION FOR SQL_ERROR_CODE number;`. Used with EXIT HANDLER for targeted error handling.

### Cursor
A database object that allows row-by-row processing of query results. Pattern: Declare → Open → Fetch → Close. Use sparingly as cursors bypass the optimizer.

---

## D

### DDL (Data Definition Language)
SQL statements that define database structure: CREATE, ALTER, DROP, TRUNCATE. DDL operations are auto-committed in HANA.

### Declarative Logic
SQL-based code that describes *what* result is wanted without specifying *how* to compute it. Converted to data flow graphs and executed in parallel. Preferred over imperative logic.

### Default Schema
The schema used when object names are not fully qualified. Set with `DEFAULT SCHEMA` clause in procedures or session-level `SET SCHEMA`.

### DML (Data Manipulation Language)
SQL statements that manipulate data: SELECT, INSERT, UPDATE, DELETE, MERGE. DML operations respect transaction boundaries.

### DUMMY
A single-row, single-column system table used for evaluating expressions without needing actual table data. `SELECT :variable FROM DUMMY;`

### Dynamic SQL
SQL statements constructed and executed at runtime using `EXECUTE IMMEDIATE`. Avoid when possible as it prevents optimization and poses security risks.

---

## E

### Engine Mixing
The anti-pattern of combining Row Store and Column Store operations in a single query, or mixing CE functions with SQL. Causes performance degradation due to data format conversion.

### EXIT HANDLER
An exception handler that executes when a specified condition occurs, then exits the current block. Declared with `DECLARE EXIT HANDLER FOR condition_value statement;`.

---

## F

### Function (UDF)
User-Defined Function. Two types:
- **Scalar UDF**: Returns a single value
- **Table UDF**: Returns a table (must be read-only)

---

## H

### HDI (HANA Deployment Infrastructure)
A container-based deployment model for SAP HANA artifacts. Provides isolated schema namespaces and supports version-controlled deployments via `.hdbtable`, `.hdbprocedure` files.

### Host Variable
A variable in SQLScript that holds values passed to or from SQL statements. Referenced with colon prefix (`:variable`).

---

## I

### Imperative Logic
Procedural code using control structures (IF, WHILE, FOR, LOOP) that specifies *how* to compute results step by step. Executes sequentially and prevents parallel processing. Use only when necessary.

### Implicit Declaration
Creating a table variable without explicit DECLARE by assigning a SELECT result: `lt_result = SELECT * FROM table;`

### INVOKER
SQL Security mode where the procedure executes with the privileges of the calling user. Compare with DEFINER mode.

---

## L

### L-Value
An expression that can appear on the left side of an assignment (can be assigned to). In SQLScript: scalar variables, table variables.

### Lateral Join
A join where the right side can reference columns from the left side. Uses `CROSS APPLY` or `OUTER APPLY` syntax. Enables correlated subquery-like patterns in FROM clause.

---

## N

### NULL
The absence of a value. Handled with COALESCE, IFNULL, NULLIF functions. Uninitialized variables default to NULL.

---

## O

### OUTER APPLY
A lateral join that returns all rows from the left side even when the right side returns no rows (similar to LEFT JOIN). Compare with CROSS APPLY.

---

## P

### Parallel Execution
The ability of SAP HANA to execute multiple operations simultaneously. Declarative SQL enables parallel execution; imperative logic prevents it.

### Plan Visualizer
SAP HANA tool for analyzing query execution plans. Shows operator breakdown, data flow, and identifies performance bottlenecks.

### Pragma
A compiler directive that influences code generation without changing logic. Example: `/*#RESULT_CACHE*/` for result caching.

### Procedure
A stored database object containing SQLScript logic with optional input/output parameters. Created with `CREATE PROCEDURE` and invoked with `CALL`.

---

## R

### READS SQL DATA
Procedure option indicating read-only operation (no DML changes). Required for procedures called from SQL expressions and table functions.

### RESIGNAL
Statement that re-throws the current exception from within an exception handler, optionally with a modified message.

### Row Store
SAP HANA storage format where data is stored by rows. Used for OLTP-style operations requiring frequent single-row access. Less common than Column Store.

### R-Value
An expression that can appear on the right side of an assignment (provides a value). In SQLScript: literals, expressions, function results.

---

## S

### Scalar Type
A data type that holds a single value (INTEGER, VARCHAR, DATE, etc.) as opposed to table types or arrays.

### SIGNAL
Statement that throws a user-defined exception with a custom error code (10000-19999) and message. `SIGNAL condition SET MESSAGE_TEXT = 'message';`

### SQL Security
Determines execution privileges for procedures:
- **DEFINER**: Runs with owner's privileges
- **INVOKER**: Runs with caller's privileges

### SQLEXCEPTION
A generic condition that matches any SQL exception. Used with EXIT HANDLER: `DECLARE EXIT HANDLER FOR SQLEXCEPTION ...`

### SQLScript
SAP HANA's procedural extension to SQL. Combines declarative SQL with imperative control structures for complex database logic.

---

## T

### Table Type
A user-defined type defining a table structure. Created with `CREATE TYPE name AS TABLE (...)`. Used for procedure parameters and structured data.

### Table Variable
A variable that holds a table structure and data. Can be implicitly or explicitly declared. Referenced with colon prefix in SQL context.

### Transaction
A logical unit of work comprising one or more database operations. Bounded by COMMIT or ROLLBACK. Note: DDL auto-commits.

---

## U

### UDF (User-Defined Function)
See Function.

### UNION ALL
Set operation combining results without removing duplicates. Faster than UNION which eliminates duplicates.

---

## V

### Variable Scope
The visibility of a variable within code:
- **Block scope**: Variables declared in a block are visible only within that block
- **Procedure scope**: Parameters visible throughout the procedure

---

## W

### Window Function
A function that performs calculations across a set of rows related to the current row, without collapsing results. Examples: ROW_NUMBER, RANK, LAG, LEAD, SUM OVER.

---

## Common Abbreviations

| Abbreviation | Full Form |
|--------------|-----------|
| AMDP | ABAP Managed Database Procedure |
| CE | Calculation Engine |
| CDS | Core Data Services |
| DDL | Data Definition Language |
| DML | Data Manipulation Language |
| HDI | HANA Deployment Infrastructure |
| HANA | High-performance ANalytic Appliance |
| UDF | User-Defined Function |
| XSA | Extended Application Services, Advanced |

---

## Related Terms

For function-specific terminology, see [built-in-functions.md](built-in-functions.md).
For AMDP-specific terminology, see [amdp-integration.md](amdp-integration.md).
For data type details, see [data-types.md](data-types.md).

```

### references/syntax-reference.md

```markdown
# SQLScript Complete Syntax Reference

## Table of Contents

- [Procedure Syntax](#procedure-syntax)
  - [CREATE PROCEDURE](#create-procedure)
  - [Parameter Modes](#parameter-modes)
  - [Procedure Body](#procedure-body)
  - [DROP PROCEDURE](#drop-procedure)
- [Function Syntax](#function-syntax)
  - [CREATE Scalar Function](#create-scalar-function)
  - [CREATE Table Function](#create-table-function)
  - [DROP FUNCTION](#drop-function)
- [Anonymous Block Syntax](#anonymous-block-syntax)
- [Variable Declaration](#variable-declaration)
  - [Scalar Variables](#scalar-variables)
  - [Table Variables](#table-variables)
  - [Array Variables](#array-variables)
- [Control Flow Statements](#control-flow-statements)
  - [IF Statement](#if-statement)
  - [CASE Statement](#case-statement)
  - [WHILE Loop](#while-loop)
  - [FOR Loop](#for-loop)
  - [LOOP Statement](#loop-statement)
- [Cursor Operations](#cursor-operations)
  - [DECLARE CURSOR](#declare-cursor)
  - [OPEN Cursor](#open-cursor)
  - [FETCH Cursor](#fetch-cursor)
  - [CLOSE Cursor](#close-cursor)
- [Exception Handling](#exception-handling-1)
  - [DECLARE EXIT HANDLER](#declare-exit-handler)
  - [DECLARE CONDITION](#declare-condition)
  - [SIGNAL Statement](#signal-statement)
  - [RESIGNAL Statement](#resignal-statement)
- [Table Type Definition](#table-type-definition)
- [Assignment Statements](#assignment-statements)
  - [Simple Assignment](#simple-assignment)
  - [SELECT INTO](#select-into)
- [DDL Statements](#ddl-statements)
  - [CREATE TABLE](#create-table)
  - [DROP TABLE](#drop-table)
  - [CREATE VIEW](#create-view)
  - [DROP VIEW](#drop-view)
- [Built-in Functions](#built-in-functions)
  - [Aggregate Functions](#aggregate-functions)
  - [Window Functions](#window-functions)
  - [String Functions](#string-functions)
  - [Date/Time Functions](#datetime-functions)
  - [Conversion Functions](#conversion-functions)
- [Special Constructs](#special-constructs)
  - [DUMMY Table](#dummy-table)
  - [SESSION_USER](#session_user)
  - [CURRENT_SCHEMA](#current_schema)

## Procedure Syntax

### CREATE PROCEDURE

```sql
CREATE [OR REPLACE] PROCEDURE <schema_name>.<procedure_name>
  (
    [IN <parameter_name> <sql_type> [DEFAULT <default_value>]],
    [OUT <parameter_name> <sql_type>],
    [INOUT <parameter_name> <sql_type>]
  )
  LANGUAGE SQLSCRIPT
  [SQL SECURITY {DEFINER | INVOKER}]
  [DEFAULT SCHEMA <schema_name>]
  [READS SQL DATA]
  [WITH RESULT VIEW <view_name>]
  [DETERMINISTIC]
AS
BEGIN
  [SEQUENTIAL EXECUTION]
  <procedure_body>
END;
```

> **Note:** `SEQUENTIAL EXECUTION` forces the procedure to execute sequentially without parallelism. This is rarely needed but can be useful for procedures that rely on side effects or specific ordering guarantees.

### Parameter Modes

| Mode | Description |
|------|-------------|
| `IN` | Input parameter (read-only within procedure) |
| `OUT` | Output parameter (write-only, initial value undefined) |
| `INOUT` | Input and output (read/write) |

### Security Modes

| Mode | Description |
|------|-------------|
| `SQL SECURITY DEFINER` | Execute with privileges of procedure owner |
| `SQL SECURITY INVOKER` | Execute with privileges of caller |

---

## Function Syntax

### Scalar User-Defined Function

```sql
CREATE [OR REPLACE] FUNCTION <schema_name>.<function_name>
  (
    <parameter_name> <sql_type> [DEFAULT <value>],
    ...
  )
RETURNS <return_type>
LANGUAGE SQLSCRIPT
[SQL SECURITY {DEFINER | INVOKER}]
[DEFAULT SCHEMA <schema_name>]
[DETERMINISTIC]
AS
BEGIN
  DECLARE result <return_type>;
  -- function logic
  RETURN result;
END;
```

### Table User-Defined Function

```sql
CREATE [OR REPLACE] FUNCTION <schema_name>.<function_name>
  (
    <parameter_name> <sql_type> [DEFAULT <value>],
    ...
  )
RETURNS TABLE (
  <column_name> <sql_type>,
  ...
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA
[DEFAULT SCHEMA <schema_name>]
AS
BEGIN
  RETURN SELECT <columns> FROM <source>;
END;
```

---

## Anonymous Block Syntax

```sql
DO [(<parameter_clause>)]
BEGIN [SEQUENTIAL EXECUTION]
  <block_body>
END;
```

### With Parameters

```sql
DO (
  IN iv_input INTEGER => 100,
  OUT ov_output INTEGER => ?
)
BEGIN
  ov_output = :iv_input * 2;
END;
```

---

## Variable Declaration Syntax

### Scalar Variables

```sql
DECLARE <variable_name> <sql_type> [:= <initial_value>];
DECLARE <var1>, <var2> <sql_type>;  -- Multiple variables same type
```

### Constant Variables

```sql
DECLARE <constant_name> CONSTANT <sql_type> := <value>;
```

### Table Variables

```sql
-- Inline definition
DECLARE <table_var> TABLE (
  <column_name> <sql_type> [NOT NULL],
  ...
);

-- From existing table structure
DECLARE <table_var> TABLE LIKE <table_name>;
DECLARE <table_var> TABLE LIKE :<other_table_var>;

-- From table type
DECLARE <table_var> <table_type_name>;
```

### Array Variables

```sql
DECLARE <array_name> <sql_type> ARRAY;
DECLARE <array_name> <sql_type> ARRAY := ARRAY(<value1>, <value2>, ...);
```

---

## Assignment Syntax

### Scalar Assignment

```sql
<variable> := <expression>;
<variable> = <expression>;  -- Alternative syntax
```

### SELECT INTO

```sql
SELECT <column> INTO <variable> FROM <table> [WHERE ...];
SELECT <col1>, <col2> INTO <var1>, <var2> FROM <table>;
```

### Table Variable Assignment

```sql
<table_var> = SELECT <columns> FROM <table>;
<table_var> = :<other_table_var>;
```

---

## Control Flow Syntax

### IF-THEN-ELSE

```sql
IF <condition> THEN
  <statements>
[ELSEIF <condition> THEN
  <statements>]
[ELSE
  <statements>]
END IF;
```

### CASE Expression

```sql
-- Simple CASE
CASE <expression>
  WHEN <value1> THEN <result1>
  WHEN <value2> THEN <result2>
  ELSE <default_result>
END

-- Searched CASE
CASE
  WHEN <condition1> THEN <result1>
  WHEN <condition2> THEN <result2>
  ELSE <default_result>
END
```

### WHILE Loop

```sql
WHILE <condition> DO
  <statements>
END WHILE;
```

### DO n TIMES Loop

Repeat a block a fixed number of times:

```sql
DO <n> TIMES
BEGIN
  <statements>
END;
```

**Example:**
```sql
DO 10 TIMES
BEGIN
  INSERT INTO "LOG_TABLE" (message) VALUES ('Iteration');
END;
```

### FOR Loop

```sql
-- Numeric range (inclusive on both ends)
FOR <var> IN [REVERSE] <start>..<end> DO
  <statements>
END FOR;

-- Cursor iteration
FOR <row_var> AS <cursor_name> DO
  <statements using row_var.column_name>
END FOR;

-- Inline cursor
FOR <row_var> AS (SELECT <columns> FROM <table>) DO
  <statements>
END FOR;
```

> **Range Semantics:** The numeric FOR loop range is **inclusive** on both bounds. `FOR i IN 1..5` iterates with i = 1, 2, 3, 4, 5 (five iterations total).

### LOOP with EXIT

```sql
LOOP
  <statements>
  IF <condition> THEN
    BREAK;  -- or LEAVE
  END IF;
  [CONTINUE;]  -- Skip to next iteration
END LOOP;
```

---

## Cursor Syntax

### Declaration

```sql
DECLARE CURSOR <cursor_name> FOR
  <select_statement>;

-- With parameters
DECLARE CURSOR <cursor_name> (<param> <type>) FOR
  SELECT * FROM <table> WHERE col = :<param>;
```

### Operations

```sql
OPEN <cursor_name>;
OPEN <cursor_name> (<param_value>);

FETCH <cursor_name> INTO <var1>, <var2>, ...;

CLOSE <cursor_name>;
```

### Cursor Attributes

| Attribute | Type | Description | Typical Usage |
|-----------|------|-------------|---------------|
| `<cursor>::ISCLOSED` | BOOLEAN | TRUE if cursor is closed | Check before OPEN to avoid "already open" error |
| `<cursor>::NOTFOUND` | BOOLEAN | TRUE if FETCH found no row | Loop termination condition after FETCH |
| `<cursor>::ROWCOUNT` | INTEGER | Number of rows fetched so far | Progress tracking, batch processing limits |

**Usage Example:**
```sql
WHILE NOT cur::NOTFOUND DO  -- Check NOTFOUND to exit loop
  FETCH cur INTO lv_var;
  IF cur::ROWCOUNT > 1000 THEN  -- Limit processing
    BREAK;
  END IF;
END WHILE;
```

---

## Table Type Syntax

### CREATE TYPE

```sql
CREATE TYPE <schema_name>.<type_name> AS TABLE (
  <column_name> <sql_type> [NOT NULL],
  ...
);
```

### DROP TYPE

```sql
DROP TYPE <schema_name>.<type_name> [CASCADE];
```

---

## Exception Handling Syntax

### EXIT HANDLER

```sql
DECLARE EXIT HANDLER FOR <condition>
  <statement>;

DECLARE EXIT HANDLER FOR <condition>
BEGIN
  <statements>
END;
```

### CONTINUE HANDLER

CONTINUE HANDLER catches exceptions and continues execution of the procedure (unlike EXIT HANDLER which suspends execution):

```sql
DECLARE CONTINUE HANDLER FOR <condition>
  <statement>;

DECLARE CONTINUE HANDLER FOR <condition>
BEGIN
  <statements>
END;
```

> **Note:** Use CONTINUE HANDLER when you want to log errors and continue processing. See `references/exception-handling.md` for detailed comparison of EXIT vs CONTINUE handlers.

### Condition Declaration

```sql
DECLARE <condition_name> CONDITION FOR SQL_ERROR_CODE <number>;
```

### SIGNAL

```sql
SIGNAL <condition_name>;
SIGNAL <condition_name> SET MESSAGE_TEXT = '<message>';
SIGNAL SQL_ERROR_CODE <number> SET MESSAGE_TEXT = '<message>';
```

### RESIGNAL

```sql
RESIGNAL;
RESIGNAL <condition_name>;
RESIGNAL SET MESSAGE_TEXT = '<message>';
```

---

## Table Variable Operators

### INSERT

```sql
:<table_var>.INSERT((<value1>, <value2>, ...));
:<table_var>.INSERT(:<other_table_var>);
:<table_var>.INSERT(:<other_table_var>, <row_number>);
```

### UPDATE

```sql
:<table_var>.UPDATE((<new_value1>, <new_value2>, ...), <row_number>);
```

### DELETE

```sql
:<table_var>.DELETE(<row_number>);
:<table_var>.DELETE(<start_row>, <count>);
```

### SEARCH

```sql
<position> = :<table_var>.SEARCH((<column_name>, <search_value>), <start_position>);
```

---

## UNNEST Function

Convert array to table:

```sql
UNNEST(<array1> [, <array2>, ...]) [WITH ORDINALITY]
  AS <table_alias> (<col1> [, <col2>, ...] [, <ordinality_col>])
```

**Example:**
```sql
DECLARE arr INTEGER ARRAY := ARRAY(10, 20, 30);
lt_result = SELECT * FROM UNNEST(:arr) AS t(value);
```

---

## Dynamic SQL Syntax

### EXECUTE IMMEDIATE

```sql
EXECUTE IMMEDIATE <sql_string>;
EXECUTE IMMEDIATE <sql_string> INTO <variable>;
EXECUTE IMMEDIATE <sql_string> USING <param1>, <param2>, ...;
```

### EXEC (Procedure Call)

```sql
EXEC <sql_string>;
```

> **Warning:** Avoid dynamic SQL for performance and security reasons.

---

## Operators

### Arithmetic
| Operator | Description |
|----------|-------------|
| `+` | Addition |
| `-` | Subtraction |
| `*` | Multiplication |
| `/` | Division |
| `%` | Modulo |

### String
| Operator | Description |
|----------|-------------|
| `\|\|` | Concatenation |

### Comparison
| Operator | Description |
|----------|-------------|
| `=` | Equal |
| `!=`, `<>` | Not equal |
| `<` | Less than |
| `>` | Greater than |
| `<=` | Less than or equal |
| `>=` | Greater than or equal |
| `BETWEEN` | Range check |
| `IN` | Set membership |
| `LIKE` | Pattern matching |
| `IS NULL` | NULL check |
| `IS NOT NULL` | Not NULL check |

### Logical
| Operator | Description |
|----------|-------------|
| `AND` | Logical AND |
| `OR` | Logical OR |
| `NOT` | Logical NOT |

---

## Comments

```sql
-- Single line comment

/* Multi-line
   comment */

/**
 * Documentation comment
 */
```

```

### references/built-in-functions.md

```markdown
# SQLScript Built-in Functions Reference

## Table of Contents

- [String Functions](#string-functions)
  - [Character Manipulation](#character-manipulation)
  - [String Operations](#string-operations)
  - [Case Conversion](#case-conversion)
  - [Trimming and Padding](#trimming-and-padding)
  - [Search and Replace](#search-and-replace)
- [Numeric Functions](#numeric-functions)
  - [Basic Operations](#basic-operations)
  - [Rounding](#rounding)
  - [Logarithmic](#logarithmic)
  - [Trigonometric](#trigonometric)
  - [Bitwise Operations](#bitwise-operations)
  - [Random](#random)
- [Date and Time Functions](#date-and-time-functions)
  - [Current Date/Time](#current-datetime)
  - [Date Extraction](#date-extraction)
  - [EXTRACT Function](#extract-function)
  - [Date Arithmetic](#date-arithmetic)
  - [Date Utilities](#date-utilities)
- [Conversion Functions](#conversion-functions)
  - [Type Conversion](#type-conversion)
  - [String Conversion](#string-conversion)
  - [Date/Time Conversion](#datatime-conversion)
  - [Binary Conversion](#binary-conversion)
  - [Date/Time Format Codes](#datatime-format-codes)
- [Aggregate Functions](#aggregate-functions)
- [Window Functions](#window-functions)
  - [Syntax](#syntax)
  - [Frame Clause](#frame-clause)
  - [Ranking Functions](#ranking-functions)
  - [Navigation Functions](#navigation-functions)
- [NULL Handling Functions](#null-handling-functions)
- [Miscellaneous Functions](#miscellaneous-functions)
  - [SESSION_CONTEXT Keys](#session-context-keys)
- [SAP-Specific Functions](#sap-specific-functions)
  - [TO_DATS](#to_dats)
  - [TO_TIMS](#to_tims)
  - [CONVERT_CURRENCY](#convert_currency)
  - [CONVERT_UNIT](#convert_unit)
- [SQLScript Libraries](#sqlscript-libraries)

## String Functions

### Character Manipulation

| Function | Description | Example |
|----------|-------------|---------|
| `ASCII(string)` | Returns ASCII value of first character | `ASCII('A')` → 65 |
| `CHAR(n)` | Returns character for ASCII value | `CHAR(65)` → 'A' |
| `NCHAR(n)` | Returns Unicode character | `NCHAR(8364)` → '€' |
| `UNICODE(string)` | Returns Unicode value | `UNICODE('€')` → 8364 |

### String Operations

| Function | Description | Example |
|----------|-------------|---------|
| `CONCAT(s1, s2)` | Concatenates strings | `CONCAT('A', 'B')` → 'AB' |
| `LENGTH(string)` | Returns string length | `LENGTH('Hello')` → 5 |
| `LEFT(string, n)` | Returns leftmost n characters | `LEFT('Hello', 2)` → 'He' |
| `RIGHT(string, n)` | Returns rightmost n characters | `RIGHT('Hello', 2)` → 'lo' |
| `SUBSTRING(s, start, len)` | Extracts substring | `SUBSTRING('Hello', 2, 3)` → 'ell' |
| `SUBSTR_BEFORE(s, pattern)` | Returns substring before pattern | `SUBSTR_BEFORE('a-b', '-')` → 'a' |
| `SUBSTR_AFTER(s, pattern)` | Returns substring after pattern | `SUBSTR_AFTER('a-b', '-')` → 'b' |

### Case Conversion

| Function | Description | Example |
|----------|-------------|---------|
| `LOWER(string)` / `LCASE(string)` | Converts to lowercase | `LOWER('ABC')` → 'abc' |
| `UPPER(string)` / `UCASE(string)` | Converts to uppercase | `UPPER('abc')` → 'ABC' |
| `INITCAP(string)` | Capitalizes first letter of each word | `INITCAP('hello world')` → 'Hello World' |

### Trimming and Padding

| Function | Description | Example |
|----------|-------------|---------|
| `TRIM(string)` | Removes leading/trailing spaces | `TRIM('  AB  ')` → 'AB' |
| `LTRIM(string [, chars])` | Removes leading characters | `LTRIM('00123', '0')` → '123' |
| `RTRIM(string [, chars])` | Removes trailing characters | `RTRIM('123  ')` → '123' |
| `LPAD(s, n [, pad])` | Left-pads to length n | `LPAD('5', 3, '0')` → '005' |
| `RPAD(s, n [, pad])` | Right-pads to length n | `RPAD('5', 3, '0')` → '500' |

### Search and Replace

| Function | Description | Example |
|----------|-------------|---------|
| `LOCATE(search, string)` | Returns position of substring | `LOCATE('ll', 'Hello')` → 3 |
| `REPLACE(s, old, new)` | Replaces occurrences | `REPLACE('abc', 'b', 'x')` → 'axc' |
| `REVERSE(string)` | Reverses string | `REVERSE('abc')` → 'cba' |

---

## Numeric Functions

### Basic Operations

| Function | Description | Example |
|----------|-------------|---------|
| `ABS(n)` | Absolute value | `ABS(-5)` → 5 |
| `SIGN(n)` | Sign (-1, 0, 1) | `SIGN(-5)` → -1 |
| `MOD(n, m)` | Modulo | `MOD(10, 3)` → 1 |
| `POWER(n, m)` | n raised to power m | `POWER(2, 3)` → 8 |
| `SQRT(n)` | Square root | `SQRT(16)` → 4 |
| `EXP(n)` | e raised to power n | `EXP(1)` → 2.718... |

### Rounding

| Function | Description | Example |
|----------|-------------|---------|
| `CEIL(n)` / `CEILING(n)` | Rounds up | `CEIL(4.2)` → 5 |
| `FLOOR(n)` | Rounds down | `FLOOR(4.8)` → 4 |
| `ROUND(n [, d])` | Rounds to d decimal places | `ROUND(4.567, 2)` → 4.57 |
| `TRUNC(n [, d])` | Truncates to d decimal places | `TRUNC(4.567, 2)` → 4.56 |

### Logarithmic

| Function | Description | Example |
|----------|-------------|---------|
| `LN(n)` | Natural logarithm | `LN(2.718)` → 1.0 |
| `LOG(base, n)` | Logarithm with base | `LOG(10, 100)` → 2 |

### Trigonometric

| Function | Description |
|----------|-------------|
| `SIN(n)` | Sine (radians) |
| `COS(n)` | Cosine (radians) |
| `TAN(n)` | Tangent (radians) |
| `ASIN(n)` | Arc sine |
| `ACOS(n)` | Arc cosine |
| `ATAN(n)` | Arc tangent |
| `ATAN2(y, x)` | Arc tangent of y/x |
| `SINH(n)` | Hyperbolic sine |
| `COSH(n)` | Hyperbolic cosine |
| `TANH(n)` | Hyperbolic tangent |

### Bitwise Operations

| Function | Description | Example |
|----------|-------------|---------|
| `BITAND(a, b)` | Bitwise AND | `BITAND(12, 10)` → 8 |
| `BITOR(a, b)` | Bitwise OR | `BITOR(12, 10)` → 14 |
| `BITXOR(a, b)` | Bitwise XOR | `BITXOR(12, 10)` → 6 |
| `BITNOT(n)` | Bitwise NOT | `BITNOT(0)` → -1 |
| `BITCOUNT(n)` | Count set bits | `BITCOUNT(7)` → 3 |
| `BITSET(n, pos)` | Set bit at position | `BITSET(0, 3)` → 8 |
| `BITUNSET(n, pos)` | Unset bit at position | `BITUNSET(8, 3)` → 0 |

### Random

| Function | Description |
|----------|-------------|
| `RAND()` | Random number 0-1 |
| `RAND(seed)` | Seeded random |

---

## Date and Time Functions

### Current Date/Time

| Function | Description | Format |
|----------|-------------|--------|
| `CURRENT_DATE` | Current local date | YYYY-MM-DD |
| `CURRENT_TIME` | Current local time | HH:MI:SS |
| `CURRENT_TIMESTAMP` | Current local datetime | YYYY-MM-DD HH:MI:SS.FF |
| `CURRENT_UTCDATE` | Current UTC date | YYYY-MM-DD |
| `CURRENT_UTCTIME` | Current UTC time | HH:MI:SS |
| `CURRENT_UTCTIMESTAMP` | Current UTC datetime | YYYY-MM-DD HH:MI:SS.FF |
| `NOW()` | Same as CURRENT_TIMESTAMP | |

### Date Extraction

| Function | Description | Example |
|----------|-------------|---------|
| `YEAR(date)` | Extract year | `YEAR('2024-03-15')` → 2024 |
| `MONTH(date)` | Extract month (1-12) | `MONTH('2024-03-15')` → 3 |
| `DAYOFMONTH(date)` | Extract day (1-31) | `DAYOFMONTH('2024-03-15')` → 15 |
| `DAYOFYEAR(date)` | Day of year (1-366) | `DAYOFYEAR('2024-03-15')` → 75 |
| `DAYNAME(date)` | Name of day | `DAYNAME('2024-03-15')` → 'FRIDAY' |
| `WEEKDAY(date)` | Day of week (0-6, Mon=0) | `WEEKDAY('2024-03-15')` → 4 |
| `WEEK(date)` | Week number (1-53) | `WEEK('2024-03-15')` → 11 |
| `QUARTER(date)` | Quarter (1-4) | `QUARTER('2024-03-15')` → 1 |
| `HOUR(time)` | Extract hour (0-23) | `HOUR('14:30:00')` → 14 |
| `MINUTE(time)` | Extract minute (0-59) | `MINUTE('14:30:00')` → 30 |
| `SECOND(time)` | Extract second (0-59) | `SECOND('14:30:45')` → 45 |

### EXTRACT Function

```sql
EXTRACT(<part> FROM <datetime>)
```

Parts: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

### Date Arithmetic

| Function | Description | Example |
|----------|-------------|---------|
| `ADD_DAYS(date, n)` | Add n days | `ADD_DAYS('2024-01-01', 30)` |
| `ADD_MONTHS(date, n)` | Add n months | `ADD_MONTHS('2024-01-01', 3)` |
| `ADD_YEARS(date, n)` | Add n years | `ADD_YEARS('2024-01-01', 1)` |
| `ADD_SECONDS(ts, n)` | Add n seconds | `ADD_SECONDS(NOW(), 3600)` |
| `DAYS_BETWEEN(d1, d2)` | Days between dates | `DAYS_BETWEEN('2024-01-01', '2024-03-01')` → 60 |
| `MONTHS_BETWEEN(d1, d2)` | Months between dates | `MONTHS_BETWEEN('2024-01-15', '2024-04-15')` → 3 |
| `SECONDS_BETWEEN(t1, t2)` | Seconds between times | |
| `NANO100_BETWEEN(t1, t2)` | 100-nanosecond intervals | |
| `WORKDAYS_BETWEEN(d1, d2 [, locale])` | Working days between | |

### Date Utilities

| Function | Description |
|----------|-------------|
| `LAST_DAY(date)` | Last day of month |
| `NEXT_DAY(date)` | Next occurrence of day |
| `ISOWEEK(date)` | ISO week number |
| `UTCTOLOCAL(ts, tz)` | Convert UTC to local |
| `LOCALTOUTC(ts, tz)` | Convert local to UTC |

---

## Conversion Functions

### Type Conversion

| Function | Description |
|----------|-------------|
| `CAST(expr AS type)` | Convert to type |
| `TO_BIGINT(value)` | Convert to BIGINT |
| `TO_INTEGER(value)` / `TO_INT(value)` | Convert to INTEGER |
| `TO_SMALLINT(value)` | Convert to SMALLINT |
| `TO_TINYINT(value)` | Convert to TINYINT |
| `TO_DOUBLE(value)` | Convert to DOUBLE |
| `TO_REAL(value)` | Convert to REAL |
| `TO_DECIMAL(value [, p, s])` | Convert to DECIMAL |
| `TO_SMALLDECIMAL(value)` | Convert to SMALLDECIMAL |

### String Conversion

| Function | Description |
|----------|-------------|
| `TO_VARCHAR(value [, format])` | Convert to VARCHAR |
| `TO_NVARCHAR(value [, format])` | Convert to NVARCHAR |
| `TO_ALPHANUM(value)` | Convert to ALPHANUM |
| `TO_FIXEDCHAR(value, n)` | Convert to fixed-length char |

### Date/Time Conversion

| Function | Description |
|----------|-------------|
| `TO_DATE(string [, format])` | Convert to DATE |
| `TO_TIME(string [, format])` | Convert to TIME |
| `TO_TIMESTAMP(string [, format])` | Convert to TIMESTAMP |
| `TO_SECONDDATE(string [, format])` | Convert to SECONDDATE |
| `TO_DATS(date)` | Convert to SAP date (YYYYMMDD) |
| `TO_TIMS(time)` | Convert to SAP time (HHMMSS) |

### Binary Conversion

| Function | Description |
|----------|-------------|
| `TO_BINARY(value)` | Convert to BINARY |
| `TO_BLOB(value)` | Convert to BLOB |
| `TO_CLOB(value)` | Convert to CLOB |
| `TO_NCLOB(value)` | Convert to NCLOB |
| `HEXTOBIN(hex)` | Hex string to binary |
| `BINTOHEX(bin)` | Binary to hex string |
| `STRTOBIN(string, encoding)` | String to binary |
| `BINTOSTR(binary, encoding)` | Binary to string |

### Date/Time Format Codes

| Code | Description |
|------|-------------|
| `YYYY` | 4-digit year |
| `YY` | 2-digit year |
| `MM` | Month (01-12) |
| `MON` | Month abbreviation |
| `DD` | Day (01-31) |
| `DY` | Day abbreviation |
| `HH` / `HH12` | Hour (01-12) |
| `HH24` | Hour (00-23) |
| `MI` | Minute (00-59) |
| `SS` | Second (00-59) |
| `FF` | Fractional seconds |
| `AM` / `PM` | Meridian indicator |

---

## Aggregate Functions

| Function | Description |
|----------|-------------|
| `COUNT(*)` | Count all rows |
| `COUNT(column)` | Count non-NULL values |
| `COUNT(DISTINCT column)` | Count distinct values |
| `SUM(column)` | Sum of values |
| `AVG(column)` | Average of values |
| `MIN(column)` | Minimum value |
| `MAX(column)` | Maximum value |
| `MEDIAN(column)` | Median value |
| `STDDEV(column)` | Standard deviation |
| `VAR(column)` | Variance |
| `STRING_AGG(column [, delimiter])` | Concatenate strings |

---

## Window Functions

### Syntax

```sql
<function>(<args>) OVER (
  [PARTITION BY <columns>]
  [ORDER BY <columns> [ASC|DESC]]
  [<frame_clause>]
)
```

### Frame Clause

```sql
ROWS BETWEEN <start> AND <end>
RANGE BETWEEN <start> AND <end>
```

Frame bounds:
- `UNBOUNDED PRECEDING`
- `n PRECEDING`
- `CURRENT ROW`
- `n FOLLOWING`
- `UNBOUNDED FOLLOWING`

### Ranking Functions

| Function | Description |
|----------|-------------|
| `ROW_NUMBER()` | Unique sequential number |
| `RANK()` | Rank with gaps for ties |
| `DENSE_RANK()` | Rank without gaps |
| `NTILE(n)` | Distribute into n buckets |
| `PERCENT_RANK()` | Relative rank (0-1) |
| `CUME_DIST()` | Cumulative distribution |

### Navigation Functions

| Function | Description |
|----------|-------------|
| `LEAD(col [, n [, default]])` | Value from n rows ahead |
| `LAG(col [, n [, default]])` | Value from n rows behind |
| `FIRST_VALUE(col)` | First value in window |
| `LAST_VALUE(col)` | Last value in window |
| `NTH_VALUE(col, n)` | Nth value in window |

---

## NULL Handling Functions

| Function | Description |
|----------|-------------|
| `COALESCE(v1, v2, ...)` | First non-NULL value |
| `IFNULL(value, default)` | Return default if NULL |
| `NULLIF(v1, v2)` | NULL if v1 = v2 |
| `NVL(value, default)` | Same as IFNULL |

---

## Miscellaneous Functions

| Function | Description |
|----------|-------------|
| `CURRENT_SCHEMA` | Current schema name |
| `CURRENT_USER` | Current user name |
| `SESSION_USER` | Session user name |
| `SESSION_CONTEXT(key)` | Session context value |
| `RECORD_COUNT(table_var)` | Row count of table variable |
| `CARDINALITY(array)` | Array length |
| `SYSUUID` | Generate UUID |
| `HASH_MD5(value)` | MD5 hash |
| `HASH_SHA256(value)` | SHA-256 hash |

### SESSION_CONTEXT Keys

| Key | Description |
|-----|-------------|
| `'CLIENT'` | SAP client (mandant) |
| `'APPLICATIONUSER'` | Application user name |
| `'LOCALE'` | Session locale |
| `'LOCALE_SAP'` | SAP locale setting |

> **Note:** Available keys may vary by HANA version and configuration. Custom session variables can be set with `SET SESSION`.

**Example:**
```sql
SELECT SESSION_CONTEXT('CLIENT') AS client,
       SESSION_CONTEXT('APPLICATIONUSER') AS app_user
FROM DUMMY;
```

---

## SAP-Specific Functions

### TO_DATS

Convert DATE to SAP date format (YYYYMMDD string):

```sql
SELECT TO_DATS(CURRENT_DATE) FROM DUMMY;
-- Returns: '20241123'

-- Extract year-month portion
SELECT SUBSTRING(TO_DATS(CURRENT_DATE), 1, 6) FROM DUMMY;
-- Returns: '202411'
```

### TO_TIMS

Convert TIME to SAP time format (HHMMSS string):

```sql
SELECT TO_TIMS(CURRENT_TIME) FROM DUMMY;
-- Returns: '143022'
```

### CONVERT_CURRENCY

Currency conversion using SAP exchange rate tables:

```sql
CONVERT_CURRENCY(
  AMOUNT => <decimal_value>,
  SOURCE_UNIT => <source_currency>,
  TARGET_UNIT => <target_currency>,
  SCHEMA => <schema_name>,
  REFERENCE_DATE => <date>,
  CLIENT => <client_number>,
  CONVERSION_TYPE => <type>
)
```

**Example:**
```sql
SELECT CONVERT_CURRENCY(
  AMOUNT => 1000.00,
  SOURCE_UNIT => 'USD',
  SCHEMA => 'SAPABAP1',
  TARGET_UNIT => 'EUR',
  REFERENCE_DATE => CURRENT_DATE,
  CLIENT => '100',
  CONVERSION_TYPE => 'EURX'
) AS converted_amount
FROM DUMMY;
```

### CONVERT_UNIT

Unit of measure conversion using SAP UOM tables:

```sql
CONVERT_UNIT(
  QUANTITY => <decimal_value>,
  SOURCE_UNIT => <source_uom>,
  TARGET_UNIT => <target_uom>,
  SCHEMA => <schema_name>,
  CLIENT => <client_number>
)
```

**Example:**
```sql
-- Convert 1000 grams to kilograms
SELECT CONVERT_UNIT(
  QUANTITY => 1000.00,
  SOURCE_UNIT => 'G',
  SCHEMA => 'SAPABAP1',
  TARGET_UNIT => 'KG',
  CLIENT => '100'
) AS converted_quantity
FROM DUMMY;
-- Returns: 1.00

-- Convert length units
SELECT CONVERT_UNIT(
  QUANTITY => 100.00,
  SOURCE_UNIT => 'CM',
  SCHEMA => 'SAPABAP1',
  TARGET_UNIT => 'M',
  CLIENT => '100'
) AS meters
FROM DUMMY;
-- Returns: 1.00
```

> **Note:** Requires UOM conversion factors configured in SAP T006* tables.

---

## SQLScript Libraries

Available since HANA 2.0 SPS03:

| Library | Purpose |
|---------|---------|
| `SQLSCRIPT_STRING` | String manipulation (e.g., `TABLE_SUMMARY`) |
| `SQLSCRIPT_PRINT` | Debug output |
| `SQLSCRIPT_SYNC` | Synchronization |
| `SQLSCRIPT_CACHE` | Caching utilities |

**Usage:**
```sql
USING SQLSCRIPT_STRING AS str_lib;
lv_summary = str_lib:TABLE_SUMMARY(:lt_data);
```

```

### references/exception-handling.md

```markdown
# SQLScript Exception Handling Reference

## Table of Contents

- [Overview](#overview)
- [EXIT HANDLER](#exit-handler)
  - [Purpose](#purpose)
  - [Syntax](#syntax)
  - [Condition Values](#condition-values)
  - [Examples](#examples)
- [CONTINUE HANDLER](#continue-handler)
  - [Purpose](#purpose-1)
  - [Syntax](#syntax-1)
  - [When to Use](#when-to-use)
  - [Examples](#examples-1)
- [CONDITION Declaration](#condition-declaration)
  - [Purpose](#purpose-2)
  - [Syntax](#syntax-2)
  - [Common Error Codes](#common-error-codes)
  - [Examples](#examples-2)
- [SIGNAL Statement](#signal-statement)
  - [Purpose](#purpose-3)
  - [Syntax](#syntax-3)
  - [User-defined Error Codes](#user-defined-error-codes)
  - [Examples](#examples-3)
- [RESIGNAL Statement](#resignal-statement)
  - [Purpose](#purpose-4)
  - [Syntax](#syntax-4)
  - [When to Use](#when-to-use-1)
  - [Examples](#examples-4)
- [Error Information Access](#error-information-access)
  - [SQL_ERROR_CODE](#sql_error_code)
  - [SQL_ERROR_MESSAGE](#sql_error_message)
  - [Examples](#examples-5)
- [Handler Precedence](#handler-precedence)
  - [Handler Selection Rules](#handler-selection-rules)
  - [Examples](#examples-6)
- [Nested Handlers](#nested-handlers)
  - [Handler Scoping](#handler-scoping)
  - [Examples](#examples-7)
- [Best Practices](#best-practices)
  - [Error Handling Strategy](#error-handling-strategy)
  - [Performance Considerations](#performance-considerations)
  - [Common Patterns](#common-patterns)

## Overview

SAP HANA SQLScript provides four primary mechanisms for exception handling:
1. **EXIT HANDLER** - Handle exceptions and suspend execution
2. **CONTINUE HANDLER** - Handle exceptions and continue execution
3. **CONDITION** - Define named conditions for error codes
4. **SIGNAL/RESIGNAL** - Throw and re-throw exceptions

---

## EXIT HANDLER

### Purpose
EXIT HANDLER catches exceptions, suspends procedure execution, and performs specified recovery actions.

### Syntax

```sql
DECLARE EXIT HANDLER FOR <condition_value>
  <statement>;

-- Or with block
DECLARE EXIT HANDLER FOR <condition_value>
BEGIN
  <statements>
END;
```

### Condition Values

| Condition | Description |
|-----------|-------------|
| `SQLEXCEPTION` | Catches any SQL exception |
| `SQL_ERROR_CODE <number>` | Catches specific error code |
| `<condition_name>` | Catches user-defined condition |

### Placement Rule

> **Important:** EXIT HANDLER must be declared after all other DECLARE statements but before any procedural code begins.

```sql
BEGIN
  -- 1. Variable declarations
  DECLARE lv_count INTEGER;

  -- 2. Condition declarations
  DECLARE my_error CONDITION FOR SQL_ERROR_CODE 301;

  -- 3. EXIT HANDLER declarations (must be last DECLARE)
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

  -- 4. Procedural code starts here
  INSERT INTO "TABLE" VALUES (1, 'test');
END;
```

### Error Information Access

| Variable | Type | Description |
|----------|------|-------------|
| `::SQL_ERROR_CODE` | INTEGER | Numeric error code of caught exception |
| `::SQL_ERROR_MESSAGE` | NVARCHAR | Error message text |

### Basic 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
    -- Log error to table
    INSERT INTO "ERROR_LOG" (
      error_code,
      error_message,
      created_at
    ) VALUES (
      ::SQL_ERROR_CODE,
      ::SQL_ERROR_MESSAGE,
      CURRENT_TIMESTAMP
    );
    -- Return error info
    SELECT ::SQL_ERROR_CODE AS err_code,
           ::SQL_ERROR_MESSAGE AS err_msg
    FROM DUMMY;
  END;

  -- Main logic that may throw exception
  INSERT INTO "CUSTOMERS" (id, name) VALUES (:iv_id, :iv_name);
END;
```

### Handling Specific Error Codes

```sql
CREATE PROCEDURE handle_duplicate (IN iv_id INTEGER)
LANGUAGE SQLSCRIPT AS
BEGIN
  -- Handle only unique constraint violations
  DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 301
  BEGIN
    SELECT 'Record already exists with ID: ' || :iv_id AS message
    FROM DUMMY;
  END;

  INSERT INTO "MYTABLE" (id) VALUES (:iv_id);
END;
```

### Multiple Handlers

```sql
CREATE PROCEDURE multi_handler_example ()
LANGUAGE SQLSCRIPT AS
BEGIN
  DECLARE duplicate_key CONDITION FOR SQL_ERROR_CODE 301;
  DECLARE no_data CONDITION FOR SQL_ERROR_CODE 1299;

  -- Specific handler for duplicates
  DECLARE EXIT HANDLER FOR duplicate_key
    SELECT 'Duplicate key error' AS error FROM DUMMY;

  -- Specific handler for no data
  DECLARE EXIT HANDLER FOR no_data
    SELECT 'No data found' AS error FROM DUMMY;

  -- Generic handler for all other errors
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SELECT 'Unexpected error: ' || ::SQL_ERROR_MESSAGE AS error FROM DUMMY;

  -- Procedural code
  INSERT INTO "TABLE1" VALUES (1);
  SELECT * FROM "TABLE2" WHERE id = 999;
END;
```

---

## CONDITION

### Purpose
CONDITIONS allow you to assign user-friendly names to SQL error codes for cleaner, more readable code.

> **Note:** CONDITION declaration is optional. You can use `SQL_ERROR_CODE <number>` directly in EXIT HANDLER declarations. However, named conditions improve code readability and maintainability.

### Syntax

```sql
DECLARE <condition_name> CONDITION FOR SQL_ERROR_CODE <number>;
```

### Example

```sql
CREATE PROCEDURE condition_example ()
LANGUAGE SQLSCRIPT AS
BEGIN
  -- Define named conditions
  DECLARE unique_violation CONDITION FOR SQL_ERROR_CODE 301;
  DECLARE invalid_table CONDITION FOR SQL_ERROR_CODE 259;
  DECLARE permission_denied CONDITION FOR SQL_ERROR_CODE 258;

  -- Use in EXIT HANDLER
  DECLARE EXIT HANDLER FOR unique_violation
    SELECT 'Cannot insert duplicate record' AS message FROM DUMMY;

  DECLARE EXIT HANDLER FOR invalid_table
    SELECT 'Table does not exist' AS message FROM DUMMY;

  DECLARE EXIT HANDLER FOR permission_denied
    SELECT 'Access denied to object' AS message FROM DUMMY;

  -- Procedural code
  INSERT INTO "PROTECTED_TABLE" VALUES (1, 'test');
END;
```

---

## SIGNAL

### Purpose
SIGNAL explicitly throws an exception with a user-defined error code (range: 10000-19999).

### Syntax

```sql
-- Using condition name
SIGNAL <condition_name>;
SIGNAL <condition_name> SET MESSAGE_TEXT = '<message>';

-- Using error code directly
SIGNAL SQL_ERROR_CODE <number> SET MESSAGE_TEXT = '<message>';
```

### User-Defined Error Code Range

| Range | Usage |
|-------|-------|
| 10000 - 19999 | User-defined exceptions |

### Example

```sql
CREATE PROCEDURE validate_input (IN iv_amount DECIMAL(15,2))
LANGUAGE SQLSCRIPT AS
BEGIN
  -- Define custom error condition
  DECLARE invalid_amount CONDITION FOR SQL_ERROR_CODE 10001;

  DECLARE EXIT HANDLER FOR invalid_amount
    SELECT 'Validation failed: ' || ::SQL_ERROR_MESSAGE AS error FROM DUMMY;

  -- Validate input
  IF :iv_amount < 0 THEN
    SIGNAL invalid_amount SET MESSAGE_TEXT = 'Amount cannot be negative';
  END IF;

  IF :iv_amount > 1000000 THEN
    SIGNAL invalid_amount SET MESSAGE_TEXT = 'Amount exceeds maximum limit';
  END IF;

  -- Continue with valid input
  INSERT INTO "TRANSACTIONS" (amount) VALUES (:iv_amount);
END;
```

### SIGNAL Without Condition

```sql
-- Throw exception with error code directly
IF :lv_count = 0 THEN
  SIGNAL SQL_ERROR_CODE 10002 SET MESSAGE_TEXT = 'No records found for processing';
END IF;
```

---

## RESIGNAL

### Purpose
RESIGNAL re-throws an exception from within an EXIT HANDLER, allowing the exception to propagate to the caller.

### Syntax

```sql
-- Re-throw current exception
RESIGNAL;

-- Re-throw with different condition
RESIGNAL <condition_name>;

-- Re-throw with modified message
RESIGNAL SET MESSAGE_TEXT = '<new_message>';
```

### Restriction

> **Important:** RESIGNAL can only be used within an EXIT HANDLER block.

### Example: Logging and Re-throwing

```sql
CREATE PROCEDURE process_with_logging ()
LANGUAGE SQLSCRIPT AS
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Log the error first
    INSERT INTO "ERROR_LOG" (
      procedure_name,
      error_code,
      error_message,
      logged_at
    ) VALUES (
      'process_with_logging',
      ::SQL_ERROR_CODE,
      ::SQL_ERROR_MESSAGE,
      CURRENT_TIMESTAMP
    );

    -- Re-throw to caller
    RESIGNAL;
  END;

  -- Risky operation
  DELETE FROM "IMPORTANT_TABLE" WHERE status = 'OLD';
END;
```

### Example: Wrapping Exceptions

```sql
CREATE PROCEDURE wrap_exception ()
LANGUAGE SQLSCRIPT AS
BEGIN
  DECLARE business_error CONDITION FOR SQL_ERROR_CODE 10100;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Wrap technical error as business error
    RESIGNAL business_error
      SET MESSAGE_TEXT = 'Business operation failed: ' || ::SQL_ERROR_MESSAGE;
  END;

  -- Technical operation
  UPDATE "ACCOUNTS" SET balance = balance - 100 WHERE id = 1;
END;
```

---

## Common SQL Error Codes

| Code | Description |
|------|-------------|
| 258 | Insufficient privilege |
| 259 | Invalid table name |
| 260 | Invalid column name |
| 261 | Invalid index name |
| 301 | Unique constraint violation |
| 339 | Foreign key constraint violation |
| 362 | NOT NULL constraint violation |
| 1299 | No data found |
| 1304 | Resource busy |
| 10000-19999 | User-defined errors |

---

## Complete Error Handling Pattern

```sql
CREATE PROCEDURE complete_error_handling (
  IN iv_customer_id INTEGER,
  IN iv_amount DECIMAL(15,2),
  OUT ov_status NVARCHAR(20),
  OUT ov_message NVARCHAR(500)
)
LANGUAGE SQLSCRIPT AS
BEGIN
  -- Define conditions
  DECLARE duplicate_key CONDITION FOR SQL_ERROR_CODE 301;
  DECLARE fk_violation CONDITION FOR SQL_ERROR_CODE 339;
  DECLARE invalid_input CONDITION FOR SQL_ERROR_CODE 10001;

  -- Handle duplicate key
  DECLARE EXIT HANDLER FOR duplicate_key
  BEGIN
    ov_status = 'ERROR';
    ov_message = 'Transaction already exists for this customer';
  END;

  -- Handle foreign key violation
  DECLARE EXIT HANDLER FOR fk_violation
  BEGIN
    ov_status = 'ERROR';
    ov_message = 'Customer ID does not exist';
  END;

  -- Handle validation errors
  DECLARE EXIT HANDLER FOR invalid_input
  BEGIN
    ov_status = 'ERROR';
    ov_message = ::SQL_ERROR_MESSAGE;
  END;

  -- Handle all other errors
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Log unexpected errors
    INSERT INTO "ERROR_LOG" VALUES (
      ::SQL_ERROR_CODE,
      ::SQL_ERROR_MESSAGE,
      CURRENT_TIMESTAMP
    );
    ov_status = 'ERROR';
    ov_message = 'Unexpected error occurred. Please contact support.';
  END;

  -- Input validation
  IF :iv_amount <= 0 THEN
    SIGNAL invalid_input SET MESSAGE_TEXT = 'Amount must be positive';
  END IF;

  IF :iv_customer_id IS NULL THEN
    SIGNAL invalid_input SET MESSAGE_TEXT = 'Customer ID is required';
  END IF;

  -- Main business logic
  INSERT INTO "TRANSACTIONS" (customer_id, amount, created_at)
  VALUES (:iv_customer_id, :iv_amount, CURRENT_TIMESTAMP);

  -- Success
  ov_status = 'SUCCESS';
  ov_message = 'Transaction recorded successfully';
END;
```

---

## Best Practices

### 1. Always Handle SQLEXCEPTION
```sql
-- Catch-all handler prevents unhandled exceptions
DECLARE EXIT HANDLER FOR SQLEXCEPTION
  -- Log and/or notify
```

### 2. Use Named Conditions
```sql
-- Good: Clear intent
DECLARE duplicate_key CONDITION FOR SQL_ERROR_CODE 301;
DECLARE EXIT HANDLER FOR duplicate_key ...

-- Avoid: Magic numbers
DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 301 ...
```

### 3. Log Before RESIGNAL
```sql
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  -- Log first
  INSERT INTO error_log VALUES (...);
  -- Then re-throw
  RESIGNAL;
END;
```

### 4. Use User-Defined Codes for Business Logic
```sql
-- Reserve 10000-19999 for application-specific errors
DECLARE validation_error CONDITION FOR SQL_ERROR_CODE 10001;
DECLARE business_rule_error CONDITION FOR SQL_ERROR_CODE 10002;
```

### 5. Provide Meaningful Messages
```sql
SIGNAL validation_error
  SET MESSAGE_TEXT = 'Order quantity (' || :lv_qty || ') exceeds available stock (' || :lv_stock || ')';
```

```

### references/amdp-integration.md

```markdown
# AMDP (ABAP Managed Database Procedures) Integration Guide

## Table of Contents

- [Overview](#overview)
- [Key Benefits](#key-benefits)
- [Prerequisites](#prerequisites)
- [AMDP Class Structure](#amdp-class-structure)
  - [Interface Definition](#interface-definition)
  - [Method Definition](#method-definition)
  - [Method Implementation](#method-implementation)
- [Data Type Mapping](#data-type-mapping)
  - [ABAP to SQLScript Types](#abap-to-sqlscript-types)
  - [Table Types](#table-types)
  - [Complex Types](#complex-types)
- [AMDP Method Types](#amdp-method-types)
  - [Procedures](#procedures)
  - [Functions](#functions)
  - [CDS Views](#cds-views)
- [Implementation Examples](#implementation-examples)
  - [Simple Procedure](#simple-procedure)
  - [Table Function](#table-function)
  - [Using CDS Entities](#using-cds-entities)
- [Advanced Features](#advanced-features)
  - [Cursor Operations](#cursor-operations)
  - [Dynamic SQL](#dynamic-sql)
  - [Exception Handling](#exception-handling)
  - [Performance Hints](#performance-hints)
- [Best Practices](#best-practices)
- [Common Pitfalls](#common-pitfalls)
- [Debugging AMDP](#debugging-amdp)
- [Transport and Deployment](#transport-and-deployment)
- [Performance Considerations](#performance-considerations)
- [AMDP vs CDS View vs HANA View](#amdp-vs-cds-view-vs-hana-view)

## Overview

ABAP Managed Database Procedures (AMDP) allow developers to write SQLScript code directly within ABAP classes. Introduced in ABAP 7.40 SP05, AMDP enables the **code-to-data paradigm** from the ABAP layer, pushing data-intensive operations to the SAP HANA database.

---

## Key Benefits

| Benefit | Description |
|---------|-------------|
| **Performance** | Execute logic in database rather than application server |
| **Simplified Lifecycle** | Procedures auto-created on first invocation |
| **ABAP Integration** | Use ABAP types and data structures |
| **Version Control** | Stored with ABAP code in transport system |
| **No HANA Access Required** | Develop without direct database access |

---

## Prerequisites

- SAP NetWeaver 7.40 SP05 or higher
- SAP HANA database
- ABAP Development Tools (Eclipse ADT)

> **Important:** AMDP classes cannot be edited in SAP GUI. Eclipse ADT is required.

---

## Basic Structure

### 1. Interface Declaration

Every AMDP class must implement the `IF_AMDP_MARKER_HDB` interface:

```abap
CLASS zcl_my_amdp DEFINITION PUBLIC FINAL CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.  " Required marker interface

    " Method declarations...
ENDCLASS.
```

### 2. Method Declaration

AMDP methods are declared like regular ABAP methods:

```abap
CLASS zcl_my_amdp DEFINITION PUBLIC FINAL CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    " Instance method
    METHODS get_sales_data
      IMPORTING VALUE(iv_year) TYPE gjahr
      EXPORTING VALUE(et_result) TYPE tt_sales.

    " Static method
    CLASS-METHODS get_customer_count
      IMPORTING VALUE(iv_country) TYPE land1
      RETURNING VALUE(rv_count) TYPE i.
ENDCLASS.
```

### 3. Method Implementation

Use the `BY DATABASE PROCEDURE` or `BY DATABASE FUNCTION` syntax:

```abap
CLASS zcl_my_amdp IMPLEMENTATION.
  METHOD get_sales_data BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING vbak vbap.

    " SQLScript code here
    et_result = SELECT vbeln, posnr, matnr, kwmeng
                FROM vbak
                JOIN vbap ON vbak.vbeln = vbap.vbeln
                WHERE gjahr = :iv_year;
  ENDMETHOD.
ENDCLASS.
```

---

## Syntax Elements

### BY DATABASE PROCEDURE

```abap
METHOD <method_name> BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  [OPTIONS <options>]
  [USING <db_entities>].
```

| Element | Description |
|---------|-------------|
| `FOR HDB` | Target database (currently only HDB supported) |
| `LANGUAGE SQLSCRIPT` | Programming language |
| `OPTIONS` | Execution options |
| `USING` | Database entities accessed |

### BY DATABASE FUNCTION

For table functions that can be used in SELECT statements:

```abap
METHOD <method_name> BY DATABASE FUNCTION
  FOR HDB
  LANGUAGE SQLSCRIPT
  OPTIONS READ-ONLY
  USING <db_entities>.
```

### OPTIONS

| Option | Description |
|--------|-------------|
| `READ-ONLY` | No data modifications allowed |
| `DETERMINISTIC` | Same input always produces same output |
| `SUPPRESS WARNINGS` | Suppress specific warnings |

### USING Clause

List all database tables and views accessed:

```abap
USING mara makt vbak vbap.  " Multiple tables
USING ztable.               " Custom table
USING zcl_other_amdp=>method_name.  " Other AMDP method
```

---

## Parameter Restrictions

### Allowed Parameter Types

| Type | Allowed |
|------|---------|
| Elementary types | Yes |
| Structures | Yes |
| Internal tables | Yes |
| Nested tables | No |
| Deep structures | No |

### Parameter Passing

| Mode | Requirement |
|------|-------------|
| `IMPORTING` | Must use `VALUE()` |
| `EXPORTING` | Must use `VALUE()` |
| `CHANGING` | Not supported |
| `RETURNING` | Not supported for procedures, allowed for functions |

### Example with Types

```abap
CLASS zcl_amdp_types DEFINITION PUBLIC FINAL CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    " Define types in class
    TYPES: BEGIN OF ty_customer,
             kunnr TYPE kunnr,
             name1 TYPE name1,
             land1 TYPE land1,
           END OF ty_customer.

    TYPES tt_customer TYPE STANDARD TABLE OF ty_customer WITH DEFAULT KEY.

    " Method using defined types
    METHODS get_customers
      IMPORTING VALUE(iv_country) TYPE land1
      EXPORTING VALUE(et_customers) TYPE tt_customer.
ENDCLASS.
```

---

## Complete Examples

### Example 1: Basic Data Retrieval

```abap
CLASS zcl_material_amdp DEFINITION PUBLIC FINAL CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_material,
             matnr TYPE matnr,
             mtart TYPE mtart,
             matkl TYPE matkl,
             meins TYPE meins,
           END OF ty_material.

    TYPES tt_material TYPE STANDARD TABLE OF ty_material WITH DEFAULT KEY.

    CLASS-METHODS get_materials_by_type
      IMPORTING VALUE(iv_mtart) TYPE mtart
      EXPORTING VALUE(et_materials) TYPE tt_material.
ENDCLASS.

CLASS zcl_material_amdp IMPLEMENTATION.
  METHOD get_materials_by_type BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING mara.

    et_materials = SELECT matnr, mtart, matkl, meins
                   FROM mara
                   WHERE mtart = :iv_mtart;
  ENDMETHOD.
ENDCLASS.
```

### Example 2: Aggregation and Joins

```abap
CLASS zcl_sales_amdp DEFINITION PUBLIC FINAL CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_sales_summary,
             vkorg TYPE vkorg,
             matnr TYPE matnr,
             total_qty TYPE kwmeng,
             total_value TYPE netwr,
           END OF ty_sales_summary.

    TYPES tt_sales_summary TYPE STANDARD TABLE OF ty_sales_summary WITH DEFAULT KEY.

    CLASS-METHODS get_sales_summary
      IMPORTING VALUE(iv_year) TYPE gjahr
      EXPORTING VALUE(et_summary) TYPE tt_sales_summary.
ENDCLASS.

CLASS zcl_sales_amdp IMPLEMENTATION.
  METHOD get_sales_summary BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING vbak vbap.

    et_summary = SELECT vbak.vkorg,
                        vbap.matnr,
                        SUM(vbap.kwmeng) AS total_qty,
                        SUM(vbap.netwr) AS total_value
                 FROM vbak
                 INNER JOIN vbap ON vbak.vbeln = vbap.vbeln
                 WHERE SUBSTRING(vbak.erdat, 1, 4) = :iv_year
                 GROUP BY vbak.vkorg, vbap.matnr;
  ENDMETHOD.
ENDCLASS.
```

### Example 3: With Exception Handling

```abap
CLASS zcl_order_amdp DEFINITION PUBLIC FINAL CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_order,
             vbeln TYPE vbeln,
             erdat TYPE erdat,
             netwr TYPE netwr,
           END OF ty_order.

    TYPES tt_orders TYPE STANDARD TABLE OF ty_order WITH DEFAULT KEY.

    CLASS-METHODS get_orders_safe
      IMPORTING VALUE(iv_kunnr) TYPE kunnr
      EXPORTING VALUE(et_orders) TYPE tt_orders
                VALUE(ev_error) TYPE string.
ENDCLASS.

CLASS zcl_order_amdp IMPLEMENTATION.
  METHOD get_orders_safe BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING vbak.

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      ev_error = 'Error: ' || ::SQL_ERROR_CODE || ' - ' || ::SQL_ERROR_MESSAGE;
      et_orders = SELECT NULL AS vbeln, NULL AS erdat, NULL AS netwr
                  FROM dummy WHERE 1 = 0;
    END;

    et_orders = SELECT vbeln, erdat, netwr
                FROM vbak
                WHERE kunnr = :iv_kunnr;

    ev_error = '';
  ENDMETHOD.
ENDCLASS.
```

### Example 4: Table Function for CDS Views

```abap
CLASS zcl_stock_amdp DEFINITION PUBLIC FINAL CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    TYPES: BEGIN OF ty_stock,
             matnr TYPE matnr,
             werks TYPE werks_d,
             labst TYPE labst,
           END OF ty_stock.

    TYPES tt_stock TYPE STANDARD TABLE OF ty_stock WITH DEFAULT KEY.

    " Table function - can be used in CDS views
    CLASS-METHODS get_stock
      IMPORTING VALUE(iv_werks) TYPE werks_d
      RETURNING VALUE(rt_stock) TYPE tt_stock.
ENDCLASS.

CLASS zcl_stock_amdp IMPLEMENTATION.
  METHOD get_stock BY DATABASE FUNCTION
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING mard.

    RETURN SELECT matnr, werks, labst
           FROM mard
           WHERE werks = :iv_werks
             AND labst > 0;
  ENDMETHOD.
ENDCLASS.
```

---

## Calling AMDP Methods

### From ABAP

```abap
DATA: lt_materials TYPE zcl_material_amdp=>tt_material.

" Call static method
zcl_material_amdp=>get_materials_by_type(
  EXPORTING
    iv_mtart = 'FERT'
  IMPORTING
    et_materials = lt_materials
).

" Call instance method
DATA(lo_amdp) = NEW zcl_my_amdp( ).
lo_amdp->get_data(
  EXPORTING
    iv_param = 'value'
  IMPORTING
    et_result = lt_result
).
```

### From CDS Views (Table Functions Only)

```sql
@AbapCatalog.sqlViewName: 'ZSTOCK_CDS'
define view ZI_STOCK_VIEW as
  select from zcl_stock_amdp=>get_stock( werks: $parameters.p_werks ) as Stock
{
  matnr,
  werks,
  labst
}
```

---

## Restrictions and Limitations

### Not Allowed in AMDP

| Feature | Restriction |
|---------|-------------|
| `COMMIT` | Not permitted |
| `ROLLBACK` | Not permitted |
| DDL statements | Not permitted |
| Dynamic SQL with table variables | Limited support |
| Nested tables | Not as parameters |
| Deep structures | Not as parameters |
| `RETURNING` parameters | Only in DB functions |
| `CHANGING` parameters | Not supported |

### Table Buffering

Writing to tables with active SAP buffering may cause issues. Use unbuffered tables or disable buffering.

---

## Debugging AMDP

### In Eclipse ADT

1. Set breakpoint in SQLScript code
2. Enable AMDP debugging in preferences
3. Run ABAP program calling the AMDP
4. Debugger switches to SQLScript debug mode

### Debug Output

Use `TRACE` in SQLScript (HANA 2.0+):

```sql
DECLARE lv_debug NVARCHAR(1000);
lv_debug = 'Processing customer: ' || :iv_kunnr;
TRACE :lv_debug;
```

**Viewing TRACE Output:**
- **Eclipse ADT:** Open Debug perspective → Console view shows TRACE output during debugging session
- **SAP HANA Web IDE:** Debug Console panel displays trace messages
- **SQL Console:** TRACE output appears in the Messages tab after execution
- **Programmatic Access:** Query `M_SQLSCRIPT_TRACE` system view for trace history (requires appropriate privileges)

---

## Best Practices

### 1. Use Appropriate Container

| Use Case | Container |
|----------|-----------|
| Data modifications | `BY DATABASE PROCEDURE` |
| Read-only queries in CDS | `BY DATABASE FUNCTION` |

### 2. Minimize Data Transfer

```abap
" Good: Filter in database
et_result = SELECT * FROM table WHERE condition;

" Avoid: Return all, filter in ABAP
```

### 3. Use Set-Based Operations

```sql
" Good: Set-based
et_result = SELECT a.*, b.name
            FROM table_a a
            JOIN table_b b ON a.id = b.id;

" Avoid: Cursor-based
FOR row AS cursor DO ...
```

### 4. Proper Error Handling

Always include EXIT HANDLER for production code:

```sql
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ev_error = ::SQL_ERROR_MESSAGE;
END;
```

### 5. Document USING Clause

```abap
" List ALL tables accessed, even in nested calls
USING vbak vbap mara makt.
```

---

## Standard SAP Examples

Explore these standard SAP classes for AMDP patterns:

| Class | Description |
|-------|-------------|
| `CL_CS_BOM_AMDP` | Bill of Materials processing |
| `CL_SALV_AMDP_UTILS` | ALV utilities |
| `CL_ABAP_AMDP_TEST` | Test examples |

Check methods like `MAT_REVISION_LEVEL_SELECT`, `MAT_BOM_CALC_QUANTITY` for real-world implementations.

```

### references/performance-guide.md

```markdown
# SQLScript Performance Optimization Guide

## Table of Contents

- [Core Principle: Code-to-Data Paradigm](#core-principle-code-to-data-paradigm)
- [Top Performance Optimization Tips](#top-performance-optimization-tips)
  - [1. Reduce Data Volume Early](#1-reduce-data-volume-early)
  - [2. Prefer Declarative Over Imperative](#2-prefer-declarative-over-imperative)
  - [3. Avoid Engine Mixing](#3-avoid-engine-mixing)
  - [4. Use UNION ALL Instead of UNION](#4-use-union-all-instead-of-union)
  - [5. Minimize Dynamic SQL](#5-minimize-dynamic-sql)
  - [6. Position Imperative Logic Last](#6-position-imperative-logic-last)
- [Execution Plan Analysis](#execution-plan-analysis)
  - [Understanding the Plan](#understanding-the-plan)
  - [Common Plan Issues](#common-plan-issues)
  - [Optimization Techniques](#optimization-techniques)
- [Advanced Optimization Techniques](#advanced-optimization-techniques)
  - [Query Hints](#query-hints)
  - [Partitioning Strategies](#partitioning-strategies)
  - [Materialized Views](#materialized-views)
  - [Join Optimization](#join-optimization)
- [Memory Management](#memory-management)
  - [Memory Allocation](#memory-allocation)
  - [Memory Leaks Prevention](#memory-leaks-prevention)
  - [Large Dataset Handling](#large-dataset-handling)
- [Parallel Execution](#parallel-execution)
  - [Parallelism in Declarative Logic](#parallelism-in-declarative-logic)
  - [Limitations of Imperative Code](#limitations-of-imperative-code)
- [Performance Monitoring](#performance-monitoring)
  - [Expensive Statements Trace](#expensive-statements-trace)
  - [Performance Tools](#performance-tools)
  - [Key Metrics](#key-metrics)
- [Common Performance Anti-Patterns](#common-performance-anti-patterns)
  - [Row-by-Row Processing](#row-by-row-processing)
  - [Cursor Overuse](#cursor-overuse)
  - [Unnecessary Joins](#unnecessary-joins)
  - [Suboptimal Data Types](#suboptimal-data-types)
- [Benchmarking Best Practices](#benchmarking-best-practices)

## Core Principle: Code-to-Data Paradigm

SAP HANA's fundamental performance philosophy is **push computation to the database**, not pull data to the application. This leverages:

- In-memory processing
- Columnar storage compression
- Parallel query execution
- Database optimizer capabilities

---

## Top Performance Optimization Tips

### 1. Reduce Data Volume Early

**Problem:** Processing large datasets consumes memory and slows execution.

**Solution:** Filter rows and select only required columns as early as possible.

```sql
-- GOOD: Filter and project early
lt_filtered = SELECT customer_id, order_total
              FROM "ORDERS"
              WHERE status = 'COMPLETED'
                AND order_date >= ADD_DAYS(CURRENT_DATE, -30);

lt_result = SELECT f.customer_id, f.order_total, c.name
            FROM :lt_filtered AS f
            JOIN "CUSTOMERS" AS c ON f.customer_id = c.id;

-- BAD: Join first, filter later
lt_result = SELECT o.customer_id, o.order_total, c.name
            FROM "ORDERS" AS o
            JOIN "CUSTOMERS" AS c ON o.customer_id = c.id
            WHERE o.status = 'COMPLETED'
              AND o.order_date >= ADD_DAYS(CURRENT_DATE, -30);
```

**Best Practices:**
- Select only columns you need (avoid `SELECT *`)
- Apply WHERE filters as early as possible
- Pre-aggregate before joining
- Consider NULL-heavy columns separately

---

### 2. Prefer Declarative Over Imperative Logic

**Problem:** Imperative constructs (loops, cursors, IF statements) prevent parallel execution.

**Solution:** Use set-based SQL operations whenever possible.

```sql
-- GOOD: Declarative set-based operation
lt_updated = SELECT id,
                    amount * 1.1 AS new_amount,
                    CASE WHEN amount > 1000 THEN 'HIGH'
                         WHEN amount > 100 THEN 'MEDIUM'
                         ELSE 'LOW'
                    END AS tier
             FROM "TRANSACTIONS";

-- BAD: Imperative row-by-row processing
FOR row AS cursor_transactions DO
  IF row.amount > 1000 THEN
    UPDATE "TRANSACTIONS" SET tier = 'HIGH' WHERE id = row.id;
  ELSEIF row.amount > 100 THEN
    UPDATE "TRANSACTIONS" SET tier = 'MEDIUM' WHERE id = row.id;
  ELSE
    UPDATE "TRANSACTIONS" SET tier = 'LOW' WHERE id = row.id;
  END IF;
END FOR;
```

**Impact:**
- Declarative: HANA optimizer creates parallel execution plan
- Imperative: Forces sequential processing, bypasses optimizer

---

### 3. Avoid Engine Mixing

**Problem:** HANA uses specialized engines for different operations. Mixing them causes data conversion overhead.

| Engine | Purpose | Triggered By |
|--------|---------|--------------|
| Column Engine | Columnar storage operations | Queries on column tables, most SQLScript |
| Row Engine | Row-store table operations | Queries on row tables, transactional operations |
| Calculation Engine | Complex expressions, CE functions | CE_* functions, certain calculation views |

**Engine Selection:** HANA automatically selects the execution engine based on:
- Table storage type (column vs row store)
- Query patterns and operations used
- Optimizer cost estimation

**Solution:** Keep operations within same engine type.

```sql
-- BAD: Mixing Row Store and Column Store
SELECT c.* FROM "COLUMN_STORE_TABLE" c
JOIN "ROW_STORE_TABLE" r ON c.id = r.id;

-- BETTER: Convert to same store type or redesign
-- Or at minimum, be aware of the conversion cost
```

**Avoid mixing:**
- Row Store tables with Column Store tables
- SQL operations with Calculation Engine (CE) functions
- SQLScript CE functions in performance-critical paths

---

### 4. Optimize Set Operations

**Problem:** UNION, INTERSECT, EXCEPT don't utilize Column Engine efficiently.

**Solution:** Replace with JOIN operations where possible, or use UNION ALL.

```sql
-- GOOD: UNION ALL (no duplicate removal)
SELECT id, name, 'A' AS source FROM table_a
UNION ALL
SELECT id, name, 'B' AS source FROM table_b;

-- SLOWER: UNION (removes duplicates)
SELECT id, name FROM table_a
UNION
SELECT id, name FROM table_b;

-- ALTERNATIVE: Use JOIN instead of INTERSECT
-- Instead of: SELECT id FROM a INTERSECT SELECT id FROM b
SELECT DISTINCT a.id
FROM table_a AS a
INNER JOIN table_b AS b ON a.id = b.id;
```

---

### 5. Eliminate Dynamic SQL

**Problem:** Dynamic SQL requires re-optimization on every execution.

**Solution:** Use static SQL with parameters.

```sql
-- BAD: Dynamic SQL
lv_sql = 'SELECT * FROM "' || :lv_table || '" WHERE status = ''' || :lv_status || '''';
EXECUTE IMMEDIATE :lv_sql;

-- GOOD: Static SQL with parameters
SELECT * FROM "ORDERS" WHERE status = :lv_status;
```

**Additional issues with dynamic SQL:**
- SQL injection vulnerabilities
- No query plan caching
- Harder to debug and maintain

---

### 6. Position Imperative Logic Last

**Problem:** Imperative statements block parallel processing of subsequent code.

**Solution:** Structure procedures with declarative logic first, imperative at the end.

```sql
CREATE PROCEDURE optimized_processing ()
LANGUAGE SQLSCRIPT AS
BEGIN
  -- 1. Declarative operations FIRST (run in parallel)
  lt_data = SELECT * FROM "SOURCE_TABLE" WHERE active = 1;
  lt_aggregated = SELECT category, SUM(amount) AS total
                  FROM :lt_data GROUP BY category;
  lt_joined = SELECT a.*, b.name
              FROM :lt_aggregated AS a
              JOIN "CATEGORIES" AS b ON a.category = b.id;

  -- 2. Final imperative operations LAST
  FOR row AS (SELECT * FROM :lt_joined) DO
    IF row.total > 10000 THEN
      INSERT INTO "ALERTS" VALUES (row.category, row.total, CURRENT_TIMESTAMP);
    END IF;
  END FOR;
END;
```

---

## Query Optimization Techniques

### Use Appropriate Indexes

```sql
-- Create index for frequently filtered columns
CREATE INDEX idx_orders_status ON "ORDERS" (status);
CREATE INDEX idx_orders_date ON "ORDERS" (order_date);

-- Composite index for multi-column filters
CREATE INDEX idx_orders_status_date ON "ORDERS" (status, order_date);
```

### Leverage Partitioning

```sql
-- Partition large tables by date
CREATE COLUMN TABLE "TRANSACTIONS" (
  id INTEGER,
  trans_date DATE,
  amount DECIMAL(15,2)
) PARTITION BY RANGE (trans_date) (
  PARTITION '2023' <= VALUES < '2024',
  PARTITION '2024' <= VALUES < '2025',
  PARTITION OTHERS
);
```

### Use Query Hints

```sql
-- Hint to use specific execution strategy
SELECT /*+ USE_OLAP_PLAN */ * FROM "LARGE_TABLE";

-- Hint to disable parallelism (when needed)
SELECT /*+ NO_PARALLEL */ * FROM "SMALL_TABLE";
```

---

## Table Variable Best Practices

### Avoid Scalar Variables in Parallel Sections

```sql
-- BAD: Scalar variables break parallelism
DECLARE lv_count INTEGER;
SELECT COUNT(*) INTO lv_count FROM "TABLE1";
-- Subsequent operations must wait

-- BETTER: Use table variables
lt_stats = SELECT COUNT(*) AS cnt FROM "TABLE1";
-- Can continue parallel processing
```

### Use Table Variable Operators

```sql
-- Efficient in-memory operations
:lt_data.INSERT((:lv_id, :lv_name));
:lt_data.UPDATE((:lv_new_name), 1);
:lt_data.DELETE(1);
```

---

## Monitoring and Analysis Tools

### Plan Visualizer

Analyze execution plans to identify bottlenecks:

1. Open SQL Console in HANA Studio/Web IDE
2. Execute query with "Visualize Plan"
3. Look for:
   - Full table scans (add indexes)
   - Engine conversions (avoid mixing)
   - Large intermediate results (filter earlier)

### Expensive Statement Trace

Enable to capture slow queries:

```sql
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM')
SET ('expensive_statement', 'enable') = 'true';

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM')
SET ('expensive_statement', 'threshold_duration') = '1000000';  -- 1 second in microseconds
```

### SQL Analyzer

Get optimization recommendations:

```sql
-- In SAP HANA Studio
EXPLAIN PLAN FOR <your_query>;
SELECT * FROM "EXPLAIN_CALL_PLANS";
```

---

## Memory Management

### Control Result Set Size

```sql
-- Limit results for testing/debugging
SELECT TOP 1000 * FROM "LARGE_TABLE";

-- Use LIMIT for pagination
SELECT * FROM "TABLE" ORDER BY id LIMIT 100 OFFSET 200;
```

### Release Resources

```sql
-- Explicitly close cursors
CLOSE my_cursor;

-- Use smaller table variables when possible
lt_small = SELECT id, name FROM :lt_large;  -- Only needed columns
```

---

## Anti-Patterns to Avoid

| Anti-Pattern | Problem | Solution |
|-------------|---------|----------|
| `SELECT *` | Retrieves unnecessary columns | List specific columns |
| Cursor loops for updates | Row-by-row processing | Set-based UPDATE |
| DISTINCT on large sets | Memory intensive | Filter earlier or redesign |
| Correlated subqueries | N+1 query pattern | Use JOIN |
| Multiple sequential queries | No parallelism | Combine into single query |
| String concatenation in loops | Memory fragmentation | Use STRING_AGG |

---

## Performance Checklist

Before deploying SQLScript:

- [ ] Columns explicitly listed (no `SELECT *`)
- [ ] WHERE filters applied early
- [ ] Set-based operations preferred over loops
- [ ] No dynamic SQL unless absolutely necessary
- [ ] Declarative statements before imperative
- [ ] Appropriate indexes exist
- [ ] Execution plan reviewed
- [ ] No engine mixing issues
- [ ] Table variables used efficiently
- [ ] Resource cleanup in place

---

## Benchmarking Template

```sql
DO
BEGIN
  DECLARE lv_start TIMESTAMP;
  DECLARE lv_end TIMESTAMP;
  DECLARE lv_duration BIGINT;

  lv_start = CURRENT_TIMESTAMP;

  -- Your code here
  lt_result = SELECT * FROM "LARGE_TABLE" WHERE condition;

  lv_end = CURRENT_TIMESTAMP;
  lv_duration = NANO100_BETWEEN(:lv_start, :lv_end) / 10000;  -- milliseconds

  SELECT :lv_duration AS execution_time_ms FROM DUMMY;
END;
```

```

### references/advanced-features.md

```markdown
# SQLScript Advanced Features Reference

## Table of Contents

- [Loop Variations](#loop-variations)
  - [DO n TIMES Loop](#do-n-times-loop)
  - [DO n TIMES with Counter](#do-n-times-with-counter)
- [Lateral Joins](#lateral-joins)
  - [Syntax](#syntax)
  - [Example](#example)
- [Query Hints](#query-hints)
  - [Common Hints](#common-hints)
  - [Hint Syntax](#hint-syntax)
- [JSON Functions](#json-functions)
  - [JSON_VALUE](#json_value)
  - [JSON_QUERY](#json_query)
  - [JSON_TABLE](#json_table)
- [Spatial Functions](#spatial-functions)
  - [Spatial Data Types](#spatial-data-types)
  - [Common Functions](#common-functions)
- [Time-Series Functions](#time-series-functions)
  - [Moving Averages](#moving-averages)
  - [Series Generation](#series-generation)
- [SAP-Specific Conversion Functions](#sap-specific-conversion-functions)
  - [TO_DATS](#to_dats)
  - [TO_TIMS](#to_tims)
  - [Usage in Date Logic](#usage-in-date-logic)
- [CONVERT_CURRENCY Function](#convert_currency-function)
  - [Syntax](#syntax-1)
  - [Example](#example-1)
  - [Complete Example with Dynamic Date](#complete-example-with-dynamic-date)
- [Session and System Functions](#session-and-system-functions)
  - [session_context()](#session_context)
  - [record_count()](#record_count)
  - [current_line_number](#current_line_number)
- [Parallel Mode Exit Triggers](#parallel-mode-exit-triggers)
  - [Best Practice](#best-practice)
- [SET Operations Alternatives](#set-operations-alternatives)
  - [INTERSECT Alternative](#intersect-alternative)
  - [EXCEPT Alternative](#except-alternative)
- [Procedure Management](#procedure-management)
  - [DROP PROCEDURE](#drop-procedure)
  - [ALTER PROCEDURE Limitation](#alter-procedure-limitation)
  - [DROP FUNCTION](#drop-function)
- [Security Considerations](#security-considerations)
  - [SQL Injection Prevention](#sql-injection-prevention)
- [AMDP Advantages Over Procedure Proxy](#amdp-advantages-over-procedure-proxy)
- [APPLY_FILTER Function](#apply_filter-function)
  - [Syntax](#syntax-2)
  - [Parameters](#parameters)
  - [Example](#example-2)
  - [Use Cases](#use-cases)
- [Array Functions](#array-functions)
  - [ARRAY_AGG](#array_agg)
  - [TRIM_ARRAY](#trim_array)
  - [Array Concatenation](#array-concatenation)
  - [CARDINALITY](#cardinality)
- [CONTINUE HANDLER](#continue-handler)
  - [Syntax](#syntax-3)
  - [Example](#example-3)
  - [EXIT vs CONTINUE Handler](#exit-vs-continue-handler)
- [CE Functions (Calculation Engine)](#ce-functions-calculation-engine)
  - [Common CE Functions](#common-ce-functions)
  - [CE_PROJECTION Example](#ce_projection-example)
- [SQLScript Analysis Tools](#sqlscript-analysis-tools)
  - [SQLScript Code Analyzer](#sqlscript-code-analyzer)
  - [SQLScript Plan Profiler](#sqlscript-plan-profiler)
  - [SQLScript Code Coverage](#sqlscript-code-coverage)
- [SQLScript Pragmas](#sqlscript-pragmas)
  - [Syntax](#syntax-4)
  - [Common Pragmas](#common-pragmas)
  - [Example](#example-4)

## Loop Variations

### DO n TIMES Loop

Repeat a block a fixed number of times:

```sql
DO 10 TIMES
BEGIN
  INSERT INTO "LOG_TABLE" (message) VALUES ('Iteration');
END;
```

### DO n TIMES with Counter

```sql
DO
BEGIN
  DECLARE i INTEGER := 0;
  WHILE :i < 10 DO
    INSERT INTO "LOG_TABLE" (counter) VALUES (:i);
    i := :i + 1;
  END WHILE;
END;
```

---

## Lateral Joins

Lateral joins enable subqueries in the FROM clause to reference columns from preceding table expressions.

### Syntax

```sql
SELECT <columns>
FROM <table1>,
     LATERAL (<subquery referencing table1>) AS <alias>
WHERE <condition>;
```

### Example

```sql
SELECT TA.a1, TB.b1
FROM TA,
     LATERAL (SELECT b1, b2 FROM TB WHERE b3 = TA.a3) TB
WHERE TA.a2 = TB.b2;
```

**Use Cases:**
- Correlated subqueries in FROM clause
- Row-by-row transformations
- Dependent data lookups

---

## Query Hints

Provide optimization guidance to the SQL parser.

### Common Hints

```sql
-- Force parallel execution
SELECT /*+ PARALLEL_EXECUTION */ * FROM "LARGE_TABLE";

-- Use OLAP execution plan
SELECT /*+ USE_OLAP_PLAN */ * FROM "ANALYTICS_TABLE";

-- Disable parallel execution
SELECT /*+ NO_PARALLEL */ * FROM "SMALL_TABLE";

-- Route to specific engine
SELECT /*+ ROUTE_TO(VOLUME_ID) */ * FROM "TABLE";
```

### Hint Syntax

```sql
SELECT /*+ HINT1 HINT2 */ <columns> FROM <table>;
```

> **Note:** Use hints sparingly. The optimizer usually makes good decisions.

> **Version Note:** Hint availability and syntax may vary across HANA versions. Verify hint support in your specific HANA version's documentation.

---

## JSON Functions

SAP HANA provides functions to parse and extract data from JSON objects.

### JSON_VALUE

Extract scalar value from JSON:

```sql
SELECT JSON_VALUE('{"name": "John", "age": 30}', '$.name') FROM DUMMY;
-- Returns: John
```

### JSON_QUERY

Extract JSON object or array:

```sql
SELECT JSON_QUERY('{"items": [1, 2, 3]}', '$.items') FROM DUMMY;
-- Returns: [1, 2, 3]
```

### JSON_TABLE

Convert JSON to relational format:

```sql
SELECT jt.*
FROM JSON_TABLE(
  '[{"id": 1, "name": "A"}, {"id": 2, "name": "B"}]',
  '$[*]'
  COLUMNS (
    id INTEGER PATH '$.id',
    name VARCHAR(100) PATH '$.name'
  )
) AS jt;
```

---

## Spatial Functions

SAP HANA supports geospatial data processing.

### Spatial Data Types

- `ST_POINT` - Point geometry
- `ST_LINESTRING` - Line geometry
- `ST_POLYGON` - Polygon geometry
- `ST_GEOMETRY` - Generic geometry

### Common Functions

```sql
-- Create point
SELECT NEW ST_POINT(10.0, 20.0) FROM DUMMY;

-- Calculate distance
SELECT point1.ST_DISTANCE(point2) FROM "LOCATIONS";

-- Check containment
SELECT polygon.ST_CONTAINS(point) FROM "AREAS";

-- Calculate area
SELECT polygon.ST_AREA() FROM "REGIONS";
```

---

## Time-Series Functions

### Moving Averages

```sql
SELECT date_col,
       AVG(value) OVER (ORDER BY date_col ROWS 7 PRECEDING) AS moving_avg_7day
FROM "TIME_SERIES";
```

### Series Generation

```sql
SELECT GENERATED_PERIOD_START, GENERATED_PERIOD_END
FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 1 DAY', '2024-01-01', '2024-12-31');
```

> **Performance Note:** Generating large date ranges (multiple years with fine granularity) can impact memory and performance. For large ranges, consider chunking into smaller periods or materializing results into a calendar table.

---

## SAP-Specific Conversion Functions

### TO_DATS

Convert DATE to SAP date format (YYYYMMDD):

```sql
SELECT TO_DATS(CURRENT_DATE) FROM DUMMY;
-- Returns: 20241123
```

### TO_TIMS

Convert TIME to SAP time format (HHMMSS):

```sql
SELECT TO_TIMS(CURRENT_TIME) FROM DUMMY;
-- Returns: 143022
```

### Usage in Date Logic

```sql
DECLARE lv_date NVARCHAR(10);
SELECT SUBSTRING(TO_DATS(CURRENT_DATE), 1, 6) INTO lv_date FROM DUMMY;
-- Returns: 202411 (YYYYMM)
```

---

## CONVERT_CURRENCY Function

Currency conversion using exchange rates from SAP tables.

### Syntax

```sql
CONVERT_CURRENCY(
  AMOUNT => <amount>,
  SOURCE_UNIT => <source_currency>,
  TARGET_UNIT => <target_currency>,
  SCHEMA => <schema_name>,
  REFERENCE_DATE => <date>,
  CLIENT => <client>,
  CONVERSION_TYPE => <type>
)
```

### Example

```sql
SELECT
  doc_currcy,
  deb_cre_dc,
  CONVERT_CURRENCY(
    AMOUNT => deb_cre_dc,
    SOURCE_UNIT => doc_currcy,
    SCHEMA => 'SAPABAP1',
    TARGET_UNIT => 'EUR',
    REFERENCE_DATE => '2024-01-01',
    CLIENT => '100',
    CONVERSION_TYPE => 'EURX'
  ) AS deb_cre_eur
FROM "FINANCIAL_DATA";
```

### Complete Example with Dynamic Date

```sql
CREATE PROCEDURE convert_amounts (OUT outTab TABLE(...))
LANGUAGE SQLSCRIPT AS
BEGIN
  DECLARE lv_date NVARCHAR(10);

  SELECT SUBSTRING(TO_DATS(CURRENT_DATE), 1, 6) INTO lv_date FROM DUMMY;

  IF :lv_date <= '202106' THEN
    lv_date := '2020-12-01';
  ELSE
    SELECT TO_NVARCHAR(CURRENT_DATE) INTO lv_date FROM DUMMY;
  END IF;

  outTab = SELECT
    doc_currcy,
    CONVERT_CURRENCY(
      AMOUNT => amount,
      SOURCE_UNIT => doc_currcy,
      SCHEMA => 'SAPABAP1',
      TARGET_UNIT => 'EUR',
      REFERENCE_DATE => :lv_date,
      CLIENT => '100',
      CONVERSION_TYPE => 'EURX'
    ) AS converted_amount
  FROM "SOURCE_TABLE";
END;
```

---

## Session and System Functions

### session_context()

Retrieve session context values. Standard keys include:

| Key | Description |
|-----|-------------|
| `'CLIENT'` | SAP client (mandant) |
| `'APPLICATIONUSER'` | Application user name |
| `'LOCALE'` | Session locale |
| `'LOCALE_SAP'` | SAP locale setting |

```sql
-- Get client (SAP system)
SELECT SESSION_CONTEXT('CLIENT') FROM DUMMY;

-- Get application user
SELECT SESSION_CONTEXT('APPLICATIONUSER') FROM DUMMY;

-- Get SAP locale
SELECT SESSION_CONTEXT('LOCALE_SAP') FROM DUMMY;
```

> **Note:** Custom session variables can be set using `SET SESSION '<key>' = '<value>'` and retrieved with SESSION_CONTEXT. Check SAP documentation for version-specific available keys.

### record_count()

Get row count of table variable:

```sql
DECLARE lt_data TABLE (...);
lt_data = SELECT * FROM "TABLE";
lv_count = RECORD_COUNT(:lt_data);
```

### current_line_number

Get current line number in FOR loop:

```sql
FOR i IN 1..10 DO
  SELECT :i AS line_number FROM DUMMY;  -- or use CURRENT_LINE_NUMBER
END FOR;
```

---

## Parallel Mode Exit Triggers

SQLScript exits parallel execution mode when encountering:

| Trigger | Description |
|---------|-------------|
| Local scalar variables | Variables block parallel data flow |
| Scalar parameters in expressions | Parameters passed to expressions |
| DML/DDL in processing blocks | INSERT, UPDATE, DELETE, CREATE |
| Imperative logic | IF, WHILE, FOR, LOOP |
| Unassigned SQL statements | SELECT without assignment |

### Best Practice

```sql
CREATE PROCEDURE optimized ()
LANGUAGE SQLSCRIPT AS
BEGIN
  -- PARALLEL SECTION: Declarative statements first
  lt_data1 = SELECT * FROM "TABLE1" WHERE active = 1;
  lt_data2 = SELECT * FROM "TABLE2" WHERE status = 'A';
  lt_joined = SELECT * FROM :lt_data1 a JOIN :lt_data2 b ON a.id = b.id;

  -- SEQUENTIAL SECTION: Imperative logic last
  FOR row AS (SELECT * FROM :lt_joined) DO
    IF row.amount > 1000 THEN
      -- Process high-value items
    END IF;
  END FOR;
END;
```

---

## SET Operations Alternatives

### INTERSECT Alternative

Replace INTERSECT with JOIN for better Column Engine utilization:

**Original (slower):**
```sql
SELECT column_a FROM table_1
INTERSECT
SELECT column_a FROM table_2;
```

**Optimized (faster):**
```sql
SELECT DISTINCT table_1.column_a
FROM table_1
JOIN table_2 ON table_1.column_a = table_2.column_a;
```

> **Note:** JOIN approach works when column_a has no NULL values. Handle NULLs separately if needed.

### EXCEPT Alternative

Replace EXCEPT with LEFT JOIN:

**Original:**
```sql
SELECT id FROM table_a
EXCEPT
SELECT id FROM table_b;
```

**Optimized:**
```sql
SELECT DISTINCT a.id
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE b.id IS NULL;
```

> **NULL Handling Caveat:** The LEFT JOIN approach does not correctly handle NULL values. In SQL, `NULL = NULL` evaluates to UNKNOWN, not TRUE. If the column may contain NULLs, use this pattern instead:
>
> ```sql
> SELECT DISTINCT a.id
> FROM table_a a
> LEFT JOIN table_b b ON a.id = b.id OR (a.id IS NULL AND b.id IS NULL)
> WHERE b.id IS NULL AND NOT (a.id IS NULL AND EXISTS (SELECT 1 FROM table_b WHERE id IS NULL));
> ```
>
> Alternatively, keep the original EXCEPT for NULL-safe semantics when correctness outweighs performance.

---

## Procedure Management

### DROP PROCEDURE

```sql
DROP PROCEDURE <schema_name>.<procedure_name>;
DROP PROCEDURE <procedure_name> CASCADE;
```

### ALTER PROCEDURE Limitation

> **Important:** ALTER PROCEDURE cannot change the number or types of parameters. You must DROP and recreate the procedure:

```sql
-- Cannot do this:
ALTER PROCEDURE my_proc ADD PARAMETER (new_param INTEGER);

-- Must do this instead:
DROP PROCEDURE my_proc;
CREATE PROCEDURE my_proc (old_param INTEGER, new_param INTEGER) ...
```

> **Schema Change Strategy:** For zero-downtime deployments, consider versioned procedure naming (e.g., `my_proc_v2`) or wrapper procedures. See `references/troubleshooting.md` for error handling patterns when managing procedure dependencies.

### DROP FUNCTION

```sql
DROP FUNCTION <schema_name>.<function_name>;
DROP FUNCTION <function_name> CASCADE;
```

---

## Security Considerations

### SQL Injection Prevention

Dynamic SQL opens potential for unauthorized queries and SQL injection:

**Vulnerable Code:**
```sql
-- DANGEROUS: User input directly in SQL string
lv_sql := 'SELECT * FROM ' || :user_table || ' WHERE id = ' || :user_id;
EXECUTE IMMEDIATE :lv_sql;
```

**Safe Alternatives:**

1. **Use static SQL with parameters:**
```sql
SELECT * FROM "FIXED_TABLE" WHERE id = :user_id;
```

2. **Validate input against whitelist:**
```sql
IF :user_table NOT IN ('TABLE_A', 'TABLE_B', 'TABLE_C') THEN
  SIGNAL SQL_ERROR_CODE 10001 SET MESSAGE_TEXT = 'Invalid table name';
END IF;
```

3. **Use USING clause for parameters:**
```sql
EXECUTE IMMEDIATE 'SELECT * FROM TABLE WHERE id = ?' USING :user_id;
```

---

## AMDP Advantages Over Procedure Proxy

| Feature | AMDP | Procedure Proxy |
|---------|------|-----------------|
| Development approach | Top-down (ABAP first) | Bottom-up (DB first) |
| Lifecycle management | Automatic with ABAP transport | Manual DB deployment |
| HANA access required | No | Yes |
| Procedure creation | On first invocation | Manual activation |
| Code location | In ABAP class | Separate DB object |
| Version control | ABAP repository | Separate tracking |
| Transport | With ABAP objects | Separate transport |

---

## APPLY_FILTER Function

Dynamic filtering function that applies a filter string to a table, table variable, or view at runtime.

### Syntax

```sql
APPLY_FILTER(<dataset>, <filter_string>)
```

### Parameters

| Parameter | Description |
|-----------|-------------|
| `<dataset>` | Table, view, calculation view, or table variable |
| `<filter_string>` | WHERE clause condition as string |

### Example

```sql
CREATE PROCEDURE dynamic_filter (
  IN iv_filter NVARCHAR(500),
  OUT et_result TABLE (id INTEGER, name NVARCHAR(100))
)
LANGUAGE SQLSCRIPT AS
BEGIN
  et_result = APPLY_FILTER("CUSTOMERS", :iv_filter);
END;

-- Call with dynamic filter
CALL dynamic_filter('country = ''US'' AND status = ''ACTIVE''', ?);
```

### Use Cases

- User-defined search criteria
- Dynamic report filtering
- Configurable data extraction

> **Note:** Prefer APPLY_FILTER over dynamic SQL with 'IN' clauses for better performance and security.

---

## Array Functions

### ARRAY_AGG

Aggregates column values into an array:

```sql
DECLARE arr_ids INTEGER ARRAY;

-- Convert table column to array
arr_ids = ARRAY_AGG(:lt_data.id ORDER BY id ASC);

-- With ordering
arr_names = ARRAY_AGG(:lt_employees.name ORDER BY name DESC);
```

> **Note:** ARRAY_AGG overwrites existing array contents; it doesn't append.

### TRIM_ARRAY

Removes elements from the end of an array:

```sql
DECLARE arr INTEGER ARRAY := ARRAY(1, 2, 3, 4, 5);

-- Remove last 2 elements
arr = TRIM_ARRAY(:arr, 2);
-- Result: ARRAY(1, 2, 3)
```

### Array Concatenation

Combine two arrays using CONCAT or ||:

```sql
DECLARE arr1 INTEGER ARRAY := ARRAY(1, 2, 3);
DECLARE arr2 INTEGER ARRAY := ARRAY(4, 5, 6);

-- Concatenate arrays
arr_combined = :arr1 || :arr2;
-- Result: ARRAY(1, 2, 3, 4, 5, 6)
```

### CARDINALITY

Get the number of elements in an array:

```sql
DECLARE arr INTEGER ARRAY := ARRAY(10, 20, 30);
DECLARE lv_count INTEGER;

lv_count = CARDINALITY(:arr);
-- Result: 3

-- Check if array is empty
IF CARDINALITY(:arr) = 0 THEN
  -- Array is empty
END IF;
```

---

## CONTINUE HANDLER

Unlike EXIT HANDLER which terminates execution, CONTINUE HANDLER allows execution to continue after handling the exception.

### Syntax

```sql
DECLARE CONTINUE HANDLER FOR <condition>
  <statement>;
```

### Example

```sql
CREATE PROCEDURE process_with_continue ()
LANGUAGE SQLSCRIPT AS
BEGIN
  DECLARE lv_errors INTEGER := 0;

  -- Continue processing even after errors
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    lv_errors := :lv_errors + 1;
    -- Log error but continue
    INSERT INTO "ERROR_LOG" VALUES (::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE);
  END;

  -- These will all be attempted even if some fail
  INSERT INTO "TABLE1" VALUES (1, 'A');
  INSERT INTO "TABLE2" VALUES (2, 'B');  -- May fail
  INSERT INTO "TABLE3" VALUES (3, 'C');  -- Still executed

  -- Check total errors
  IF :lv_errors > 0 THEN
    SELECT :lv_errors || ' errors occurred' FROM DUMMY;
  END IF;
END;
```

### EXIT vs CONTINUE Handler

| Feature | EXIT HANDLER | CONTINUE HANDLER |
|---------|--------------|------------------|
| After handling | Execution stops | Execution continues |
| Use case | Critical errors | Recoverable errors |
| Subsequent code | Not executed | Executed |

---

## CE Functions (Calculation Engine)

> **Deprecation Notice:** CE Functions are legacy features. SAP recommends using standard SQL instead for new development.

CE Functions provide direct access to the HANA Calculation Engine:

### Common CE Functions

| Function | Purpose |
|----------|---------|
| `CE_PROJECTION` | Select/rename columns, apply filters |
| `CE_JOIN` | Join tables |
| `CE_LEFT_OUTER_JOIN` | Left outer join |
| `CE_UNION_ALL` | Union tables |
| `CE_COLUMN_TABLE` | Access column table |
| `CE_CALC` | Calculated columns |
| `CE_AGGREGATION` | Aggregate data |

### CE_PROJECTION Example

```sql
-- Restrict columns and apply filter
lt_filtered = CE_PROJECTION(
  :lt_products,
  ["PRODUCTID", "PRICE", "NAME"],
  '"PRICE" > 50'
);
```

> **Recommendation:** Use standard SQL SELECT statements instead of CE functions for better maintainability and optimizer support.

---

## SQLScript Analysis Tools

### SQLScript Code Analyzer

Identifies code quality, security, and performance issues.

**Analysis Procedures:**

```sql
-- Analyze existing objects
CALL SYS.ANALYZE_SQLSCRIPT_OBJECTS('SCHEMA_NAME', 'PROCEDURE_NAME', NULL);

-- Analyze source code before creation
CALL SYS.ANALYZE_SQLSCRIPT_DEFINITION('<source_code>');
```

**Common Analysis Rules:**

| Rule | Description |
|------|-------------|
| `UNCHECKED_SQL_INJECTION_SAFETY` | Potential SQL injection vulnerability |
| `USE_OF_DYNAMIC_SQL` | Dynamic SQL detected |
| `UNNECESSARY_VARIABLE` | Variable declared but not used |
| `UNUSED_PARAMETER` | Parameter not used in procedure |

### SQLScript Plan Profiler

Performance profiling for SQLScript execution.

**Enable Profiling:**

```sql
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM')
SET ('sqlscript', 'plan_profiler_enabled') = 'true';
```

**View Results:**

```sql
SELECT * FROM M_SQLSCRIPT_PLAN_PROFILER_RESULTS
WHERE PROCEDURE_NAME = 'MY_PROCEDURE';
```

### SQLScript Code Coverage

Tracks which statements are executed during testing.

```sql
-- Enable code coverage
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM')
SET ('sqlscript', 'code_coverage') = 'true';

-- View coverage results
SELECT * FROM M_SQLSCRIPT_CODE_COVERAGE_RESULTS;
```

---

## SQLScript Pragmas

Compiler directives that control SQLScript behavior.

### Syntax

```sql
DO
BEGIN
  PRAGMA <pragma_name> = <value>;
  -- Code affected by pragma
END;
```

### Common Pragmas

| Pragma | Purpose |
|--------|---------|
| `AUTONOMOUS_TRANSACTION` | Execute in separate transaction |
| `SUPPRESS_WARNINGS` | Suppress specific warnings |

### Example

```sql
CREATE PROCEDURE autonomous_logging (IN iv_message NVARCHAR(500))
LANGUAGE SQLSCRIPT AS
BEGIN
  PRAGMA AUTONOMOUS_TRANSACTION = ON;

  -- This INSERT commits independently
  INSERT INTO "AUDIT_LOG" (message, logged_at)
  VALUES (:iv_message, CURRENT_TIMESTAMP);
END;

```

### references/troubleshooting.md

```markdown
# SQLScript Troubleshooting Guide

## Table of Contents

- [Common Errors and Solutions](#common-errors-and-solutions)
  - [Syntax Errors](#syntax-errors)
    - ["feature not supported: Scalar UDF does not support SQL statements"](#feature-not-supported-scalar-udf-does-not-support-sql-statements)
    - ["invalid column name" / SQL_ERROR_CODE 260](#invalid-column-name--sql_error_code-260)
    - ["invalid table name" / SQL_ERROR_CODE 259](#invalid-table-name--sql_error_code-259)
    - ["identifier is too long" / SQL_ERROR_CODE 383](#identifier-is-too-long--sql_error_code-383)
    - ["syntax error" / SQL_ERROR_CODE 257](#syntax-error--sql_error_code-257)
    - ["not a valid procedure" / SQL_ERROR_CODE 363](#not-a-valid-procedure--sql_error_code-363)
  - [Runtime Errors](#runtime-errors)
    - [" insufficient privilege" / SQL_ERROR_CODE 10](#-insufficient-privilege--sql_error_code-10)
    - ["unique constraint violated" / SQL_ERROR_CODE 301](#unique-constraint-violated--sql_error_code-301)
    - ["foreign key violation" / SQL_ERROR_CODE 343](#foreign-key-violation--sql_error_code-343)
    - ["transaction rolled back" / SQL_ERROR_CODE 2](#transaction-rolled-back--sql_error_code-2)
    - ["memory allocation failed" / SQL_ERROR_CODE 30109](#memory-allocation-failed--sql_error_code-30109)
    - ["connection limit exceeded" / SQL_ERROR_CODE 126](#connection-limit-exceeded--sql_error_code-126)
    - ["division by zero" / SQL_ERROR_CODE 1194](#division-by-zero--sql_error_code-1194)
    - ["invalid date" / SQL_ERROR_CODE 301](#invalid-date--sql_error_code-301)
  - [Logic Errors](#logic-errors)
    - [No data returned / NULL values](#no-data-returned--null-values)
    - [Wrong data type conversion](#wrong-data-type-conversion)
    - [Incorrect NULL handling](#incorrect-null-handling)
    - [Cursors not found / already closed](#cursors-not-found--already-closed)
    - [Arrays cannot be returned](#arrays-cannot-be-returned)
  - [Performance Issues](#performance-issues)
    - [Query too slow](#query-too-slow)
    - [High memory consumption](#high-memory-consumption)
    - [Too many locks](#too-many-locks)
    - [Unstable query performance](#unstable-query-performance)
    - [Execution plan changes](#execution-plan-changes)
- [Debugging Techniques](#debugging-techniques)
  - [Using SQLScript Debugger](#using-sqlscript-debugger)
  - [Plan Visualizer](#plan-visualizer)
  - [Expensive Statement Trace](#expensive-statement-trace)
  - [SQL Analyzer](#sql-analyzer)
  - [Logging with DUMMY table](#logging-with-dummy-table)
  - [Error logging procedures](#error-logging-procedures)
- [Testing Strategies](#testing-strategies)
  - [Unit testing SQLScript](#unit-testing-sqlscript)
  - [Performance testing](#performance-testing)
  - [Integration testing](#integration-testing)
- [Best Practices](#best-practices)
  - [Error handling patterns](#error-handling-patterns)
  - [Defensive programming](#defensive-programming)
  - [Logging strategy](#logging-strategy)

## Common Errors and Solutions

### Syntax Errors

#### Error: "feature not supported: Scalar UDF does not support SQL statements"

**Cause:** Attempting to use SELECT or other SQL statements in scalar UDF. This restriction applies to HANA versions prior to 2.0 SPS05. Starting with HANA 2.0 SPS05+, scalar UDFs support SQL statements including SELECT.

**Version Support:**
- HANA 1.0 / 2.0 (prior to SPS05): SQL statements NOT supported in Scalar UDF
- HANA 2.0 SPS05+: SQL statements supported in Scalar UDF

**Solution:**
```sql
-- Option 1: Use Table UDF instead
CREATE FUNCTION get_value (iv_id INTEGER)
RETURNS TABLE (result_value INTEGER)
LANGUAGE SQLSCRIPT AS
BEGIN
  RETURN SELECT value AS result_value FROM "TABLE" WHERE id = :iv_id;
END;

-- Option 2: Use expressions only in Scalar UDF
CREATE FUNCTION calculate_discount (iv_amount DECIMAL)
RETURNS DECIMAL
LANGUAGE SQLSCRIPT AS
BEGIN
  RETURN :iv_amount * 0.1;  -- Expression only, no SQL
END;
```

---

#### Error: "invalid column name" / SQL_ERROR_CODE 260

**Cause:** Column doesn't exist or typo in column name.

**Solutions:**
```sql
-- Check column exists
SELECT COLUMN_NAME FROM TABLE_COLUMNS
WHERE SCHEMA_NAME = 'YOUR_SCHEMA' AND TABLE_NAME = 'YOUR_TABLE';

-- Check for case sensitivity (HANA is case-sensitive for quoted identifiers)
SELECT "ColumnName" FROM "TABLE";  -- Exact case required
SELECT COLUMNNAME FROM "TABLE";    -- Unquoted = uppercase
```

---

#### Error: "invalid table name" / SQL_ERROR_CODE 259

**Cause:** Table doesn't exist or wrong schema.

**Solutions:**
```sql
-- Check table exists
SELECT TABLE_NAME FROM TABLES
WHERE SCHEMA_NAME = 'YOUR_SCHEMA' AND TABLE_NAME = 'YOUR_TABLE';

-- Use fully qualified name
SELECT * FROM "SCHEMA_NAME"."TABLE_NAME";

-- Or set default schema in procedure
CREATE PROCEDURE my_proc ()
DEFAULT SCHEMA "MY_SCHEMA"
LANGUAGE SQLSCRIPT AS
BEGIN
  SELECT * FROM "TABLE_NAME";  -- Uses MY_SCHEMA
END;
```

---

### Variable Errors

#### Error: "variable not defined"

**Cause:** Variable not declared or scope issue.

**Solutions:**
```sql
-- Ensure DECLARE before use
DECLARE lv_count INTEGER;
lv_count := 10;

-- Check scope - variables only valid in declaring block
BEGIN
  DECLARE lv_local INTEGER := 5;
END;
-- lv_local not accessible here
```

---

#### Error: "cannot use variable before assignment"

**Cause:** Using uninitialized OUT parameter or variable.

**Solution:**
```sql
-- Initialize variable at declaration
DECLARE lv_result NVARCHAR(100) := '';

-- Or assign before first use
DECLARE lv_result NVARCHAR(100);
lv_result := 'initial';
```

---

#### Error: "scalar variable expected" / "table variable expected"

**Cause:** Type mismatch between scalar and table variable.

**Solutions:**
```sql
-- For single value, use scalar variable
DECLARE lv_count INTEGER;
SELECT COUNT(*) INTO lv_count FROM "TABLE";

-- For multiple rows, use table variable
lt_data = SELECT * FROM "TABLE";

-- To get scalar from table variable
SELECT COUNT(*) INTO lv_count FROM :lt_data;
```

---

### Table Variable Errors

#### Error: "TABLE variable expected" when using colon

**Cause:** Incorrect colon usage.

**Rules:**
```sql
-- Use colon ONLY when referencing variable value
lt_result = SELECT * FROM :lt_input;  -- Reading lt_input
SELECT * FROM :lt_result;             -- Reading lt_result

-- NO colon when assigning
lt_result = SELECT * FROM "TABLE";    -- Assigning to lt_result

-- NO colon in table variable operators
:lt_result.INSERT((1, 'test'));       -- Colon before variable name
```

---

#### Error: "array cannot be used in this context"

**Cause:** Attempting to return array from procedure or use in unsupported context.

**Solution:**
```sql
-- Arrays cannot be returned from procedures
-- Convert to table using UNNEST
DECLARE arr INTEGER ARRAY := ARRAY(1, 2, 3);
lt_result = SELECT * FROM UNNEST(:arr) AS t(value);
```

---

### Cursor Errors

#### Error: "cursor is not open" / "cursor already open"

**Cause:** Incorrect cursor lifecycle management.

**Solution:**
```sql
DECLARE CURSOR cur FOR SELECT * FROM "TABLE";

-- Correct sequence: OPEN -> FETCH -> CLOSE
OPEN cur;
FETCH cur INTO lv_var;
WHILE NOT cur::NOTFOUND DO
  -- process
  FETCH cur INTO lv_var;
END WHILE;
CLOSE cur;

-- If reusing cursor, close before reopening
IF NOT cur::ISCLOSED THEN
  CLOSE cur;
END IF;
OPEN cur;
```

---

### Exception Handling Errors

#### Error: EXIT HANDLER not working

**Cause:** Handler declared in wrong position.

**Solution:**
```sql
BEGIN
  -- 1. All DECLARE statements first
  DECLARE lv_var INTEGER;
  DECLARE my_condition CONDITION FOR SQL_ERROR_CODE 301;

  -- 2. EXIT HANDLER must be LAST declaration
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SELECT ::SQL_ERROR_CODE FROM DUMMY;

  -- 3. Then procedural code
  INSERT INTO "TABLE" VALUES (1);
END;
```

---

#### Error: "RESIGNAL can only be used in exception handler"

**Cause:** RESIGNAL used outside EXIT HANDLER block.

**Solution:**
```sql
-- RESIGNAL only valid inside EXIT HANDLER
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  -- Log error
  INSERT INTO "ERROR_LOG" VALUES (::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE);
  -- Re-throw
  RESIGNAL;  -- Only valid here
END;
```

---

#### Error: User-defined error code out of range

**Cause:** Using error code outside 10000-19999 range.

**Solution:**
```sql
-- User-defined codes: 10000-19999
SIGNAL SQL_ERROR_CODE 10001 SET MESSAGE_TEXT = 'Custom error';

-- NOT valid:
-- SIGNAL SQL_ERROR_CODE 301 SET MESSAGE_TEXT = 'Cannot use system codes';
```

---

### Performance Issues

#### Issue: Procedure runs slowly

**Diagnostic Steps:**

```sql
-- 1. Check execution plan
EXPLAIN PLAN FOR
SELECT * FROM "LARGE_TABLE" WHERE status = 'ACTIVE';

-- 2. Check for full table scans
SELECT * FROM "EXPLAIN_CALL_PLANS"
WHERE OPERATOR_NAME = 'TABLE SCAN';

-- 3. Check for expensive operations
SELECT * FROM M_EXPENSIVE_STATEMENTS
ORDER BY DURATION DESC;
```

> **System View Prerequisites:**
> - `EXPLAIN_CALL_PLANS`: Available after executing EXPLAIN PLAN; results stored per session
> - `M_EXPENSIVE_STATEMENTS`: Requires `MONITORING` system privilege or `DATA ADMIN` role
> - Enable expensive statement tracing first (see configuration below) for M_EXPENSIVE_STATEMENTS to contain data
>
> Some views may not be available in SAP HANA Cloud; check platform-specific documentation.

**Common Causes and Solutions:**

| Symptom | Cause | Solution |
|---------|-------|----------|
| Full table scan | Missing index | Create appropriate index |
| Engine conversion | Mixing row/column store | Use consistent store type |
| Sequential execution | Imperative logic | Convert to set-based |
| High memory | Large intermediate results | Filter earlier |

---

#### Issue: "memory allocation failed"

**Cause:** Query exceeds available memory.

**Solutions:**
```sql
-- Process in batches
DECLARE lv_offset INTEGER := 0;
DECLARE lv_batch INTEGER := 10000;

WHILE lv_offset < lv_total DO
  lt_batch = SELECT * FROM "LARGE_TABLE"
             ORDER BY id
             LIMIT :lv_batch OFFSET :lv_offset;
  -- Process batch
  lv_offset := :lv_offset + :lv_batch;
END WHILE;

-- Or limit result set
SELECT TOP 10000 * FROM "LARGE_TABLE";
```

---

### AMDP Specific Errors

#### Error: "AMDP method must implement IF_AMDP_MARKER_HDB"

**Cause:** Missing interface declaration.

**Solution:**
```abap
CLASS zcl_my_amdp DEFINITION.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.  " Add this line
    ...
ENDCLASS.
```

---

#### Error: "BY DATABASE can only be specified for methods of AMDP classes"

**Cause:** Class doesn't implement AMDP interface.

**Solution:**
```abap
" Ensure class implements marker interface
CLASS zcl_amdp DEFINITION.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    METHODS my_method ...
ENDCLASS.

CLASS zcl_amdp IMPLEMENTATION.
  METHOD my_method BY DATABASE PROCEDURE
    FOR HDB LANGUAGE SQLSCRIPT.
    ...
  ENDMETHOD.
ENDCLASS.
```

---

#### Error: "Table not found in USING clause"

**Cause:** Database table accessed but not declared in USING.

**Solution:**
```abap
METHOD my_method BY DATABASE PROCEDURE
  FOR HDB
  LANGUAGE SQLSCRIPT
  USING mara makt vbak.  " List ALL tables accessed

  et_result = SELECT * FROM mara
              JOIN makt ON mara.matnr = makt.matnr;  " Both in USING
ENDMETHOD.
```

---

#### Error: "CHANGING parameter not supported"

**Cause:** AMDP doesn't support CHANGING parameters.

**Solution:**
```abap
" Use IMPORTING and EXPORTING instead
METHODS my_method
  IMPORTING VALUE(iv_input) TYPE string
  EXPORTING VALUE(et_output) TYPE tt_output.  " Not CHANGING
```

---

### Debugging Techniques

#### Print Debug Information

```sql
DO
BEGIN
  DECLARE lv_debug NVARCHAR(1000);

  -- Using SELECT from DUMMY for output
  SELECT 'Starting process' AS debug_msg FROM DUMMY;

  lt_data = SELECT * FROM "TABLE";

  SELECT 'Processed ' || COUNT(*) || ' rows' AS debug_msg
  FROM :lt_data;
END;
```

---

#### Logging to Table

```sql
-- Create log table
CREATE TABLE "DEBUG_LOG" (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  message NVARCHAR(5000),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Log in procedure
CREATE PROCEDURE debug_example ()
LANGUAGE SQLSCRIPT AS
BEGIN
  INSERT INTO "DEBUG_LOG" (message) VALUES ('Step 1: Started');

  lt_data = SELECT * FROM "SOURCE_TABLE";
  INSERT INTO "DEBUG_LOG" (message)
  VALUES ('Step 2: Loaded ' || RECORD_COUNT(:lt_data) || ' rows');

  -- More processing...
  INSERT INTO "DEBUG_LOG" (message) VALUES ('Step 3: Completed');
END;
```

---

#### Checking Variable Values

```sql
DO
BEGIN
  DECLARE lv_count INTEGER;
  DECLARE lv_total DECIMAL(15,2);

  SELECT COUNT(*), SUM(amount) INTO lv_count, lv_total FROM "ORDERS";

  -- Output for debugging
  SELECT :lv_count AS row_count,
         :lv_total AS total_amount,
         CURRENT_TIMESTAMP AS check_time
  FROM DUMMY;
END;
```

---

## Error Code Quick Reference

| Code | Description | Common Cause |
|------|-------------|--------------|
| 258 | Insufficient privilege | Missing grants |
| 259 | Invalid table name | Table doesn't exist |
| 260 | Invalid column name | Column doesn't exist or typo |
| 261 | Invalid index name | Index doesn't exist |
| 301 | Unique constraint violation | Duplicate key insert |
| 339 | Foreign key violation | Referenced record missing |
| 362 | NOT NULL constraint | NULL in required column |
| 1299 | No data found | Query returned no rows |
| 1304 | Resource busy | Lock contention |
| 10000-19999 | User-defined | Application-specific errors |

---

## Getting Help

### Check SAP Documentation

- SQLScript Reference: `[https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-sqlscript-reference/`](https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-sqlscript-reference/`)
- SQL Reference: `[https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/`](https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/`)

### System Information Queries

```sql
-- Check HANA version
SELECT VERSION FROM M_DATABASE;

-- Check available memory
SELECT * FROM M_HOST_RESOURCE_UTILIZATION;

-- Check running statements
SELECT * FROM M_ACTIVE_STATEMENTS;

-- Check error history
SELECT * FROM M_ERROR_CODES WHERE ERROR_CODE = <your_error_code>;
```

```

### templates/simple-procedure.sql

```sql
/**
 * Simple Stored Procedure Template
 *
 * A production-ready template for basic SQLScript procedures with:
 * - Input parameter validation
 * - Proper error handling with EXIT HANDLER
 * - Structured logging
 * - Documentation comments
 *
 * Usage: Replace placeholders (<SCHEMA>, <PROCEDURE_NAME>, etc.) with actual values
 *
 * @version 2.1.0
 * @last_updated 2025-12-27
 */

CREATE OR REPLACE PROCEDURE "<SCHEMA>"."<PROCEDURE_NAME>"
(
    -- Input Parameters
    IN  iv_input_id     INTEGER,                    -- Primary input identifier
    IN  iv_filter_value NVARCHAR(100) DEFAULT '',   -- Optional filter (default empty)

    -- Output Parameters
    OUT ev_success      INTEGER,                    -- 1 = success, 0 = failure
    OUT ev_message      NVARCHAR(500),              -- Status/error message
    OUT et_result       TABLE (                     -- Result set
        id          INTEGER,
        name        NVARCHAR(100),
        created_at  TIMESTAMP
    )
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
DEFAULT SCHEMA "<SCHEMA>"
READS SQL DATA
AS
BEGIN
    /*******************************************************************
     * Variable Declarations
     *******************************************************************/
    DECLARE lv_record_count INTEGER := 0;
    DECLARE lv_start_time   TIMESTAMP := CURRENT_TIMESTAMP;

    /*******************************************************************
     * Exception Handler - Catches all SQL exceptions
     *
     * ::SQL_ERROR_CODE    - Numeric error code
     * ::SQL_ERROR_MESSAGE - Error message text
     *******************************************************************/
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ev_success := 0;
        ev_message := 'Error [' || ::SQL_ERROR_CODE || ']: ' || ::SQL_ERROR_MESSAGE;

        -- Optional: Log error to audit table
        -- INSERT INTO "<SCHEMA>"."ERROR_LOG" (
        --     procedure_name, error_code, error_message, input_params, created_at
        -- ) VALUES (
        --     '<PROCEDURE_NAME>', ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE,
        --     'iv_input_id=' || :iv_input_id, CURRENT_TIMESTAMP
        -- );
    END;

    /*******************************************************************
     * Input Validation
     *******************************************************************/
    IF :iv_input_id IS NULL OR :iv_input_id <= 0 THEN
        ev_success := 0;
        ev_message := 'Invalid input: iv_input_id must be a positive integer';
        RETURN;
    END IF;

    /*******************************************************************
     * Main Logic
     *******************************************************************/

    -- Example: Fetch data with optional filter
    IF :iv_filter_value = '' THEN
        et_result = SELECT
                        id,
                        name,
                        created_at
                    FROM "<SCHEMA>"."<SOURCE_TABLE>"
                    WHERE parent_id = :iv_input_id
                    ORDER BY created_at DESC;
    ELSE
        et_result = SELECT
                        id,
                        name,
                        created_at
                    FROM "<SCHEMA>"."<SOURCE_TABLE>"
                    WHERE parent_id = :iv_input_id
                      AND name LIKE '%' || :iv_filter_value || '%'
                    ORDER BY created_at DESC;
    END IF;

    -- Get record count for logging
    SELECT COUNT(*) INTO lv_record_count FROM :et_result;

    /*******************************************************************
     * Success Response
     *******************************************************************/
    ev_success := 1;
    ev_message := 'Success: Retrieved ' || :lv_record_count || ' records in ' ||
                  NANO100_BETWEEN(:lv_start_time, CURRENT_TIMESTAMP) / 10000 || 'ms';

END;

/*
 * Example Call:
 *
 * CALL "<SCHEMA>"."<PROCEDURE_NAME>"(
 *     iv_input_id     => 100,
 *     iv_filter_value => 'test',
 *     ev_success      => ?,
 *     ev_message      => ?,
 *     et_result       => ?
 * );
 */

```

### templates/procedure-with-error-handling.sql

```sql
/**
 * Comprehensive Error Handling Procedure Template
 *
 * Production-ready template demonstrating advanced SQLScript error handling:
 * - Multiple named conditions for specific errors
 * - Nested exception handlers
 * - Error logging to audit table
 * - SIGNAL for custom exceptions
 * - RESIGNAL for re-throwing
 * - Transaction rollback patterns
 *
 * @version 2.1.0
 * @last_updated 2025-12-27
 */

CREATE OR REPLACE PROCEDURE "<SCHEMA>"."<PROCEDURE_NAME>_WITH_ERROR_HANDLING"
(
    -- Input Parameters
    IN  iv_order_id      INTEGER,
    IN  iv_quantity      INTEGER,
    IN  iv_user_id       NVARCHAR(50),

    -- Output Parameters
    OUT ev_success       INTEGER,           -- 1 = success, 0 = failure
    OUT ev_error_code    INTEGER,           -- Specific error code
    OUT ev_error_message NVARCHAR(1000)     -- Detailed error message
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
DEFAULT SCHEMA "<SCHEMA>"
AS
BEGIN
    /*******************************************************************
     * Constants for Custom Error Codes (10000-19999 range)
     *******************************************************************/
    DECLARE VALIDATION_ERROR       INTEGER := 10001;
    DECLARE BUSINESS_RULE_ERROR    INTEGER := 10002;
    DECLARE INSUFFICIENT_STOCK     INTEGER := 10003;
    DECLARE CONCURRENT_UPDATE      INTEGER := 10004;

    /*******************************************************************
     * Named Conditions for Specific SQL Errors
     *******************************************************************/
    DECLARE duplicate_key    CONDITION FOR SQL_ERROR_CODE 301;   -- Unique constraint violation
    DECLARE no_data_found    CONDITION FOR SQL_ERROR_CODE 1299;  -- No data found
    DECLARE lock_timeout     CONDITION FOR SQL_ERROR_CODE 131;   -- Lock wait timeout
    DECLARE foreign_key      CONDITION FOR SQL_ERROR_CODE 461;   -- Foreign key violation

    /*******************************************************************
     * Custom Conditions for Business Logic
     *******************************************************************/
    DECLARE validation_failed     CONDITION FOR SQL_ERROR_CODE 10001;
    DECLARE business_rule_failed  CONDITION FOR SQL_ERROR_CODE 10002;
    DECLARE stock_insufficient    CONDITION FOR SQL_ERROR_CODE 10003;

    /*******************************************************************
     * Local Variables
     *******************************************************************/
    DECLARE lv_current_stock    INTEGER;
    DECLARE lv_order_status     NVARCHAR(20);
    DECLARE lv_transaction_id   NVARCHAR(36);
    DECLARE lv_start_time       TIMESTAMP := CURRENT_TIMESTAMP;

    /*******************************************************************
     * Exception Handlers (processed in reverse declaration order)
     *******************************************************************/

    -- Handler for duplicate key violations
    DECLARE EXIT HANDLER FOR duplicate_key
    BEGIN
        ev_success := 0;
        ev_error_code := 301;
        ev_error_message := 'Duplicate entry: Record already exists for order ' || :iv_order_id;

        CALL "<SCHEMA>"."LOG_ERROR"(
            '<PROCEDURE_NAME>', 301, :ev_error_message, :iv_user_id
        );
    END;

    -- Handler for no data found
    DECLARE EXIT HANDLER FOR no_data_found
    BEGIN
        ev_success := 0;
        ev_error_code := 1299;
        ev_error_message := 'Order not found: ' || :iv_order_id;

        CALL "<SCHEMA>"."LOG_ERROR"(
            '<PROCEDURE_NAME>', 1299, :ev_error_message, :iv_user_id
        );
    END;

    -- Handler for lock timeout (concurrent access)
    DECLARE EXIT HANDLER FOR lock_timeout
    BEGIN
        ev_success := 0;
        ev_error_code := 131;
        ev_error_message := 'Concurrent update detected. Please retry.';

        CALL "<SCHEMA>"."LOG_ERROR"(
            '<PROCEDURE_NAME>', 131, :ev_error_message, :iv_user_id
        );
    END;

    -- Handler for insufficient stock
    DECLARE EXIT HANDLER FOR stock_insufficient
    BEGIN
        ev_success := 0;
        ev_error_code := :INSUFFICIENT_STOCK;
        ev_error_message := 'Insufficient stock for order ' || :iv_order_id ||
                           '. Requested: ' || :iv_quantity || ', Available: ' || :lv_current_stock;

        CALL "<SCHEMA>"."LOG_ERROR"(
            '<PROCEDURE_NAME>', :INSUFFICIENT_STOCK, :ev_error_message, :iv_user_id
        );
    END;

    -- Handler for validation failures
    DECLARE EXIT HANDLER FOR validation_failed
    BEGIN
        ev_success := 0;
        ev_error_code := :VALIDATION_ERROR;
        -- ev_error_message already set by SIGNAL

        CALL "<SCHEMA>"."LOG_ERROR"(
            '<PROCEDURE_NAME>', :VALIDATION_ERROR, :ev_error_message, :iv_user_id
        );
    END;

    -- Generic handler for all other SQL exceptions (catch-all)
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ev_success := 0;
        ev_error_code := ::SQL_ERROR_CODE;
        ev_error_message := 'Unexpected error [' || ::SQL_ERROR_CODE || ']: ' ||
                           ::SQL_ERROR_MESSAGE;

        CALL "<SCHEMA>"."LOG_ERROR"(
            '<PROCEDURE_NAME>', ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE, :iv_user_id
        );
    END;

    /*******************************************************************
     * Generate Transaction ID for Tracking
     *******************************************************************/
    lv_transaction_id := SYSUUID;

    /*******************************************************************
     * Input Validation with Custom Exceptions
     *******************************************************************/

    -- Validate order_id
    IF :iv_order_id IS NULL OR :iv_order_id <= 0 THEN
        ev_error_message := 'Invalid order_id: Must be a positive integer';
        SIGNAL validation_failed SET MESSAGE_TEXT = :ev_error_message;
    END IF;

    -- Validate quantity
    IF :iv_quantity IS NULL OR :iv_quantity <= 0 THEN
        ev_error_message := 'Invalid quantity: Must be a positive integer';
        SIGNAL validation_failed SET MESSAGE_TEXT = :ev_error_message;
    END IF;

    -- Validate user_id
    IF :iv_user_id IS NULL OR LENGTH(TRIM(:iv_user_id)) = 0 THEN
        ev_error_message := 'Invalid user_id: Cannot be empty';
        SIGNAL validation_failed SET MESSAGE_TEXT = :ev_error_message;
    END IF;

    /*******************************************************************
     * Business Logic with Targeted Error Handling
     *******************************************************************/

    -- Step 1: Verify order exists and get status
    SELECT status INTO lv_order_status
    FROM "<SCHEMA>"."ORDERS"
    WHERE order_id = :iv_order_id;

    -- Step 2: Business rule - Only pending orders can be modified
    IF :lv_order_status != 'PENDING' THEN
        ev_error_message := 'Order ' || :iv_order_id || ' is ' || :lv_order_status ||
                           '. Only PENDING orders can be modified.';
        SIGNAL business_rule_failed SET MESSAGE_TEXT = :ev_error_message;
    END IF;

    -- Step 3: Check stock availability
    SELECT available_qty INTO lv_current_stock
    FROM "<SCHEMA>"."INVENTORY"
    WHERE product_id = (
        SELECT product_id FROM "<SCHEMA>"."ORDERS" WHERE order_id = :iv_order_id
    );

    IF :lv_current_stock < :iv_quantity THEN
        SIGNAL stock_insufficient;
    END IF;

    -- Step 4: Update order (may trigger lock_timeout if concurrent access)
    UPDATE "<SCHEMA>"."ORDERS"
    SET quantity = :iv_quantity,
        modified_by = :iv_user_id,
        modified_at = CURRENT_TIMESTAMP,
        transaction_id = :lv_transaction_id
    WHERE order_id = :iv_order_id
      AND status = 'PENDING';

    -- Step 5: Update inventory
    UPDATE "<SCHEMA>"."INVENTORY"
    SET available_qty = available_qty - :iv_quantity,
        reserved_qty = reserved_qty + :iv_quantity,
        modified_at = CURRENT_TIMESTAMP
    WHERE product_id = (
        SELECT product_id FROM "<SCHEMA>"."ORDERS" WHERE order_id = :iv_order_id
    );

    /*******************************************************************
     * Success Response
     *******************************************************************/
    ev_success := 1;
    ev_error_code := 0;
    ev_error_message := 'Order ' || :iv_order_id || ' updated successfully. ' ||
                        'Transaction: ' || :lv_transaction_id || '. ' ||
                        'Duration: ' || NANO100_BETWEEN(:lv_start_time, CURRENT_TIMESTAMP) / 10000000 || 'ms';

END;

/*
 * Supporting Error Logging Procedure
 */
-- CREATE OR REPLACE PROCEDURE "<SCHEMA>"."LOG_ERROR"
-- (
--     IN iv_procedure_name NVARCHAR(128),
--     IN iv_error_code     INTEGER,
--     IN iv_error_message  NVARCHAR(1000),
--     IN iv_user_id        NVARCHAR(50)
-- )
-- LANGUAGE SQLSCRIPT
-- SQL SECURITY DEFINER
-- AS
-- BEGIN
--     INSERT INTO "<SCHEMA>"."ERROR_LOG" (
--         log_id, procedure_name, error_code, error_message, user_id, created_at
--     ) VALUES (
--         "<SCHEMA>"."ERROR_LOG_SEQ".NEXTVAL,
--         :iv_procedure_name,
--         :iv_error_code,
--         :iv_error_message,
--         :iv_user_id,
--         CURRENT_TIMESTAMP
--     );
-- END;

```

### templates/table-function.sql

```sql
/**
 * Table User-Defined Function (Table UDF) Template
 *
 * Production-ready template for SQLScript table functions with:
 * - Parameter validation with CASE expressions (no IF in functions)
 * - Proper READS SQL DATA declaration
 * - Efficient set-based operations
 * - Documentation and usage examples
 *
 * Note: Table UDFs cannot perform DML (INSERT/UPDATE/DELETE)
 *       and must return a table type.
 *
 * @version 2.1.0
 * @last_updated 2025-12-27
 */

CREATE OR REPLACE FUNCTION "<SCHEMA>"."<FUNCTION_NAME>_TABLE_UDF"
(
    -- Input Parameters (all IN by default, cannot have OUT params)
    iv_start_date   DATE,
    iv_end_date     DATE,
    iv_category     NVARCHAR(50) DEFAULT '',    -- Optional filter
    iv_limit        INTEGER DEFAULT 1000        -- Row limit for safety
)
RETURNS TABLE (
    -- Return column definitions
    record_id       INTEGER,
    category_name   NVARCHAR(100),
    metric_value    DECIMAL(15,2),
    record_date     DATE,
    status          NVARCHAR(20),
    row_rank        INTEGER
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA                    -- Required for table functions
AS
BEGIN
    /*******************************************************************
     * Note: Table functions cannot use:
     * - IF/ELSE statements (use CASE WHEN instead)
     * - DECLARE for variables (inline expressions only)
     * - DML statements (INSERT, UPDATE, DELETE)
     * - Exception handlers in the main body
     *
     * They CAN use:
     * - Complex SELECT statements
     * - JOINs, subqueries, CTEs
     * - Window functions
     * - CASE expressions for conditional logic
     *******************************************************************/

    /*******************************************************************
     * Main Logic with Inline Validation using CASE
     *******************************************************************/

    RETURN
        SELECT
            t.id AS record_id,
            c.name AS category_name,
            t.value AS metric_value,
            t.record_date,
            CASE
                WHEN t.value >= 1000 THEN 'HIGH'
                WHEN t.value >= 100 THEN 'MEDIUM'
                ELSE 'LOW'
            END AS status,
            ROW_NUMBER() OVER (
                PARTITION BY c.name
                ORDER BY t.value DESC
            ) AS row_rank
        FROM "<SCHEMA>"."TRANSACTIONS" t
        INNER JOIN "<SCHEMA>"."CATEGORIES" c
            ON t.category_id = c.id
        WHERE
            -- Date range validation with COALESCE for NULL handling
            t.record_date >= COALESCE(:iv_start_date, ADD_DAYS(CURRENT_DATE, -30))
            AND t.record_date <= COALESCE(:iv_end_date, CURRENT_DATE)
            -- Optional category filter using CASE
            AND (
                :iv_category = ''
                OR c.name = :iv_category
            )
        ORDER BY t.record_date DESC, t.value DESC
        LIMIT CASE
            WHEN :iv_limit <= 0 THEN 1000
            WHEN :iv_limit > 10000 THEN 10000
            ELSE :iv_limit
        END;

END;

/*
 * Example Usage:
 *
 * -- Basic call with date range
 * SELECT * FROM "<SCHEMA>"."<FUNCTION_NAME>_TABLE_UDF"(
 *     iv_start_date => '2025-01-01',
 *     iv_end_date   => '2025-12-31',
 *     iv_category   => '',
 *     iv_limit      => 100
 * );
 *
 * -- With category filter
 * SELECT * FROM "<SCHEMA>"."<FUNCTION_NAME>_TABLE_UDF"(
 *     iv_start_date => '2025-01-01',
 *     iv_end_date   => '2025-12-31',
 *     iv_category   => 'Electronics',
 *     iv_limit      => 500
 * );
 *
 * -- Using in JOIN
 * -- Note: record_id corresponds to TRANSACTIONS.id (see line 59)
 * SELECT
 *     u.user_name,
 *     f.category_name,
 *     f.metric_value
 * FROM "<SCHEMA>"."USERS" u
 * INNER JOIN "<SCHEMA>"."<FUNCTION_NAME>_TABLE_UDF"(
 *     '2025-01-01', '2025-12-31', '', 1000
 * ) f ON u.id = f.record_id  -- Join on user ID, not category_id
 * WHERE f.row_rank <= 10;
 *
 * -- Alternative: Join through TRANSACTIONS table for clarity
 * SELECT
 *     u.user_name,
 *     f.category_name,
 *     f.metric_value
 * FROM "<SCHEMA>"."USERS" u
 * INNER JOIN "<SCHEMA>"."TRANSACTIONS" t ON u.id = t.user_id
 * INNER JOIN "<SCHEMA>"."<FUNCTION_NAME>_TABLE_UDF"(
 *     '2025-01-01', '2025-12-31', '', 1000
 * ) f ON t.id = f.record_id
 * WHERE f.row_rank <= 10;
 *
 * -- Using in CTE
 * WITH ranked_data AS (
 *     SELECT * FROM "<SCHEMA>"."<FUNCTION_NAME>_TABLE_UDF"(
 *         iv_start_date => ADD_DAYS(CURRENT_DATE, -90),
 *         iv_end_date   => CURRENT_DATE,
 *         iv_category   => 'Sales',
 *         iv_limit      => 500
 *     )
 * )
 * SELECT
 *     category_name,
 *     SUM(metric_value) AS total_value,
 *     COUNT(*) AS record_count
 * FROM ranked_data
 * WHERE row_rank <= 5
 * GROUP BY category_name;
 */

/**
 * Alternative: Table Function with TYPE for Complex Return Structure
 */

-- First create the return type
-- CREATE TYPE "<SCHEMA>"."TT_METRICS_RESULT" AS TABLE (
--     record_id       INTEGER,
--     category_name   NVARCHAR(100),
--     metric_value    DECIMAL(15,2),
--     record_date     DATE,
--     status          NVARCHAR(20),
--     row_rank        INTEGER
-- );

-- Then use it in function
-- CREATE OR REPLACE FUNCTION "<SCHEMA>"."<FUNCTION_NAME>_WITH_TYPE"(
--     iv_start_date DATE,
--     iv_end_date   DATE
-- )
-- RETURNS "<SCHEMA>"."TT_METRICS_RESULT"
-- LANGUAGE SQLSCRIPT
-- READS SQL DATA
-- AS
-- BEGIN
--     RETURN SELECT ... ;
-- END;

```

### templates/scalar-function.sql

```sql
/**
 * Scalar User-Defined Function (Scalar UDF) Template
 *
 * Production-ready template for SQLScript scalar functions with:
 * - Type-safe return values
 * - NULL handling patterns
 * - Inline validation using CASE expressions
 * - Performance considerations
 *
 * Note: Scalar UDFs return a single value and can be used in
 *       SELECT lists, WHERE clauses, and other SQL expressions.
 *
 * @version 2.1.0
 * @last_updated 2025-12-27
 */

/**
 * Example 1: String Processing Function
 * Formats a full name from components with proper NULL handling
 */
CREATE OR REPLACE FUNCTION "<SCHEMA>"."FORMAT_FULL_NAME"
(
    iv_first_name   NVARCHAR(100),
    iv_middle_name  NVARCHAR(100),
    iv_last_name    NVARCHAR(100),
    iv_suffix       NVARCHAR(20) DEFAULT ''
)
RETURNS NVARCHAR(400)
LANGUAGE SQLSCRIPT
DETERMINISTIC              -- Same inputs always produce same output (enables caching)
SQL SECURITY INVOKER
AS
BEGIN
    /*******************************************************************
     * NULL Handling: Return NULL if required fields are NULL
     * Use COALESCE and NULLIF for safe string operations
     *******************************************************************/

    DECLARE lv_result NVARCHAR(400);

    -- Return NULL if both first and last name are NULL/empty
    IF COALESCE(TRIM(:iv_first_name), '') = '' AND COALESCE(TRIM(:iv_last_name), '') = '' THEN
        RETURN NULL;
    END IF;

    -- Build full name with proper spacing
    lv_result := TRIM(
        COALESCE(TRIM(:iv_first_name), '') ||
        CASE
            WHEN COALESCE(TRIM(:iv_middle_name), '') != ''
            THEN ' ' || TRIM(:iv_middle_name)
            ELSE ''
        END ||
        CASE
            WHEN COALESCE(TRIM(:iv_last_name), '') != ''
            THEN ' ' || TRIM(:iv_last_name)
            ELSE ''
        END ||
        CASE
            WHEN COALESCE(TRIM(:iv_suffix), '') != ''
            THEN ', ' || TRIM(:iv_suffix)
            ELSE ''
        END
    );

    RETURN :lv_result;

END;

/**
 * Example 2: Numeric Calculation Function
 * Calculates compound interest with validation
 */
CREATE OR REPLACE FUNCTION "<SCHEMA>"."CALCULATE_COMPOUND_INTEREST"
(
    iv_principal    DECIMAL(15,2),      -- Initial amount
    iv_rate         DECIMAL(5,4),       -- Annual interest rate (e.g., 0.0525 for 5.25%)
    iv_periods      INTEGER,            -- Number of compounding periods
    iv_compounds    INTEGER DEFAULT 12  -- Compounds per year (12 = monthly)
)
RETURNS DECIMAL(15,2)
LANGUAGE SQLSCRIPT
DETERMINISTIC
SQL SECURITY INVOKER
AS
BEGIN
    /*******************************************************************
     * Formula: A = P(1 + r/n)^(periods)
     * Where:
     *   A = Final amount
     *   P = Principal (iv_principal)
     *   r = Annual interest rate (iv_rate)
     *   n = Compounds per year (iv_compounds)
     *   periods = Total number of compounding periods (iv_periods = n*t)
     *            e.g., 60 for 5 years of monthly compounding (12*5)
     *                 or 60 for 60 months of monthly compounding
     *******************************************************************/

    DECLARE lv_result DECIMAL(15,2);

    -- Validation: Return NULL for invalid inputs
    IF :iv_principal IS NULL OR :iv_principal <= 0 THEN
        RETURN NULL;
    END IF;

    IF :iv_rate IS NULL OR :iv_rate < 0 THEN
        RETURN NULL;
    END IF;

    IF :iv_periods IS NULL OR :iv_periods < 0 THEN
        RETURN NULL;
    END IF;

    IF :iv_compounds IS NULL OR :iv_compounds <= 0 THEN
        RETURN NULL;
    END IF;

    -- Calculate compound interest
    lv_result := ROUND(
        :iv_principal * POWER(
            1 + (:iv_rate / :iv_compounds),
            :iv_periods
        ),
        2
    );

    RETURN :lv_result;

END;

/**
 * Example 3: Date Utility Function
 * Returns business days between two dates (excludes weekends)
 */
CREATE OR REPLACE FUNCTION "<SCHEMA>"."BUSINESS_DAYS_BETWEEN"
(
    iv_start_date   DATE,
    iv_end_date     DATE
)
RETURNS INTEGER
LANGUAGE SQLSCRIPT
DETERMINISTIC
SQL SECURITY INVOKER
AS
BEGIN
    DECLARE lv_days INTEGER := 0;
    DECLARE lv_current DATE;
    DECLARE lv_total_days INTEGER;

    -- Handle NULL inputs
    IF :iv_start_date IS NULL OR :iv_end_date IS NULL THEN
        RETURN NULL;
    END IF;

    -- Handle reversed dates
    IF :iv_start_date > :iv_end_date THEN
        RETURN -1 * "<SCHEMA>"."BUSINESS_DAYS_BETWEEN"(:iv_end_date, :iv_start_date);
    END IF;

    -- Calculate using set-based approach for better performance
    SELECT COUNT(*) INTO lv_days
    FROM (
        SELECT GENERATED_PERIOD_START AS day_date
        FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', :iv_start_date, ADD_DAYS(:iv_end_date, 1))
    )
    WHERE WEEKDAY(day_date) NOT IN (5, 6);  -- 5 = Saturday, 6 = Sunday

    RETURN :lv_days;

END;

/**
 * Example 4: Status/Enum Mapping Function
 * Converts status codes to human-readable labels
 */
CREATE OR REPLACE FUNCTION "<SCHEMA>"."GET_STATUS_LABEL"
(
    iv_status_code  NVARCHAR(10),
    iv_language     NVARCHAR(2) DEFAULT 'EN'
)
RETURNS NVARCHAR(100)
LANGUAGE SQLSCRIPT
DETERMINISTIC
SQL SECURITY INVOKER
AS
BEGIN
    /*******************************************************************
     * Use CASE expression for status mapping
     * Supports multiple languages
     *******************************************************************/

    RETURN
        CASE UPPER(:iv_language)
            WHEN 'EN' THEN
                CASE UPPER(:iv_status_code)
                    WHEN 'A' THEN 'Active'
                    WHEN 'I' THEN 'Inactive'
                    WHEN 'P' THEN 'Pending'
                    WHEN 'C' THEN 'Completed'
                    WHEN 'X' THEN 'Cancelled'
                    WHEN 'E' THEN 'Error'
                    ELSE 'Unknown'
                END
            WHEN 'DE' THEN
                CASE UPPER(:iv_status_code)
                    WHEN 'A' THEN 'Aktiv'
                    WHEN 'I' THEN 'Inaktiv'
                    WHEN 'P' THEN 'Ausstehend'
                    WHEN 'C' THEN 'Abgeschlossen'
                    WHEN 'X' THEN 'Storniert'
                    WHEN 'E' THEN 'Fehler'
                    ELSE 'Unbekannt'
                END
            ELSE
                CASE UPPER(:iv_status_code)
                    WHEN 'A' THEN 'Active'
                    WHEN 'I' THEN 'Inactive'
                    WHEN 'P' THEN 'Pending'
                    WHEN 'C' THEN 'Completed'
                    WHEN 'X' THEN 'Cancelled'
                    WHEN 'E' THEN 'Error'
                    ELSE 'Unknown'
                END
        END;

END;

/*
 * Example Usage:
 *
 * -- Format name
 * SELECT "<SCHEMA>"."FORMAT_FULL_NAME"('John', 'Q', 'Public', 'Jr.') FROM DUMMY;
 * -- Result: 'John Q Public, Jr.'
 *
 * -- Calculate interest
 * SELECT "<SCHEMA>"."CALCULATE_COMPOUND_INTEREST"(10000, 0.05, 60, 12) FROM DUMMY;
 * -- Result: 12833.59 (5% for 60 months compounded monthly)
 *
 * -- Business days
 * SELECT "<SCHEMA>"."BUSINESS_DAYS_BETWEEN"('2025-01-01', '2025-01-31') FROM DUMMY;
 * -- Result: 23
 *
 * -- Status label with language
 * SELECT "<SCHEMA>"."GET_STATUS_LABEL"('P', 'DE') FROM DUMMY;
 * -- Result: 'Ausstehend'
 *
 * -- Use in SELECT
 * SELECT
 *     order_id,
 *     "<SCHEMA>"."GET_STATUS_LABEL"(status, 'EN') AS status_text,
 *     "<SCHEMA>"."FORMAT_FULL_NAME"(first_name, NULL, last_name, '') AS customer_name
 * FROM "<SCHEMA>"."ORDERS";
 */

```

### templates/amdp-procedure.sql

```sql
/**
 * AMDP Procedure SQLScript Template
 *
 * This file contains the SQLScript implementation that goes inside
 * an AMDP method. Use in conjunction with amdp-class.abap template.
 *
 * Key differences from standalone SQLScript:
 * - No CREATE PROCEDURE statement (defined by ABAP method signature)
 * - Parameters mapped from ABAP types
 * - COMMIT/ROLLBACK not allowed
 * - Transaction managed by ABAP layer
 * - Table names must be declared in USING clause
 *
 * @version 2.1.0
 * @last_updated 2025-12-27
 */

/*******************************************************************
 * AMDP Method Signature in ABAP:
 *
 * METHOD process_order
 *   BY DATABASE PROCEDURE
 *   FOR HDB
 *   LANGUAGE SQLSCRIPT
 *   OPTIONS READ-ONLY                    -- Or omit for read-write
 *   USING zorders zorder_items zinventory.
 *
 * Parameters (from ABAP method signature):
 *   iv_order_id     TYPE i              -- Input: Order ID
 *   iv_action       TYPE string         -- Input: Action (APPROVE/REJECT/SHIP)
 *   et_result       TYPE tt_order_line  -- Output: Processed line items
 *   ev_status       TYPE string         -- Output: New order status
 *   ev_message      TYPE string         -- Output: Status message
 *******************************************************************/

/*******************************************************************
 * SQLScript Implementation (Method Body)
 *******************************************************************/

-- Declare custom conditions for AMDP error handling
DECLARE order_not_found    CONDITION FOR SQL_ERROR_CODE 10001;
DECLARE invalid_action     CONDITION FOR SQL_ERROR_CODE 10002;
DECLARE insufficient_stock CONDITION FOR SQL_ERROR_CODE 10003;
DECLARE already_processed  CONDITION FOR SQL_ERROR_CODE 10004;

-- Local variables
DECLARE lv_current_status NVARCHAR(20);
DECLARE lv_product_id     NVARCHAR(40);
DECLARE lv_total_qty      INTEGER;
DECLARE lv_available_qty  INTEGER;

/*******************************************************************
 * Exception Handlers
 * Note: In AMDP, unhandled exceptions propagate to ABAP as cx_amdp_error
 *******************************************************************/

DECLARE EXIT HANDLER FOR order_not_found
BEGIN
    ev_status := 'ERROR';
    ev_message := 'Order ' || :iv_order_id || ' not found';
END;

DECLARE EXIT HANDLER FOR invalid_action
BEGIN
    ev_status := 'ERROR';
    ev_message := 'Invalid action: ' || :iv_action || '. Valid: APPROVE, REJECT, SHIP';
END;

DECLARE EXIT HANDLER FOR insufficient_stock
BEGIN
    ev_status := 'ERROR';
    ev_message := 'Insufficient stock. Required: ' || :lv_total_qty ||
                  ', Available: ' || :lv_available_qty;
END;

DECLARE EXIT HANDLER FOR already_processed
BEGIN
    ev_status := 'ERROR';
    ev_message := 'Order already processed. Current status: ' || :lv_current_status;
END;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ev_status := 'ERROR';
    ev_message := 'SQL Error [' || ::SQL_ERROR_CODE || ']: ' || ::SQL_ERROR_MESSAGE;
END;

/*******************************************************************
 * Initialize Output
 *******************************************************************/
ev_status := '';
ev_message := '';

/*******************************************************************
 * Input Validation
 *******************************************************************/

-- Validate order exists
SELECT status INTO lv_current_status
FROM zorders
WHERE order_id = :iv_order_id;

IF :lv_current_status IS NULL THEN
    SIGNAL order_not_found;
END IF;

-- Validate action
IF UPPER(:iv_action) NOT IN ('APPROVE', 'REJECT', 'SHIP') THEN
    SIGNAL invalid_action;
END IF;

-- Check if already in final state
IF :lv_current_status IN ('SHIPPED', 'CANCELLED', 'REJECTED') THEN
    SIGNAL already_processed;
END IF;

/*******************************************************************
 * Business Logic by Action
 *******************************************************************/

CASE UPPER(:iv_action)

    WHEN 'APPROVE' THEN
        -- Can only approve PENDING orders
        IF :lv_current_status != 'PENDING' THEN
            ev_status := 'ERROR';
            ev_message := 'Can only approve PENDING orders';
            RETURN;
        END IF;

        -- Update order status
        UPDATE zorders
        SET status = 'APPROVED',
            approved_at = CURRENT_TIMESTAMP
        WHERE order_id = :iv_order_id;

        ev_status := 'APPROVED';
        ev_message := 'Order ' || :iv_order_id || ' approved successfully';

    WHEN 'REJECT' THEN
        -- Can reject PENDING or APPROVED orders
        IF :lv_current_status NOT IN ('PENDING', 'APPROVED') THEN
            ev_status := 'ERROR';
            ev_message := 'Can only reject PENDING or APPROVED orders';
            RETURN;
        END IF;

        -- Update order status
        UPDATE zorders
        SET status = 'REJECTED',
            rejected_at = CURRENT_TIMESTAMP
        WHERE order_id = :iv_order_id;

        ev_status := 'REJECTED';
        ev_message := 'Order ' || :iv_order_id || ' rejected';

    WHEN 'SHIP' THEN
        -- Can only ship APPROVED orders
        IF :lv_current_status != 'APPROVED' THEN
            ev_status := 'ERROR';
            ev_message := 'Can only ship APPROVED orders';
            RETURN;
        END IF;

        -- Check inventory per product (not total across all products)
        -- This ensures each product has sufficient stock individually
        DECLARE lt_insufficient TABLE (
            product_id      NVARCHAR(40),
            required_qty    INTEGER,
            available_qty   INTEGER
        );

        lt_insufficient = SELECT items.product_id,
                                 items.quantity AS required_qty,
                                 inv.available_qty
                          FROM zorder_items items
                          INNER JOIN zinventory inv
                              ON inv.product_id = items.product_id
                          WHERE items.order_id = :iv_order_id
                            AND inv.available_qty < items.quantity;

        IF NOT IS_EMPTY(:lt_insufficient) THEN
            -- Get first insufficient product for error message
            SELECT product_id, required_qty, available_qty
            INTO lv_product_id, lv_total_qty, lv_available_qty
            FROM :lt_insufficient
            LIMIT 1;
            SIGNAL insufficient_stock;
        END IF;

        -- Deduct inventory
        UPDATE zinventory
        SET available_qty = available_qty - (
            SELECT quantity
            FROM zorder_items
            WHERE order_id = :iv_order_id
              AND product_id = zinventory.product_id
        )
        WHERE product_id IN (
            SELECT product_id
            FROM zorder_items
            WHERE order_id = :iv_order_id
        );

        -- Update order status
        UPDATE zorders
        SET status = 'SHIPPED',
            shipped_at = CURRENT_TIMESTAMP
        WHERE order_id = :iv_order_id;

        ev_status := 'SHIPPED';
        ev_message := 'Order ' || :iv_order_id || ' shipped successfully';

END CASE;

/*******************************************************************
 * Return Order Line Items
 *******************************************************************/
et_result = SELECT
                items.item_id,
                items.product_id,
                items.quantity,
                items.unit_price,
                items.quantity * items.unit_price AS total_price,
                orders.status
            FROM zorder_items items
            INNER JOIN zorders orders
                ON items.order_id = orders.order_id
            WHERE items.order_id = :iv_order_id
            ORDER BY items.item_id;

/*
 * ABAP Type Mapping Reference:
 *
 * ABAP Type          | SQLScript Type
 * -------------------|------------------
 * i                  | INTEGER
 * int8               | BIGINT
 * p (packed)         | DECIMAL
 * f                  | DOUBLE
 * decfloat16/34      | DECIMAL
 * d                  | DATE (as YYYYMMDD string)
 * t                  | TIME (as HHMMSS string)
 * utclong            | TIMESTAMP
 * c, string          | NVARCHAR
 * x, xstring         | VARBINARY
 * abap_bool          | TINYINT
 *
 * Note: ABAP date (d) is passed as string 'YYYYMMDD'
 * Convert with: TO_DATE(:iv_date, 'YYYYMMDD')
 */

```

### templates/cursor-iteration.sql

```sql
/**
 * Cursor Iteration Pattern Templates
 *
 * Production-ready templates for SQLScript cursor operations with:
 * - Proper open/fetch/close lifecycle
 * - Error handling for cursor states
 * - FOR loop alternative (simpler syntax)
 * - Performance considerations
 *
 * WARNING: Cursors bypass the database optimizer and process rows sequentially.
 * Use set-based operations whenever possible. Cursors are appropriate for:
 * - Complex row-by-row validation that cannot be expressed in SQL
 * - Calling external procedures for each row
 * - Scenarios requiring sequential processing order
 *
 * @version 2.1.0
 * @last_updated 2025-12-27
 */

/**
 * Pattern 1: Classic Cursor with FETCH INTO
 * Use when you need explicit control over cursor lifecycle
 */
CREATE OR REPLACE PROCEDURE "<SCHEMA>"."CURSOR_CLASSIC_PATTERN"
(
    IN  iv_category     NVARCHAR(50),
    OUT ev_processed    INTEGER,
    OUT ev_errors       INTEGER,
    OUT et_results      TABLE (
        id              INTEGER,
        status          NVARCHAR(20),
        message         NVARCHAR(200)
    )
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    /*******************************************************************
     * Variable Declarations
     *******************************************************************/
    DECLARE lv_id           INTEGER;
    DECLARE lv_name         NVARCHAR(100);
    DECLARE lv_amount       DECIMAL(15,2);
    DECLARE lv_status       NVARCHAR(20);
    DECLARE lv_message      NVARCHAR(200);
    DECLARE lv_row_count    INTEGER := 0;
    DECLARE lv_error_count  INTEGER := 0;

    -- Temporary table for results
    DECLARE lt_results TABLE (
        id      INTEGER,
        status  NVARCHAR(20),
        message NVARCHAR(200)
    );

    /*******************************************************************
     * Cursor Declaration
     * Can include parameters, joins, and complex conditions
     *******************************************************************/
    DECLARE CURSOR cur_items FOR
        SELECT
            id,
            name,
            amount
        FROM "<SCHEMA>"."ITEMS"
        WHERE category = :iv_category
          AND status = 'PENDING'
        ORDER BY id
        FOR UPDATE;  -- Lock rows if updating

    /*******************************************************************
     * Error Handler for Cursor Operations
     *******************************************************************/
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Always close cursor on error
        IF cur_items::ISOPEN THEN
            CLOSE cur_items;
        END IF;

        ev_processed := :lv_row_count;
        ev_errors := :lv_error_count + 1;
        et_results = SELECT * FROM :lt_results;
    END;

    /*******************************************************************
     * Cursor Processing Pattern
     *******************************************************************/

    -- Open cursor
    OPEN cur_items;

    -- First fetch
    FETCH cur_items INTO lv_id, lv_name, lv_amount;

    -- Process loop using ::NOTFOUND
    WHILE NOT cur_items::NOTFOUND DO

        -- Initialize row status
        lv_status := 'SUCCESS';
        lv_message := '';

        -- Process each row (example: validation and update)
        BEGIN
            -- Row-level error handler
            DECLARE EXIT HANDLER FOR SQLEXCEPTION
            BEGIN
                lv_status := 'ERROR';
                lv_message := 'Error [' || ::SQL_ERROR_CODE || ']: ' || ::SQL_ERROR_MESSAGE;
                lv_error_count := :lv_error_count + 1;
            END;

            -- Business logic for each row
            IF :lv_amount <= 0 THEN
                lv_status := 'SKIPPED';
                lv_message := 'Invalid amount: ' || :lv_amount;
            ELSE
                -- Example: Update the row
                UPDATE "<SCHEMA>"."ITEMS"
                SET status = 'PROCESSED',
                    processed_at = CURRENT_TIMESTAMP
                WHERE CURRENT OF cur_items;  -- Update current cursor row

                lv_message := 'Processed successfully. Amount: ' || :lv_amount;
            END IF;
        END;

        -- Record result
        INSERT INTO :lt_results VALUES (:lv_id, :lv_status, :lv_message);
        lv_row_count := :lv_row_count + 1;

        -- Fetch next row
        FETCH cur_items INTO lv_id, lv_name, lv_amount;

    END WHILE;

    -- Close cursor (important!)
    CLOSE cur_items;

    /*******************************************************************
     * Output Results
     *******************************************************************/
    ev_processed := :lv_row_count;
    ev_errors := :lv_error_count;
    et_results = SELECT * FROM :lt_results;

END;

/**
 * Pattern 2: FOR Loop Cursor (Simplified Syntax)
 * Automatically handles OPEN, FETCH, CLOSE
 * Recommended for most cursor use cases
 */
CREATE OR REPLACE PROCEDURE "<SCHEMA>"."CURSOR_FOR_LOOP_PATTERN"
(
    IN  iv_department   NVARCHAR(50),
    OUT ev_total        DECIMAL(15,2),
    OUT ev_count        INTEGER
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA
AS
BEGIN
    /*******************************************************************
     * Local Variables
     *******************************************************************/
    DECLARE lv_running_total DECIMAL(15,2) := 0;
    DECLARE lv_record_count  INTEGER := 0;

    /*******************************************************************
     * FOR Loop with Inline Cursor
     * Syntax: FOR <row_var> AS <cursor_name> [(<params>)] DO
     *
     * Benefits:
     * - No explicit OPEN/FETCH/CLOSE
     * - Automatic cleanup on exit
     * - Cleaner syntax
     *******************************************************************/

    FOR row AS cur_employees (
        SELECT
            employee_id,
            name,
            salary,
            bonus
        FROM "<SCHEMA>"."EMPLOYEES"
        WHERE department = :iv_department
          AND active = 1
        ORDER BY employee_id
    ) DO
        -- Access columns using row.<column_name>
        lv_running_total := :lv_running_total + row.salary + COALESCE(row.bonus, 0);
        lv_record_count := :lv_record_count + 1;

        -- Can perform DML operations
        -- UPDATE "<SCHEMA>"."EMPLOYEES"
        -- SET last_processed = CURRENT_TIMESTAMP
        -- WHERE employee_id = row.employee_id;

    END FOR;

    /*******************************************************************
     * Output
     *******************************************************************/
    ev_total := :lv_running_total;
    ev_count := :lv_record_count;

END;

/**
 * Pattern 3: Nested Cursors
 * Use sparingly - performance impact is multiplicative
 */
CREATE OR REPLACE PROCEDURE "<SCHEMA>"."CURSOR_NESTED_PATTERN"
(
    OUT et_summary TABLE (
        parent_id       INTEGER,
        parent_name     NVARCHAR(100),
        child_count     INTEGER,
        total_value     DECIMAL(15,2)
    )
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA
AS
BEGIN
    DECLARE lt_summary TABLE (
        parent_id   INTEGER,
        parent_name NVARCHAR(100),
        child_count INTEGER,
        total_value DECIMAL(15,2)
    );

    /*******************************************************************
     * Outer Cursor: Parents
     *******************************************************************/
    FOR parent_row AS cur_parents (
        SELECT id, name
        FROM "<SCHEMA>"."PARENT_TABLE"
        WHERE active = 1
        ORDER BY id
    ) DO

        DECLARE lv_child_count  INTEGER := 0;
        DECLARE lv_child_total  DECIMAL(15,2) := 0;

        /***************************************************************
         * Inner Cursor: Children of Current Parent
         ***************************************************************/
        FOR child_row AS cur_children (
            SELECT value
            FROM "<SCHEMA>"."CHILD_TABLE"
            WHERE parent_id = parent_row.id
        ) DO
            lv_child_count := :lv_child_count + 1;
            lv_child_total := :lv_child_total + child_row.value;
        END FOR;

        -- Record parent summary
        INSERT INTO :lt_summary VALUES (
            parent_row.id,
            parent_row.name,
            :lv_child_count,
            :lv_child_total
        );

    END FOR;

    et_summary = SELECT * FROM :lt_summary;

END;

/**
 * Pattern 4: Set-Based Alternative (PREFERRED)
 * Same result as nested cursors but using JOINs and GROUP BY
 */
CREATE OR REPLACE PROCEDURE "<SCHEMA>"."SET_BASED_ALTERNATIVE"
(
    OUT et_summary TABLE (
        parent_id       INTEGER,
        parent_name     NVARCHAR(100),
        child_count     INTEGER,
        total_value     DECIMAL(15,2)
    )
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA
AS
BEGIN
    /*******************************************************************
     * Set-Based Approach - MUCH faster than nested cursors
     * Uses JOIN and GROUP BY instead of row-by-row processing
     *******************************************************************/

    et_summary = SELECT
                     p.id AS parent_id,
                     p.name AS parent_name,
                     COUNT(c.id) AS child_count,
                     COALESCE(SUM(c.value), 0) AS total_value
                 FROM "<SCHEMA>"."PARENT_TABLE" p
                 LEFT JOIN "<SCHEMA>"."CHILD_TABLE" c
                     ON p.id = c.parent_id
                 WHERE p.active = 1
                 GROUP BY p.id, p.name
                 ORDER BY p.id;

END;

/*
 * Cursor Attribute Reference:
 *
 * cursor_name::ISOPEN    - TRUE if cursor is open
 * cursor_name::NOTFOUND  - TRUE after FETCH finds no more rows
 * cursor_name::ROWCOUNT  - Number of rows fetched so far
 *
 * Performance Guidelines:
 * 1. Always prefer set-based operations over cursors
 * 2. If cursor is necessary, use FOR loop syntax
 * 3. Limit cursor result set with WHERE conditions
 * 4. Index columns used in cursor WHERE clause
 * 5. Avoid nested cursors (exponential performance impact)
 * 6. Process in batches for large datasets
 */

```

### templates/bulk-operations.sql

```sql
/**
 * Bulk Operations Pattern Templates
 *
 * Production-ready templates for high-performance SQLScript bulk operations:
 * - Set-based INSERT, UPDATE, DELETE
 * - MERGE (UPSERT) operations
 * - Batch processing with chunking
 * - Transaction management patterns
 * - Performance optimization techniques
 *
 * @version 2.1.0
 * @last_updated 2025-12-27
 */

/**
 * Pattern 1: Bulk INSERT from Table Variable
 * Inserts multiple rows in a single operation
 */
CREATE OR REPLACE PROCEDURE "<SCHEMA>"."BULK_INSERT_PATTERN"
(
    IN  it_data         TABLE (
        id              INTEGER,
        name            NVARCHAR(100),
        category        NVARCHAR(50),
        amount          DECIMAL(15,2)
    ),
    OUT ev_inserted     INTEGER,
    OUT ev_message      NVARCHAR(500)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    DECLARE lv_count INTEGER := 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ev_inserted := 0;
        ev_message := 'Insert failed [' || ::SQL_ERROR_CODE || ']: ' || ::SQL_ERROR_MESSAGE;
    END;

    /*******************************************************************
     * Get input count
     *******************************************************************/
    SELECT COUNT(*) INTO lv_count FROM :it_data;

    IF :lv_count = 0 THEN
        ev_inserted := 0;
        ev_message := 'No data to insert';
        RETURN;
    END IF;

    /*******************************************************************
     * Bulk INSERT - Single statement for all rows
     * Much faster than row-by-row INSERT in a loop
     *******************************************************************/
    INSERT INTO "<SCHEMA>"."TARGET_TABLE" (
        id, name, category, amount, created_at
    )
    SELECT
        id,
        name,
        category,
        amount,
        CURRENT_TIMESTAMP
    FROM :it_data;

    ev_inserted := ::ROWCOUNT;
    ev_message := 'Successfully inserted ' || :ev_inserted || ' records';

END;

/**
 * Pattern 2: Bulk UPDATE from Table Variable
 * Updates multiple rows in a single operation
 */
CREATE OR REPLACE PROCEDURE "<SCHEMA>"."BULK_UPDATE_PATTERN"
(
    IN  it_updates      TABLE (
        id              INTEGER,
        new_amount      DECIMAL(15,2),
        new_status      NVARCHAR(20)
    ),
    OUT ev_updated      INTEGER,
    OUT ev_message      NVARCHAR(500)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    DECLARE lv_count INTEGER := 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ev_updated := 0;
        ev_message := 'Update failed [' || ::SQL_ERROR_CODE || ']: ' || ::SQL_ERROR_MESSAGE;
    END;

    SELECT COUNT(*) INTO lv_count FROM :it_updates;

    IF :lv_count = 0 THEN
        ev_updated := 0;
        ev_message := 'No data to update';
        RETURN;
    END IF;

    /*******************************************************************
     * Bulk UPDATE using JOIN pattern
     * Updates target table based on matching source records
     *******************************************************************/
    UPDATE "<SCHEMA>"."TARGET_TABLE" AS t
    SET t.amount = u.new_amount,
        t.status = u.new_status,
        t.modified_at = CURRENT_TIMESTAMP
    FROM :it_updates AS u
    WHERE t.id = u.id;

    ev_updated := ::ROWCOUNT;
    ev_message := 'Successfully updated ' || :ev_updated || ' records';

END;

/**
 * Pattern 3: MERGE (UPSERT) Operation
 * Inserts new records, updates existing ones
 */
CREATE OR REPLACE PROCEDURE "<SCHEMA>"."MERGE_PATTERN"
(
    IN  it_data         TABLE (
        id              INTEGER,
        name            NVARCHAR(100),
        amount          DECIMAL(15,2)
    ),
    OUT ev_inserted     INTEGER,
    OUT ev_updated      INTEGER,
    OUT ev_message      NVARCHAR(500)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    DECLARE lv_before_count INTEGER;
    DECLARE lv_after_count INTEGER;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ev_inserted := 0;
        ev_updated := 0;
        ev_message := 'Merge failed [' || ::SQL_ERROR_CODE || ']: ' || ::SQL_ERROR_MESSAGE;
    END;

    -- Get count before merge
    SELECT COUNT(*) INTO lv_before_count FROM "<SCHEMA>"."TARGET_TABLE";

    /*******************************************************************
     * MERGE Statement (UPSERT)
     * WHEN MATCHED: Update existing records
     * WHEN NOT MATCHED: Insert new records
     *******************************************************************/
    MERGE INTO "<SCHEMA>"."TARGET_TABLE" AS target
    USING :it_data AS source
    ON target.id = source.id
    WHEN MATCHED THEN
        UPDATE SET
            target.name = source.name,
            target.amount = source.amount,
            target.modified_at = CURRENT_TIMESTAMP
    WHEN NOT MATCHED THEN
        INSERT (id, name, amount, created_at)
        VALUES (source.id, source.name, source.amount, CURRENT_TIMESTAMP);

    -- Capture ROWCOUNT immediately after MERGE (before any other statement)
    DECLARE lv_merge_count INTEGER := ::ROWCOUNT;

    -- Calculate inserted vs updated
    SELECT COUNT(*) INTO lv_after_count FROM "<SCHEMA>"."TARGET_TABLE";

    ev_inserted := :lv_after_count - :lv_before_count;
    ev_updated := :lv_merge_count - :ev_inserted;
    ev_message := 'Merge complete. Inserted: ' || :ev_inserted || ', Updated: ' || :ev_updated;

END;

/**
 * Pattern 4: Batch Processing with Chunking
 * Processes large datasets in manageable chunks
 * Prevents memory issues and allows progress tracking
 */
CREATE OR REPLACE PROCEDURE "<SCHEMA>"."BATCH_PROCESSING_PATTERN"
(
    IN  iv_batch_size   INTEGER DEFAULT 1000,   -- Records per batch
    IN  iv_max_batches  INTEGER DEFAULT 100,    -- Max iterations
    OUT ev_total        INTEGER,
    OUT ev_batches      INTEGER,
    OUT ev_message      NVARCHAR(500)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    DECLARE lv_offset       INTEGER := 0;
    DECLARE lv_batch_count  INTEGER := 0;
    DECLARE lv_total_count  INTEGER := 0;
    DECLARE lv_processed    INTEGER := 0;

    -- Table variable to hold current batch
    DECLARE lt_batch TABLE (
        id      INTEGER,
        name    NVARCHAR(100),
        amount  DECIMAL(15,2)
    );

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ev_total := :lv_total_count;
        ev_batches := :lv_batch_count;
        ev_message := 'Batch processing failed at batch ' || :lv_batch_count ||
                      ' [' || ::SQL_ERROR_CODE || ']: ' || ::SQL_ERROR_MESSAGE;
    END;

    -- Get total records to process
    SELECT COUNT(*) INTO lv_total_count
    FROM "<SCHEMA>"."SOURCE_TABLE"
    WHERE status = 'PENDING';

    IF :lv_total_count = 0 THEN
        ev_total := 0;
        ev_batches := 0;
        ev_message := 'No records to process';
        RETURN;
    END IF;

    /*******************************************************************
     * Batch Processing Loop
     *******************************************************************/
    WHILE :lv_processed < :lv_total_count AND :lv_batch_count < :iv_max_batches DO

        -- Fetch next batch
        lt_batch = SELECT id, name, amount
                   FROM "<SCHEMA>"."SOURCE_TABLE"
                   WHERE status = 'PENDING'
                   ORDER BY id
                   LIMIT :iv_batch_size OFFSET :lv_offset;

        -- Check if batch is empty
        IF IS_EMPTY(:lt_batch) THEN
            -- No more records
            BREAK;
        END IF;

        -- Process batch: Mark as processing
        UPDATE "<SCHEMA>"."SOURCE_TABLE"
        SET status = 'PROCESSING',
            batch_id = :lv_batch_count
        WHERE id IN (SELECT id FROM :lt_batch);

        -- Process batch: Apply business logic
        UPDATE "<SCHEMA>"."SOURCE_TABLE"
        SET processed_amount = amount * 1.1,  -- Example transformation
            status = 'COMPLETED',
            processed_at = CURRENT_TIMESTAMP
        WHERE id IN (SELECT id FROM :lt_batch);

        -- Update counters
        lv_processed := :lv_processed + CARDINALITY(:lt_batch);
        lv_batch_count := :lv_batch_count + 1;
        lv_offset := :lv_offset + :iv_batch_size;

    END WHILE;

    /*******************************************************************
     * Results
     *******************************************************************/
    ev_total := :lv_processed;
    ev_batches := :lv_batch_count;
    ev_message := 'Processed ' || :lv_processed || ' records in ' ||
                  :lv_batch_count || ' batches';

END;

/**
 * Pattern 5: Bulk DELETE with Safety Checks
 * Deletes multiple records with validation and logging
 */
CREATE OR REPLACE PROCEDURE "<SCHEMA>"."BULK_DELETE_PATTERN"
(
    IN  it_ids          TABLE (id INTEGER),
    IN  iv_hard_delete  INTEGER DEFAULT 0,      -- 0 = soft delete, 1 = hard delete
    OUT ev_deleted      INTEGER,
    OUT ev_message      NVARCHAR(500)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    DECLARE lv_count INTEGER := 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ev_deleted := 0;
        ev_message := 'Delete failed [' || ::SQL_ERROR_CODE || ']: ' || ::SQL_ERROR_MESSAGE;
    END;

    SELECT COUNT(*) INTO lv_count FROM :it_ids;

    IF :lv_count = 0 THEN
        ev_deleted := 0;
        ev_message := 'No IDs provided for deletion';
        RETURN;
    END IF;

    /*******************************************************************
     * Safety Check: Verify records exist and can be deleted
     *******************************************************************/
    DECLARE lt_valid_ids TABLE (id INTEGER);

    lt_valid_ids = SELECT t.id
                   FROM "<SCHEMA>"."TARGET_TABLE" t
                   INNER JOIN :it_ids i ON t.id = i.id
                   WHERE t.status NOT IN ('LOCKED', 'ARCHIVED');

    SELECT COUNT(*) INTO lv_count FROM :lt_valid_ids;

    IF :lv_count = 0 THEN
        ev_deleted := 0;
        ev_message := 'No valid records found for deletion';
        RETURN;
    END IF;

    /*******************************************************************
     * Archive before delete (audit trail)
     *******************************************************************/
    INSERT INTO "<SCHEMA>"."TARGET_TABLE_ARCHIVE" (
        id, name, amount, status, deleted_at
    )
    SELECT
        t.id, t.name, t.amount, t.status, CURRENT_TIMESTAMP
    FROM "<SCHEMA>"."TARGET_TABLE" t
    INNER JOIN :lt_valid_ids v ON t.id = v.id;

    /*******************************************************************
     * Delete Records
     *******************************************************************/
    IF :iv_hard_delete = 1 THEN
        -- Hard delete: Remove records completely
        DELETE FROM "<SCHEMA>"."TARGET_TABLE"
        WHERE id IN (SELECT id FROM :lt_valid_ids);
    ELSE
        -- Soft delete: Mark as deleted
        UPDATE "<SCHEMA>"."TARGET_TABLE"
        SET status = 'DELETED',
            deleted_at = CURRENT_TIMESTAMP
        WHERE id IN (SELECT id FROM :lt_valid_ids);
    END IF;

    ev_deleted := ::ROWCOUNT;
    ev_message := CASE :iv_hard_delete
                      WHEN 1 THEN 'Hard deleted '
                      ELSE 'Soft deleted '
                  END || :ev_deleted || ' records';

END;

/*
 * Performance Guidelines for Bulk Operations:
 *
 * 1. ALWAYS prefer set-based operations over cursors
 * 2. Use MERGE instead of separate INSERT/UPDATE logic
 * 3. Process in batches for datasets > 10,000 rows
 * 4. Index columns used in WHERE and JOIN conditions
 * 5. Use table variables to pass data between operations
 * 6. Avoid dynamic SQL in bulk operations
 * 7. Consider partitioning for very large tables
 * 8. Monitor memory usage with large result sets
 *
 * Transaction Guidelines:
 * - DDL operations auto-commit
 * - Explicit COMMIT only allowed in top-level procedures
 * - COMMIT not allowed in AMDP
 * - Use autonomous transactions for logging
 */

```