Hi Alan,

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

You can combine this by using the parameter to check if it's 0 or
filled in etc, but when looking at the query plan, you will see this is
suboptimal. You have created the most optimal case.

However, you can cut down on coding by using FOR EXECUTE
STATEMENT and modifying the WHERE H.HAUL_CODE part
on the fly.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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