SAP HANA Stored Procedure optional output parameter with text type

Question

Let's imagine that I have created the stored procedure in SAP HANA database and would like to have optional out parameter with text type, like error details. As I have read to achieve this I should use some default value thus I have done like this:

PROCEDURE "myProcedure"
(
    IN  inSomeParameter  BIGINT,
    OUT outResult        INTEGER,   -- output, result of the operation
    OUT outErrorDetail   NVARCHAR(32) default ''
)

Unfortunately build failed with the following error:

OUT and IN OUT parameters may not have default expressions

So, I decided to try with null, but it failed the same way. Later I changed the type to integer just to try and it failed exactly same way again.

In the same time this works:

PROCEDURE "myProcedure"
(
    IN  inSomeParameter  BIGINT,
    OUT outResult        INTEGER,   -- output, result of the operation
    OUT outErrorDetail   TABLE(errorDetails NVARCHAR(32)) default empty
)

but it feels like a huge overkill - to make a table to return only one text value.

Do you have any suggestion how to add optional output parameter?

Answer 1

SQL Script in its current state doesn’t allow for optional OUT parameters. Why don’t you just set the OUT parameter default value in the procedure body right before the code? This adds boilerplate code, but you could also use it to convey explicit success messages.