📋

Description

This error signifies that an SAP HANA SQLScript procedure or function has been defined with more than one output parameter (OUT or INOUT), which is a violation of the SQLScript syntax rules. It typically occurs when activating or executing SQLScript objects within the SAP S/4HANA environment that interact directly with the underlying HANA database.

💬

Error Message

ERR_SQLSCRIPT_AT_MOST_ONE_OUT_PARAM: At most one output parameter is allowed

🔍

Known Causes

3 known causes

⚠️

Multiple OUT Parameters Declared

A SQLScript procedure or function was explicitly defined with more than one 'OUT' or 'INOUT' parameter in its signature, which is not permitted by HANA SQLScript.

⚠️

Incorrect Procedure Signature Design

The procedure's signature was designed to return multiple individual values as output, leading to the use of multiple OUT parameters instead of a single table type or cursor.

⚠️

Misinterpretation of SQLScript Syntax

The developer or user might have misinterpreted the specific syntax rules of SAP HANA SQLScript, assuming multiple output parameters were permissible as in other database procedural languages.

🛠️

Solutions

3 solutions available

1. Refactor Stored Procedure to Use a Table Type Output advanced

Modify the stored procedure to return a single table type instead of multiple scalar output parameters.

1

Identify the stored procedure causing the error. This will likely be a custom-developed procedure or a very specific SAP function that has been modified.

2

Analyze the existing output parameters. Determine how to logically group these outputs into a single structure.

3

Create a new table type in SAP HANA that represents the combined structure of your desired output parameters. This can be done using SQLScript.

CREATE TYPE MY_OUTPUT_TABLE_TYPE AS TABLE (param1 TYPE_OF_PARAM1, param2 TYPE_OF_PARAM2, ...);

4

Modify the stored procedure's signature to declare a single output parameter of the newly created table type.

CREATE PROCEDURE MY_PROCEDURE (...) -- input parameters
RETURNS MY_OUTPUT_TABLE_TYPE -- single output parameter
LANGUAGE SQLSCRIPT
AS
BEGIN
  -- Procedure logic to populate the output table
  RETURN SELECT param1, param2, ... FROM ...;
END;

5

Rewrite the procedure's logic to populate this single table type. Instead of assigning values to individual output parameters, insert rows into the table type.

DECLARE output_table MY_OUTPUT_TABLE_TYPE;
-- ... logic to populate output_table ...
INSERT INTO :output_table (param1, param2, ...) VALUES (value1, value2, ...);
-- ... more inserts if needed ...
RETURN :output_table;

6

Update any calling programs or functions to expect a single table type as the output, and process the data accordingly.

2. Consolidate Scalar Outputs into a Single VARCHAR/JSON Output medium

Combine multiple scalar outputs into a single string or JSON object for return.

1

Identify the stored procedure and its multiple scalar output parameters that are causing the error.

2

Choose between returning a delimited string or a JSON object. JSON is generally preferred for structured data.

3

Modify the stored procedure to have a single output parameter of type VARCHAR or NVARCHAR. If using JSON, ensure the length is sufficient.

CREATE PROCEDURE MY_PROCEDURE (...) -- input parameters
RETURNS OUT_PARAM VARCHAR(4000) -- single string output parameter
LANGUAGE SQLSCRIPT
AS
BEGIN
  -- Procedure logic to build the output string/JSON
  OUT_PARAM := ...;
END;

4

Within the stored procedure, construct the output string by concatenating the values of the original scalar outputs. Use a delimiter if creating a delimited string.

DECLARE v_param1 VARCHAR(100);
DECLARE v_param2 INTEGER;
-- ... populate v_param1 and v_param2 ...
OUT_PARAM := :v_param1 || '|' || :v_param2;

5

Alternatively, use JSON functions to build a JSON string. This is more robust for complex data.

DECLARE v_param1 VARCHAR(100);
DECLARE v_param2 INTEGER;
-- ... populate v_param1 and v_param2 ...
OUT_PARAM := JSON_OBJECT('param1': :v_param1, 'param2': :v_param2);

6

Update the calling application or function to parse the single string output (e.g., using string splitting functions or JSON parsing libraries).

3. Utilize a Temporary Table for Multiple Outputs medium

Leverage a temporary table within the stored procedure to hold multiple output values.

1

Identify the stored procedure and its multiple scalar output parameters.

2

Modify the stored procedure to not have any explicit output parameters in its signature. Instead, it will populate a temporary table.

CREATE PROCEDURE MY_PROCEDURE (...) -- input parameters
LANGUAGE SQLSCRIPT
AS
BEGIN
  -- Procedure logic to populate the temporary table
END;

3

Inside the procedure, declare and create a local temporary table with columns corresponding to the desired output values.

CREATE LOCAL TEMPORARY COLUMN TABLE "#MY_TEMP_OUTPUT" (
  param1 TYPE_OF_PARAM1,
  param2 TYPE_OF_PARAM2
);

4

Populate the temporary table with the results of your procedure's logic. You can insert multiple rows if needed.

INSERT INTO "#MY_TEMP_OUTPUT" (param1, param2)
SELECT value1, value2
FROM your_source_table
WHERE ...;

5

If the calling program needs to access these values, you will need to select from the temporary table after the procedure call. This often involves a wrapper function or selecting into variables in the calling context.

CALL MY_PROCEDURE(...);
SELECT param1, param2 INTO v_output1, v_output2 FROM "#MY_TEMP_OUTPUT";

6

Consider using a table variable (similar to temporary tables but with different scope and management) if appropriate for your use case.

🔗

Related Errors

5 related errors

SAP S/4HANA Error 1312: SQLScript Single Output Limit