Hello, first let me congratulate all the volunteers working with firebird.

I've found a pattern in the bugs in (our team) database programming. I was wondering if anyone has found a better way to solve my problem.

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?

Thanks in advance.

Alejandro