No data found but proc should still continue

[Former Member]

I have a stored procedure in HANA that inserts a result set into a variable.  However, sometimes, the result set will come up empty.

For example

select dollaramount into variable

from dummy

where x=material;

In some cases, the material won't be in the table, resulting in an empty result.

When this happens, HANA errors out with:

Could not execute 'call zfcc.Z_WEEKS_OF_SUPPLY' in 2.867 seconds .

SAP DBTech JDBC: [1299]: no data found:  [1299] "ZFCC"."Z_WEEKS_OF_SUPPLY": line 64 col 4 (at pos 1707): [1299] (range 3) no data found exception

I know I can use an Exit Handler (DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299), but I don't want to stop the procedure.  I want the variable to be set to a 0 if there is no result.

Any ideas how I can make this happen?  Thanks in advance.

[Former Member]

The work around I used is to create a union to create a dummy transaction.

select sum(dollaramount) into variable

from (

select dollaramount as dollararmount

from dummy

where x=material

union

select 0 from dummy);

This way, I always get at least one record of a 0 which won't impact my results.  It seems like it's working but was wondering if there was a better way to go about it.