Christian Gütter <cguetter@...> wrote:

>[...] But when I do the same select in a stored procedure (R_Usr = 'TOWI')
>
> SELECT Val_Intern, Val_Extern FROM T_Stundensaetze WHERE
>(Usr=:R_Usr)
> INTO :L_Val_Intern, :L_Val_Extern;
> if ((L_Val_Intern IS NULL) or (L_Val_Extern IS NULL)) then EXCEPTION
>E_Kein_Stundensatz;
>
>then the exception will not be fired because "L_Val_Intern" and
>"L_Val_Extern" are not "NULL".
>They should be "NULL", but they contain the values of the previous user
>that was found by the stored procedure.
>
>So I wonder who is mixing something up: Interbase or me?
>[Usually, it's silly me.]

I'd guess, Interbase. I had the very same behaviour half a year
ago, unfortunately in a very complex stored procedure so it took
me a day to find out what was wrong. The only solution was
to set the Variable to NULL before the select.

On the other hand, it is disputable if this is wrong behaviour:
A select that does not return anything DOES NOT RETURN
ANYTHING, not even NULL, just leaves the result vars as
they were before. I'd guess that this is the technical background,
and the fact that we're normally getting NULLs for "empty"
selects in clients has to do more with how the column
data stuctures are initialized on the client than with
the Interbase engine actually returning NULLs.

It'll be great if there was a more clean and elegant way
to check in a stored procedure after a select if it has
returned values or not (especially because checking
for NULL is not really equal with checking for no result
anyway). Of course, a SELECT COUNT(x) before
the actual select would work, but I think that's not
elegant nor efficient (but precise at least).

Just in this context: I also had the opposite case:
when doing a SELECT SUM(x) in a stored procedure,
the result contains NULL if no summands were found.
But in most contexts I'd expect that summing no records
just gives zero, especially when using the result in further
numeric calculations.
Now I have to add a statement like:

"IF (returned_sum IS NULL) THEN returned_sum = 0"

after almost every SELECT SUM/MIN/MAX etc.

Interestingly, the behaviour is different here; It does not
help setting the returned_sum variable to zero before
the SELECT SUM(), it is actively overwritten with NULL.

At least, that's what I observed so far - any real insight
provided by those who KNOW (instead of guess like me)
what's going on would be great :-)

--
Lukas Zeller (luz@...)
-
Synthesis - Sustainable Software Concepts
info@..., http://www.synthesis.ch