Now liveThe Skillselion MCP - thousands of ranked skills, loaded into your agent mid-task. No install.Get it →
secondsky avatar

Sap Sqlscript

secondsky/sap-skills

  • 341 installs
  • 399 repo stars
  • Updated August 4, 2026
  • secondsky/sap-skills

Author, debug, and optimize SAP HANA SQLScript procedures, functions, and table types for enterprise SAP data models and reporting pipelines.

About

Guides creation and refinement of SAP HANA SQLScript for enterprise workloads, covering procedures, functions, table types, cursors, and performance patterns inside secondsky/sap-skills.

  • HANA SQLScript syntax and patterns
  • Stored procedures and functions
  • Table types and cursors
  • Performance tuning for SAP workloads
  • Enterprise SAP data modeling

Sap Sqlscript by the numbers

  • 341 all-time installs (skills.sh)
  • +34 installs in the week ending Jul 27, 2026 (Skillselion tracking)
  • Ranked #160 of 911 Databases skills by installs in the Skillselion catalog
  • Data as of Aug 5, 2026 (Skillselion catalog sync)
npx skills add https://github.com/secondsky/sap-skills --skill sap-sqlscript

Add your badge

Show developers this skill is listed on Skillselion. Paste this into your README.

Listed on Skillselion
Installs341
repo stars★ 399
Last updatedAugust 4, 2026
Repositorysecondsky/sap-skills ↗

What it does

Author, debug, and optimize SAP HANA SQLScript procedures, functions, and table types for enterprise SAP data models and reporting pipelines.

Files

SKILL.mdMarkdownGitHub ↗

SAP SQLScript Development Guide

When to Use This Skill

Use this skill when writing SQLScript procedures, anonymous blocks, table/scalar functions, AMDP methods, exception handlers, cursor logic, bulk operations, or HANA performance-sensitive database logic that should run close to the data.

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

TypeDescriptionExecution
DeclarativePure SQL sequencesConverted to data flow graphs, processed in parallel
ImperativeControl structures (IF, WHILE, FOR)Processed sequentially, prevents parallel execution

---

Table of Contents

---

Container Types

1. Anonymous Blocks

Single-use logic not stored in the database. Useful for testing and ad-hoc execution.

DO [(<parameter_clause>)]
BEGIN [SEQUENTIAL EXECUTION]
  <body>
END;

Example:

DO
BEGIN
  DECLARE lv_count INTEGER;
  SELECT COUNT(*) INTO lv_count FROM "MYTABLE";
  SELECT :lv_count AS record_count FROM DUMMY;
END;

2. Stored Procedures

Reusable database objects with input/output parameters.

CREATE [OR REPLACE] PROCEDURE <procedure_name>
  (
    [IN <param> <datatype>],
    [OUT <param> <datatype>],
    [INOUT <param> <datatype>]
  )
  LANGUAGE SQLSCRIPT
  [SQL SECURITY {DEFINER | INVOKER}]
  [DEFAULT SCHEMA <schema_name>]
  [READS SQL DATA | READS SQL DATA WITH RESULT VIEW <view_name>]
AS
BEGIN
  <procedure_body>
END;

3. User-Defined Functions

Scalar UDF - Returns single value:

CREATE FUNCTION <function_name> (<input_parameters>)
RETURNS <scalar_type>
LANGUAGE SQLSCRIPT
AS
BEGIN
  <function_body>
  RETURN <value>;
END;

Table UDF - Returns table (read-only):

CREATE FUNCTION <function_name> (<input_parameters>)
RETURNS TABLE (<column_definitions>)
LANGUAGE SQLSCRIPT
READS SQL DATA
AS
BEGIN
  RETURN SELECT ... FROM ...;
END;

---

Data Types

SQLScript supports comprehensive data types for different use cases. See references/data-types.md for complete documentation including:

  • Numeric types (TINYINT, INTEGER, DECIMAL, etc.)
  • Character types (VARCHAR, NVARCHAR, CLOB, etc.)
  • Date/Time types (DATE, TIME, TIMESTAMP, SECONDDATE)
  • Binary types (VARBINARY, BLOB)
  • Type conversion functions (CAST, TO_ functions)
  • NULL handling patterns

---

