I'm having one of those days. I really couldn't see the wood for the
trees.

> You could have some enumerated type
>
> 1. GET_COMPANIES_BY_CODE
> 2. GET_COMPANIES_BY_NAME
> 3. GET_COMPANIES_BY_POSTCODE
> 4. GET_COMPANIES_BY_NAME_POSTCODE
>
> set term ^;
>
> create procedure "GET_COMPANIES"
> (
> iType integer,
> iParam1 varchar(100),
> iParam2 varchar(100)
> )
> RETURNS
> (
> CompanyID integer,
> CompanyName varchar(100)
> )
> AS
> DECLARE VARIABLE VID INTEGER;
> DECLARE VARIABLE VNAME VARCHAR(100);
> BEGIN
> IF (iType = 1) THEN
> BEGIN
> FOR SELECT ID, NAME
> FROM COMPANY
> WHERE CODE = :iParam1
> INTO :VID, :VNAME
> DO
> BEGIN
> COMPANYID = :VID;
> COMPANYNAME = :VNAME;
> SUSPEND;
> END
> END
> ELSE IF (iType = 2) THEN
> BEGIN
> etc
> END
> END
> ^
>
> It is best to have your stored procedure doing the where clause for
> you, otherwise the procedure will need to fetch every record before
> it can filter (so the underlying index will not be used). If the
> stored procedure runs a query with a where clause, that where clause
> will use any available index.

Yeah, thought that was the case.

> The main restriction with reusing the same procedure for the
> different queries is that their parameters will need to look pretty
> much identical. You can get away with casting things as varchars and
> vice versa, but I generally find it easier to have a different
> procedure for each one.

Ok. Sound fare enough.

> You shouldn't use a stored procedure for the sake of using one
> though. There is no harm in actually running a query from your
> application. I use stored procedures when the query is common, when
> it is a bit more complex and I dont trust the developer to query the
> right fields, when a group of fields in different tables need to be
> impacted in a single transaction, when you don't want to give
> permissions to directly work with the table, when certain basic
> arithmetic is required on the dataset or if the query needs to run
> inside a trigger or another procedure.
>
> In the case of get_companies, I would probably leave it for a normal
> query unless there is complex and specific conditions that must be
> met before a company can be considered.

There are a couple of reasons as you've identified above.
1. There's restricted access to the tables.
2. Only certain companies can be viewed depending on staff records.

Thanks again for the help. I've taken yours and Myles' suggestions and
applied them to my development database. After a couple of tweaks to
the code, it's working like a charm.

Thanks again,
Marcus