HANA SQL Script: Writing Procedures on SAP HANA Database

TL;DR

HANA SQL Script is HANA's procedural language for stored procedures — like Oracle PL/SQL but with key differences: no cursors (table variables instead), set-based operations, and explicit parameter mode declaration (IN, OUT, INOUT).

A procedure declares input (IN) and output (OUT) parameters, uses table variables to store intermediate results, and executes a block of procedural and SQL code. Parameters are referenced with a colon (`:param`).

Real SuccessFactors IM use: batch attainment calculations, commission tier assignment, rollup aggregations that feed dashboards. Procedures are faster than iterative application logic.

SAP HANA SQL Script is the procedural extension for HANA. It's the equivalent of Oracle PL/SQL: you write stored procedures that execute complex business logic, manipulate data, and return results to applications. If you're coming from Oracle, SQL Script will feel familiar — but the philosophy differs significantly. HANA emphasizes set-based operations and table variables over cursor-heavy row-by-row processing. This lesson covers the essentials: procedure structure, parameters, table variables, control flow, and error handling.

What HANA SQL Script Is (And Isn't)

SQL Script Is:

A procedural extension for HANA, executed server-side.
Set-based: you work with tables and result sets, not rows.
Integrated with HANA's columnar engine for performance.
Called from applications or other procedures via SQL.

SQL Script Is NOT:

Cursor-heavy: No explicit cursors. Use table variables instead.
Row-by-row: Avoid loops over individual rows. Aggregate, then process the result set.
Stateful: Procedures don't persist state between calls. They start fresh each time.

The mental shift: PL/SQL encourages fetching rows one at a time and processing them. SQL Script encourages fetching all rows, processing them as a set (with JOINs and aggregations), then returning or updating. This is faster and leverages HANA's columnar strength.

CREATE PROCEDURE: Basic Structure

Minimal Procedure

CREATE OR REPLACE PROCEDURE hello_world()
LANGUAGE SQLSCRIPT AS
BEGIN
  -- This is a comment
  SELECT 'Hello, World' AS MESSAGE;
END;

Breaking this down:

CREATE OR REPLACE PROCEDURE: Define a new procedure or replace existing one.
hello_world(): Procedure name and parameter list (empty here).
LANGUAGE SQLSCRIPT AS: Declare the language and begin the definition.
BEGIN ... END: The procedure body.

Procedure with Parameters

CREATE OR REPLACE PROCEDURE get_participant_earnings (
  IN  iv_participant_id    NVARCHAR(20),
  IN  iv_period_id        NVARCHAR(20),
  OUT ov_total_earned    DECIMAL(18,2),
  OUT ov_result_count    INTEGER
)
LANGUAGE SQLSCRIPT AS
BEGIN
  -- Query and assign output parameters
  SELECT
    SUM(RESULT_AMOUNT),
    COUNT(*)
  INTO :ov_total_earned, :ov_result_count
  FROM   CSC_RESULTS
  WHERE  PARTICIPANT_ID = :iv_participant_id
    AND  PERIOD_ID = :iv_period_id;
END;

Key syntax rules:

IN: Input parameter. Caller provides the value. Read-only inside the procedure.
OUT: Output parameter. Procedure sets the value; caller reads it.
INOUT: Input and output. Caller passes a value; procedure can modify it.
Parameter names: Convention uses iv_ prefix (input variable), ov_ (output variable), lv_ (local variable).
Colons (`:`): Reference parameters and local variables with a leading colon (`:iv_participant_id`, `:ov_total_earned`).
INTO: Assign SELECT results to OUT parameters.

Table Variables: The Core of SQL Script

Table variables are SAP HANA's answer to cursors. Instead of fetching rows one at a time, you load an entire result set into a table variable, process it, and return or use it.

Declaring and Using a Table Variable

CREATE OR REPLACE PROCEDURE calc_attainment (
  IN  iv_period_id        NVARCHAR(20),
  OUT ot_attainment      TABLE (
    PARTICIPANT_ID NVARCHAR(20),
    PARTICIPANT_NAME NVARCHAR(200),
    TOTAL_EARNED   DECIMAL(18,2),
    QUOTA_AMOUNT   DECIMAL(18,2),
    ATTAINMENT_PCT DECIMAL(8,2)
  )
)
LANGUAGE SQLSCRIPT AS
BEGIN
  -- Declare a local table variable for intermediate results
  DECLARE lt_results TABLE (
    PARTICIPANT_ID NVARCHAR(20),
    TOTAL_EARNED   DECIMAL(18,2)
  );

  -- Populate the local table variable
  lt_results = SELECT
    PARTICIPANT_ID,
    SUM(RESULT_AMOUNT) AS TOTAL_EARNED
  FROM   CSC_RESULTS
  WHERE  PERIOD_ID = :iv_period_id
  GROUP BY PARTICIPANT_ID;

  -- Join the local table with dimension data to build output
  ot_attainment = SELECT
    r.PARTICIPANT_ID,
    p.NAME,
    r.TOTAL_EARNED,
    q.QUOTA_AMOUNT,
    ROUND(r.TOTAL_EARNED / NULLIF(q.QUOTA_AMOUNT, 0) * 100, 2)
  FROM   :lt_results r
  LEFT JOIN CSC_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID
  LEFT JOIN CSC_QUOTA q
           ON q.PARTICIPANT_ID = r.PARTICIPANT_ID
           AND q.PERIOD_ID = :iv_period_id;
END;

Key points:

DECLARE: Local table variables must be declared with DECLARE.
Output table variables: In the parameter list, use TABLE (...) with column definitions.
Assignment: Use = (not INTO) to assign a SELECT result to a table variable.
Referencing: Use `:lt_results` (with colon) when joining or selecting from a table variable.
Structure: Table variables are typed — you must define column names and types upfront.

Control Flow: IF, ELSE, LOOPS

SQL Script supports procedural control structures similar to PL/SQL.

IF...ELSE Statements

CREATE OR REPLACE PROCEDURE tier_commission (
  IN  iv_attainment_pct   DECIMAL(8,2),
  OUT ov_tier_name       NVARCHAR(50),
  OUT ov_multiplier      DECIMAL(5,2)
)
LANGUAGE SQLSCRIPT AS
BEGIN
  IF :iv_attainment_pct >= 150 THEN
    :ov_tier_name := 'ACCELERATED';
    :ov_multiplier := 1.5;
  ELSEIF :iv_attainment_pct >= 100 THEN
    :ov_tier_name := 'STANDARD';
    :ov_multiplier := 1.0;
  ELSEIF :iv_attainment_pct >= 75 THEN
    :ov_tier_name := 'PARTIAL';
    :ov_multiplier := 0.5;
  ELSE
    :ov_tier_name := 'BELOW_TARGET';
    :ov_multiplier := 0;
  END IF;
END;

Note the assignment operator: `:=` (not `=` for variable assignment, which is for SELECT results).

FOR and WHILE Loops (Use Sparingly!)

SQL Script supports loops, but avoid them. Row-by-row processing defeats HANA's strength. Use them only when set-based logic isn't possible.

-- AVOID: Looping over rows is slow on HANA
DECLARE lv_count INTEGER := 1;
WHILE :lv_count <= 100 DO
  -- Do something
  :lv_count := :lv_count + 1;
END WHILE;

-- BETTER: Use set-based operations instead
UPDATE CSC_RESULTS
SET COMMISSION = RESULT_AMOUNT * 0.1
WHERE PERIOD_ID = :iv_period_id;

Error Handling: SIGNAL and DECLARE EXIT HANDLER

Handle errors gracefully with error signaling and handlers.

SIGNAL: Raise an Error

CREATE OR REPLACE PROCEDURE validate_period (
  IN  iv_period_id NVARCHAR(20)
)
LANGUAGE SQLSCRIPT AS
BEGIN
  DECLARE lv_period_exists INTEGER;

  -- Check if period exists
  SELECT COUNT(*)
  INTO :lv_period_exists
  FROM   CSC_PERIOD
  WHERE  ID = :iv_period_id;

  -- Raise error if not found
  IF :lv_period_exists = 0 THEN
    SIGNAL SQLEXCEPTION SET
      MESSAGE_TEXT = CONCAT('Period ', :iv_period_id, ' not found'),
      SQLSTATE = '45000';
  END IF;
END;

DECLARE EXIT HANDLER: Catch an Error

CREATE OR REPLACE PROCEDURE safe_update (
  IN  iv_participant_id NVARCHAR(20),
  OUT ov_success        INTEGER,
  OUT ov_message        NVARCHAR(200)
)
LANGUAGE SQLSCRIPT AS
BEGIN
  -- Handle any SQL exception
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    :ov_success := 0;
    :ov_message := 'Update failed: error occurred';
  END;

  -- Attempt the update
  UPDATE CSC_PARTICIPANT
  SET STATUS = 'INACTIVE'
  WHERE ID = :iv_participant_id;

  -- Success path
  :ov_success := 1;
  :ov_message := 'Update successful';
END;

Worked Example: Attainment Calculation Procedure

Here's a complete procedure that calculates attainment percentages for all active participants in a period:

CREATE OR REPLACE PROCEDURE calc_period_attainment (
  IN  iv_period_id        NVARCHAR(20),
  OUT ot_attainment      TABLE (
    PARTICIPANT_ID   NVARCHAR(20),
    PARTICIPANT_NAME NVARCHAR(200),
    TOTAL_EARNED     DECIMAL(18,2),
    QUOTA_AMOUNT     DECIMAL(18,2),
    ATTAINMENT_PCT   DECIMAL(8,2),
    PERFORMANCE_TIER NVARCHAR(50)
  ),
  OUT ov_record_count    INTEGER,
  OUT ov_success        INTEGER,
  OUT ov_message        NVARCHAR(500)
)
LANGUAGE SQLSCRIPT AS
BEGIN
  -- Validate period exists
  DECLARE lv_period_exists INTEGER;

  SELECT COUNT(*)
  INTO :lv_period_exists
  FROM   CSC_PERIOD
  WHERE  ID = :iv_period_id;

  IF :lv_period_exists = 0 THEN
    :ov_success := 0;
    :ov_message := CONCAT('Period ', :iv_period_id, ' not found');
    :ov_record_count := 0;
  ELSE
    -- Build attainment table with all calculations
    ot_attainment = SELECT
      p.ID AS PARTICIPANT_ID,
      UPPER(TRIM(p.NAME)) AS PARTICIPANT_NAME,
      ROUND(SUM(r.RESULT_AMOUNT), 2) AS TOTAL_EARNED,
      ROUND(SUM(q.QUOTA_AMOUNT), 2) AS QUOTA_AMOUNT,
      ROUND(
        SUM(r.RESULT_AMOUNT) /
        NULLIF(SUM(q.QUOTA_AMOUNT), 0) * 100,
        2
      ) AS ATTAINMENT_PCT,
      CASE
        WHEN SUM(r.RESULT_AMOUNT) /
             NULLIF(SUM(q.QUOTA_AMOUNT), 0) >= 1.5 THEN 'ACCELERATED'
        WHEN SUM(r.RESULT_AMOUNT) /
             NULLIF(SUM(q.QUOTA_AMOUNT), 0) >= 1.0 THEN 'QUOTA_MET'
        WHEN SUM(r.RESULT_AMOUNT) /
             NULLIF(SUM(q.QUOTA_AMOUNT), 0) >= 0.75 THEN 'ON_TRACK'
        ELSE 'AT_RISK'
      END AS PERFORMANCE_TIER
    FROM   CSC_PARTICIPANT p
    LEFT JOIN CSC_RESULTS r
             ON r.PARTICIPANT_ID = p.ID
             AND r.PERIOD_ID = :iv_period_id
    LEFT JOIN CSC_QUOTA q
             ON q.PARTICIPANT_ID = p.ID
             AND q.PERIOD_ID = :iv_period_id
    WHERE  p.STATUS = 'ACTIVE'
    GROUP BY p.ID, p.NAME
    ORDER BY ATTAINMENT_PCT DESC;

    -- Count records and set success message
    :ov_record_count := COUNT(*) FROM :ot_attainment;
    :ov_success := 1;
    :ov_message := CONCAT(
      'Attainment calculated for ',
      CAST(:ov_record_count AS NVARCHAR),
      ' participants in period ',
      :iv_period_id
    );
  END IF;
END;

This procedure:

Validates the period exists.
Builds a complete attainment dataset with earnings, quota, percentage, and performance tier.
Calculates the tier using CASE logic based on attainment percentage.
Returns the result set, a count, and a success message.
Uses set-based operations (no loops) for performance.

Calling a Procedure from SQL

Once created, call the procedure from any query tool:

-- Declare output variables
CALL calc_period_attainment(
  '2026-Q1',
  ?,
  ?,
  ?,
  ?
);

-- Or with variable assignment (in a script context)
CALL tier_commission(
  125.5,
  ov_tier,
  ov_mult
);

In HANA Database Explorer, you can right-click a procedure and "Generate CALL" to create a template automatically.

Key Differences from Oracle PL/SQL

Feature	Oracle PL/SQL	HANA SQL Script
Cursors	Explicit: OPEN, FETCH, CLOSE loop	No explicit cursors. Use table variables.
Processing style	Row-by-row loop encouraged	Set-based operations encouraged
Variable assignment	:= or INTO after SELECT	:= for simple, INTO for SELECT INTO single value, = for table variable
Parameter reference	No prefix: parameter_name	Colon prefix: :parameter_name
Output parameter syntax	OUT param IN OUT declarations mixed	Explicit IN, OUT, INOUT modes
Exception handling	BEGIN EXCEPTION WHEN ... END	DECLARE [EXIT] HANDLER FOR [condition]

Performance Best Practices for SQL Script

Avoid loops: Use JOIN and GROUP BY instead of cursors and loops.
Minimize table variables: Each intermediate result set consumes memory. Nest operations when possible.
Filter early: Apply WHERE conditions to CSC_ tables as soon as possible, not after building large intermediate sets.
Test with EXPLAIN PLAN: Check the execution plan for your queries within procedures, especially in the recursive parts.
Use INSERT INTO ... SELECT: For inserting procedure results into real tables, never loop and INSERT individual rows.

Key Takeaways

HANA SQL Script is the procedural language for HANA — the equivalent of Oracle PL/SQL.
Procedures use table variables (not cursors) to hold result sets.
Parameters are declared with IN, OUT, or INOUT mode. Reference them with a colon (`:param`).
Use set-based operations (JOIN, GROUP BY, aggregates) instead of loops. Leverage HANA's columnar strength.
Control flow: IF/ELSEIF/ELSE, WHILE/FOR loops (avoid loops), CASE expressions.
Error handling: SIGNAL to raise errors, DECLARE EXIT HANDLER to catch them.
Real SuccessFactors IM use: batch attainment calculations, tiering, rollup aggregations for dashboards.

Learning Path Complete!

You've now covered HANA fundamentals, SQL, functions, hierarchies, and procedures. You're ready to build production SuccessFactors IM queries and procedures.

Back to Learning Path Hub →