Sure it can be done very easily, but at the cost of not being able to benefit from an index for this particular field (I also changed to JOIN (SQL-92), I haven't yet heard of any good reason (except 'old habit') for using the implicit joining of SQL-89):

for select H.Haul_Code,H.Haul_Name,A.amount
from Haulier H
join account_charges A on A.code=H.haul_code
where H.Haul_Code= coalesce(:supphaul, H.Haul_Code)

If :supphaul is NULL, then all rows will be returned. Though don't believe this to be THE universal solution for these kind of problems, since it prevents an index from being useful for H.Haul_Code it isn't any good solution if you have millions of rows and no other indexed field in your WHERE clause - if so your verbose solution or Martijns EXECUTE STATEMENT suggestion are your best options. Though for small tables or queries that benefit from other selective indexes, I find COALESCE quite useful.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Alan.Davies@...
Sent: 19. november 2007 12:25
To: firebird-support@yahoogroups.com
Subject: [firebird-support] conditional selects in a SP

Hi, I don't know if this can be done, but basically I want to select
one single record, or all relevant records in a database. I do this as
follows but there is a lot of duplicate coding (and, therefore, more
chance of errors) This is a cut-down version, if they were all this
simple I wouldn't bother:

CREATE PROCEDURE HAULIERCHARGES (
print_all_one integer,
supphaul integer,
)
returns (
code integer,
name char(40)
amount numeric(9,2))
as
begin
if (PRINT_ALL_ONE=0) then /* One Haulier only - :HaulCode */
begin
for select H.Haul_Code,H.Haul_Name,A.amount
from Haulier H,account_charges A
where H.Haul_Code=:supphaul *** the difference ***
and A.code=H.haul_code
order by H.Haul_Name
into :Code,:Name,:Amount
do
suspend;
end
else if (PRINT_ALL_ONE=1) then /* All Hauliers */
begin
for select H.Haul_Code,H.Haul_Name,A.amount
from Haulier H,account_charges A
where A.code=H.haul_code *** the difference ***
order by H.Haul_Name
into :Code,:Name,:Amount
do
suspend;
end
end
--
Alan J Davies