Variable Declaration

Scalar Variables

DECLARE <variable_name> <datatype> [:= <initial_value>];

-- 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:

lt_result = SELECT * FROM "MYTABLE" WHERE status = 'A';

Explicit declaration:

DECLARE lt_data TABLE (
  id INTEGER,
  name NVARCHAR(100),
  amount DECIMAL(15,2)
);

Using TABLE LIKE:

DECLARE lt_copy TABLE LIKE :lt_original;

Arrays

DECLARE arr INTEGER ARRAY := ARRAY(1, 2, 3, 4, 5);
-- Access: arr[1], arr[2], etc. (1-based index)
-- Note: Arrays cannot be returned from procedures

---

Control Structures

IF-ELSE Statement

IF <condition1> THEN
  <statements>
[ELSEIF <condition2> THEN
  <statements>]
[ELSE
  <statements>]
END IF;

Comparison Operators:

OperatorMeaning
=Equal to
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal
!=, <>Not equal
Important: IF-ELSE cannot be used within SELECT statements. Use CASE WHEN instead.

WHILE Loop

WHILE <condition> DO
  <statements>
END WHILE;

FOR Loop

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

LOOP
  <statements>
  IF <condition> THEN
    BREAK;
  END IF;
END LOOP;

---

Table Types

Define reusable table structures:

CREATE TYPE <type_name> AS TABLE (
  <column1> <datatype>,
  <column2> <datatype>,
  ...
);

Usage in procedures:

CREATE PROCEDURE get_employees (OUT et_result MY_TABLE_TYPE)
LANGUAGE SQLSCRIPT AS
BEGIN
  et_result = SELECT * FROM "EMPLOYEES";
END;

---

Cursors

Cursors handle result sets row by row. Pattern: Declare → Open → Fetch → Close

Performance Note: Cursors bypass the database optimizer and process rows sequentially. Use primarily with primary key-based queries. Prefer set-based operations when possible.
DECLARE CURSOR <cursor_name> FOR
  SELECT <columns> FROM <table> [WHERE <condition>];

OPEN <cursor_name>;

FETCH <cursor_name> INTO <variables>;

CLOSE <cursor_name>;

Complete Example:

DO
BEGIN
  DECLARE lv_id INTEGER;
  DECLARE lv_name NVARCHAR(100);
  DECLARE CURSOR cur_employees FOR
    SELECT id, name FROM "EMPLOYEES" WHERE dept = 'IT';

  OPEN cur_employees;
  FETCH cur_employees INTO lv_id, lv_name;
  WHILE NOT cur_employees::NOTFOUND DO
    -- 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:

FOR row AS cur_employees DO
  SELECT row.id, row.name FROM DUMMY;
END FOR;

---

Exception Handling

EXIT HANDLER

Suspends execution and performs cleanup when exceptions occur.

DECLARE EXIT HANDLER FOR <condition_value>
  <statement>;

Condition values:

  • SQLEXCEPTION - Any SQL exception
  • SQL_ERROR_CODE <number> - Specific error code

Access error details:

  • ::SQL_ERROR_CODE - Numeric error code
  • ::SQL_ERROR_MESSAGE - Error message text

Example:

CREATE PROCEDURE safe_insert (IN iv_id INTEGER, IN iv_name NVARCHAR(100))
LANGUAGE SQLSCRIPT AS
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT ::SQL_ERROR_CODE AS err_code,
           ::SQL_ERROR_MESSAGE AS err_msg FROM DUMMY;
  END;

  INSERT INTO "MYTABLE" VALUES (:iv_id, :iv_name);
END;

CONDITION

Associate user-defined names with error codes:

DECLARE <condition_name> CONDITION FOR SQL_ERROR_CODE <number>;

-- 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):

-- Throw exception
SIGNAL <condition_name> SET MESSAGE_TEXT = '<message>';

-- Re-throw in handler
RESIGNAL [<condition_name>] [SET MESSAGE_TEXT = '<message>'];

Common Error Codes:

CodeDescription
301Unique constraint violation
1299No data found

---

AMDP Integration

ABAP Managed Database Procedures allow SQLScript within ABAP classes.

