At 10:20 AM 14/04/2011, aalliana wrote:

>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?

Code the loop and properly initialise variables *always*. Don't ever write code that might work sometimes and not work other times!

./heLen