Richard Spitz wrote:
>
> Hi,
>
> in a Stored Procedure, I have an implicit cursor with FOREACH. However,
> it is possible that the SELECT statement returns no rows, and in that
> case,
> I want to have a completely different action.
>
> I tried catching the NOTFOUND with ON EXCEPTION IN (100), but it seems
> that NOTFOUND doesn't raise an exception at all. Is that correct? How
> can
> I find out in my procedure whether the SELECT returned a NOTFOUND?
>
> Regards, Richard
> --
> +--------------------------+------------------------------------------+
> | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de |
> | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3413 |
> | Klinikum Grosshadern | FAX : +49-89-7095-8886 |
> | 81366 Munich, Germany | |
> +--------------------------+------------------------------------------+
Hi Richard,
you can try to use a variable to flag if something was found or not,
i.e.
LET l_cnt = 0;
FOREACH SELECT ...
LET l_cnt = l_cnt + 1;
END FOREACH
IF l_cnt = 0 THEN
RAISE EXCEPTION -9999,0;
END IF
As a side-effect you have the number of records found accessible -)
HTH
Tolis
+---------------------------------------------------------------------+
| V+K Relational Solutions email: tvarnas@compulink.gr |
| Deligiorgi 26 tvarnas@orbit.de |
| 546 42 Thessaloniki Voice: (30) 31 820270 |
| Greece Fax: (30) 31 865463 |
+---------------------------------------------------------------------+