Class Definition

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

    TYPES: BEGIN OF ty_result,
             id   TYPE i,
             name TYPE string,
           END OF ty_result,
           tt_result TYPE STANDARD TABLE OF ty_result.

    CLASS-METHODS: get_data
      IMPORTING VALUE(iv_filter) TYPE string
      EXPORTING VALUE(et_result) TYPE tt_result.
ENDCLASS.

Method Implementation

CLASS zcl_my_amdp IMPLEMENTATION.
  METHOD get_data BY DATABASE PROCEDURE
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING ztable.

    et_result = SELECT id, name
                FROM ztable
                WHERE category = :iv_filter;
  ENDMETHOD.
ENDCLASS.

AMDP Restrictions

  • Parameters must be pass-by-value (no RETURNING)
  • Only scalar types, structures, internal tables allowed
  • No nested tables or deep structures
  • COMMIT/ROLLBACK not permitted
  • Must use Eclipse ADT for development
  • Auto-created on first invocation

---

Performance Best Practices

1. Reduce Data Volume Early

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

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

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

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

LimitValue
Table locks per transaction16,383
Tables in a statement4,095
SQL statement length2 GB
Procedure sizeBounded by SQL statement length (2 GB)
Note: Actual limits may vary by HANA version. Consult SAP documentation for version-specific limits.

---

Debugging Tools

  • SQLScript Debugger - SAP Web IDE / Business Application Studio
  • Plan Visualizer - Analyze execution plans
  • Expensive Statement Trace - Identify bottlenecks
  • SQL Analyzer - Query optimization recommendations

---

Quick Reference

String Concatenation

lv_result = lv_str1 || ' ' || lv_str2;

NULL Handling

COALESCE(value, default_value)
IFNULL(value, default_value)
NULLIF(value1, value2)

Date Operations

ADD_DAYS(date, n)
ADD_MONTHS(date, n)
DAYS_BETWEEN(date1, date2)
CURRENT_DATE
CURRENT_TIMESTAMP

Type Conversion

CAST(value AS datatype)
TO_VARCHAR(value)
TO_INTEGER(value)
TO_DATE(string, 'YYYY-MM-DD')
TO_TIMESTAMP(string, 'YYYY-MM-DD HH24:MI:SS')

---

Related Skills

For comprehensive SAP development, combine this skill with:

SkillUse Case
sap-abapABAP programming patterns for AMDP context
sap-abap-cdsCDS views that consume SQLScript procedures
sap-cap-capireCAP framework database procedures integration
sap-hana-cliHANA CLI for procedure deployment and testing
sap-btp-cloud-platformBTP 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

Related skills

open.feishu.cnLark BaseLet their coding agents read from, write to, and query structured data stored in Lark Base (Feishu) spreadsheets and databases.493kmicrosoftAzure KustoRun fast KQL queries against Azure Data Explorer for log analytics, telemetry, and time-series data.484k1.3klarksuiteLark SheetsCreate, read, update, and export spreadsheets inside Lark (Feishu) directly from their AI coding agent.389k15.9ksupabaseSupabase Postgres Best PracticesAutomatically apply Supabase Postgres performance rules during query writing, schema design, and database optimization.313k2.4kfirebaseFirebase Data ConnectGenerate complete, production-ready Firebase Data Connect GraphQL schemas and queries for complex relational data models.112k392firebaseFirebase FirestoreAdd Cloud Firestore read/write capabilities to an Android Kotlin app using the official Firebase SDK.77k392
Databasesdatabases
Browse by category
AI & AgentsFrontend & DesignBackend & DataDev ToolsAutomationTesting & ReviewSecurityMonitoring & CloudDocs & Planning
Design & frontend
Design systemsAnimation & motionAccessibility (a11y)CSS & TailwindUI designUXDesign to codeshadcn/uiLanding pagesBest design skills
Most searched skills
find-skillscavemanfrontend-designbrainstormingsystematic-debuggingstop-slopemil-design-engui-ux-pro-maxkarpathy-guidelinesweb-design-guidelinesreact-best-practicesgrill-meimprovemicrosoft-foundryagent-browser
Browse all listings
All agent skillsAll MCP serversAll marketplacesAll plugins

This week in AI coding

Five minutes, every Monday - the tools, releases and tactics for developers.

unsubscribe anytime.