Hi Danny!

> 4)
> for
> select *
> from table_a a
> where a.pk = :value
> union all
> select *
> from table_a a
> where (:value = -1)
> into ...
> do begin
> <do something>
> end

The problem with this solution is that (:value = -1) is evaluated for
each record in the table. Hence, it is similar to solution 1 and 2 (at
least for Firebird 1.5, I don't know much about Fb 2).

Maybe

> 5)
> for
> select *
> from table_a a
> where a.pk between :value1 and :value2
> into ...
> do begin
> <do something>
> end

will fulfill your requirements? It will be slower when you want the
entire table returned(e.g. value1 = -1, value2 = 999999999), but should
be good when you want a specific record (e.g value1 = 893205, value2 =
893205).

Excepting this suggestion, I do not know of any good alternative to
solution 3 (in complex scenarios, it is often possible to mix solution 3
with solution 1 or 2 to provide decent performance with maintainable code).

HTH,
Set

> 1)
> for
> select *
> from table_a a
> where (a.pk = :value) or (:value = -1)
> into ...
> do begin
> <do something>
> end
>
> 2)
> for
> select *
> from table_a a
> where (a.pk = :value) or (:value is null)
> into ...
> do begin
> <do something>
> end
>
> 3)
> if (value is null) then
> begin
> for
> select *
> from table_a a
> into ...
> do begin
> <do something>
> end
> end
> else begin
> for
> select *
> from table_a a
> where (a.pk = :value)
> into ...
> do begin
> <do something>
> end
> end