>The following example should present my case:
>
>create procedure test (id_in integer)
>returns (id_out integer)
>as
>begin
>
> --loop
> id_out = 99; --previous assigned value
>
> --this could return an empty set.
> select f.id_factura
> from facturas f
> where f.id_factura = :id_in
> into :id_out;
> suspend;
> --end loop
>end
>
>In a loop, we sometimes forget that if the query returns an empty set,
>the value in id_out returns the previously assigned value instead of NULL.
>
>The obvious solution is to always initialize the variable before a select into,
>but that is what we sometimes forget, and these bugs are hard to find.
>
> id_out = null;
> select f.id_factura
> from facturas f
> where f.id_factura = :id_in
> into :id_out;
>
>Another solution we found is to always use an aggregate when the semantics
>allow it, but this is a rather cumbersome solution.
>
> select avg(f.id_factura)
> from facturas f
> where f.id_factura = :id_in
> into :id_out;
> suspend;
>
>Does anyone have a better solution?

I agree with Helen that initialising the variables is the best solution. However, an alternative you could consider if your selects are simple, would be:

select coalesce(f.id_factura, 99)
from rdb$database
left join facturas f on f.id_factura = :id_in
into :id_out;

In addition to being more cumbersome, this also requires you to think through the order in which you place the tables in your query. At least in Fb 1.5 (which I mostly use), when using SELECT ... FROM A JOIN B ON ... it doesn't matter whether I put A or B first. However, SELECT ... FROM rdb$DATABASE LEFT JOIN A ON ... LEFT JOIN B ON ... will provide a different plan if I reverse the order of A and B.

HTH,
Set