Thanks to all on this subject; after trying the options I'll stick to
my original versions because of the performance hit, as Martijn
suggested.
I guess we are all striving to get best performance for the least cost/effort.
Alan

--
Alan J Davies


Quoting Svein Erling Tysvær <svein.erling.tysvaer@...>:

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