📋

Description

This error, ERR_SQLSCRIPT_NOT_ASSIGNED_OUT_TABVAR, occurs in SAP HANA SQLScript procedures or functions when an `OUT` table variable is declared but does not receive an explicit assignment of a value or result set. It indicates that the procedure attempts to return an uninitialized or empty table variable, which is not permitted.

💬

Error Message

ERR_SQLSCRIPT_NOT_ASSIGNED_OUT_TABVAR

🔍

Known Causes

3 known causes

⚠️

Missing Assignment Statement

An `OUT` table variable was declared in the procedure signature but was not assigned any data through a `SELECT` statement or other assignment before the procedure completed.

⚠️

Uncovered Conditional Path

Within conditional logic (e.g., `IF/ELSE` statements), some execution branches may fail to assign a value to the `OUT` table variable, leading to the error if that specific path is taken.

⚠️

Typo in Variable Name

A typographical error in the name of the `OUT` table variable during an assignment operation can cause the intended variable to remain unassigned.

🛠️

Solutions

3 solutions available

1. Verify and Assign Output Table Variable in SQLScript easy

Ensure that the output table variable in your SQLScript procedure is correctly declared and assigned a value before returning.

1

Locate the SQLScript procedure that is generating the error. This is typically done by checking the execution stack or the transaction that triggered the error.

2

Within the SQLScript procedure, identify the `RETURN TABLE` statement. Before this statement, ensure that the table variable being returned has been populated with data. If it's intended to be empty, it should still be declared and potentially initialized.

PROCEDURE "MY_SCHEMA"."MY_PROCEDURE" (
  IN in_param VARCHAR(10),
  OUT out_table MY_SCHEMA.MY_TABLE_TYPE
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN

  -- Ensure 'out_table' is populated or declared if it can be empty
  -- Example: If no data is found, you might explicitly assign an empty table
  IF :in_param IS NULL THEN
    out_table = SELECT * FROM MY_SCHEMA.MY_TABLE_TYPE WHERE 1 = 2; -- Assign an empty table structure
  ELSE
    out_table = SELECT column1, column2 FROM my_source_table WHERE some_condition = :in_param;
  END IF;

  RETURN TABLE (:out_table);
END;

3

If the output table variable is intended to hold results, verify that the `SELECT` statement or other data manipulation logic correctly populates it. If the procedure can legitimately return an empty set, ensure the variable is still assigned an empty table of the correct type.

2. Check SQLScript Procedure Signature and Type Definitions medium

Confirm that the output table variable's data type in the procedure signature matches the actual data type used within the procedure body.

1

Examine the `OUT` parameter definition in your SQLScript procedure. Ensure the declared type (e.g., `MY_SCHEMA.MY_TABLE_TYPE`) is correct.

CREATE TYPE MY_SCHEMA.MY_TABLE_TYPE AS TABLE (
  column1 INT,
  column2 VARCHAR(50)
);

2

Inside the procedure, check how this output variable is being populated. If it's via a `SELECT` statement, ensure the columns and their types in the `SELECT` list are compatible with the declared `MY_TABLE_TYPE`. If you are assigning another table variable, ensure it has the same or a compatible structure.

out_table = SELECT CAST(column1 AS INT), CAST(column2 AS VARCHAR(50)) FROM another_source_table WHERE condition;
-- or
DECLARE temp_table MY_SCHEMA.MY_TABLE_TYPE;
temp_table = SELECT ...;
out_table = :temp_table;

3

If you are using implicit table type definitions (less common but possible in older versions or specific contexts), ensure that the structure of the data being assigned to the output variable is consistent.

3. Analyze and Correct Data Population Logic advanced

Thoroughly review the logic that populates the output table variable, especially conditional paths, to ensure it's always assigned a value.

1

Trace the execution flow of the SQLScript procedure. Pay close attention to any `IF/THEN/ELSE` blocks, loops, or error handling that might bypass the assignment of the output table variable.

2

Identify any conditions where the procedure might exit without assigning a value to the output table variable. This is a common cause of ERR_SQLSCRIPT_NOT_ASSIGNED_OUT_TABVAR.

3

Implement robust assignment for all possible execution paths. This might involve:
- Explicitly assigning an empty table if no data is found.
- Ensuring that even in error scenarios (if handled within the procedure), a default empty table is assigned.
- Using `COALESCE` or similar functions on the result of a `SELECT` if it could return `NULL` for the entire table (though less direct for table variables).

PROCEDURE "MY_SCHEMA"."MY_PROCEDURE" (
  IN in_param INT,
  OUT out_table MY_SCHEMA.MY_TABLE_TYPE
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
  DECLARE row_count INT;

  out_table = SELECT column1, column2 FROM my_source_table WHERE id = :in_param;
  row_count = CEIL(CARDINALITY(:out_table)); -- Check if any rows were returned

  IF :row_count = 0 THEN
    -- Explicitly assign an empty table if no data is found
    out_table = SELECT * FROM MY_SCHEMA.MY_TABLE_TYPE WHERE 1 = 2;
  END IF;

  RETURN TABLE (:out_table);
END;

🔗

Related Errors

5 related errors

SAP S/4HANA Error 1316: Unassigned SQLScript Output Table Variable