At 08:16 AM 11/04/2007, Adam wrote:

>--- In firebird-support@yahoogroups.com, "slalom91" <slalom91@...> wrote:
> >
> > I had a stored procedure similar to the following:
> >
> > ----
> > Select value from table where id = someidnumber into :variable;
> >
> > If (variable is null) then
> > result = 'EOF'
> > else
> > result = variable;
> >
> > ----
> > In FB 1.5 this was working when the result set was empty. However,
> > with FB 2.0.1 it is not.
>
>If it was working in 1.5 you were extremely lucky.
>
>If the result set of your query is empty, the variable will not be set
>to null. It won't be set at all.

Ummm, not so. If a variable is defined and has never been
initialised, it will be null. If the SELECT doesn't return a result
then the variable simply stays null. It's a valid (if unsafe)
test....in the case where the SELECT returns a result that is null,
the interpretation of the result is going to be wrong.

>Before your select, you need to say.
>
>Variable = null;

That doesn't help, if the query potentially returns a result that is null.

>Or you could simplify the whole thing.
>
>Variable = 'EOF';
>Select value from table where id = someidnumber into :variable;

That's better. :-)

But, better by far is to use an existence test and *never* to rely on
uninitialised variables remaining unset. This one will score a
coconut every time and avoids other horrors like multiple rows in
singleton select errors:

variable = 0;
select 1 from rdb$database
where exists (
(select 1 from atable where id = :someidnumber)
into :variable;

./heLen