0

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?

Rufi
  • 2,529
  • 1
  • 20
  • 41

1 Answers1

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.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • Yes, that is possible, but the problem is that the procedure is called in many places, thus it brings a lot of additional work to change every call. It is nicer if there is a way to do it one by one when it is necessary. – Rufi Mar 04 '19 at 07:23
  • Well, alternatively I would use the `SIGNAL` facility to raise an error message including additional information. That way, you would use the standard error handling channel. – Lars Br. Mar 04 '19 at 08:06