> there's
> one particular thing that seem to have a bad impact on the execution
> plan.

Plan is chosen during Prepare, i.e. when parameter values are unknown yet ...

> These SPs accept several arguments that may be used to delimit the set
> of records to operate on. Basically, an argument may be either NULL to
> mean "don't consider it" or a particular value for a field.

... but if you prefer do use indexes always then it is pretty simple, try this:


CREATE OR ALTER PROCEDURE proc$testbes3 (p_dat DATE, p_id INTEGER)
RETURNS (what INTEGER) AS
DECLARE VARIABLE dat_min DATE;
DECLARE VARIABLE dat_max DATE;
DECLARE VARIABLE id_min INTEGER;
DECLARE VARIABLE id_max INTEGER;
BEGIN
dat_min='0001-01-01';
dat_max='9999-01-01';
id_min=-2147483648;
id_max= 2147483647;
IF (p_dat IS NOT NULL) THEN BEGIN
dat_min=p_dat;
dat_max=p_dat;
END
IF (p_id IS NOT NULL) THEN BEGIN
id_min=p_id;
id_max=p_id;
END

FOR SELECT what
FROM testbes tb
WHERE id BETWEEN :id_min AND :id_max
AND dat BETWEEN :dat_min AND :dat_max
INTO what DO
SUSPEND;

END


(Better avoid that method for huge tables, but around million rows
you should still be o.k.)


Ivan
http://www.volny.cz/iprenosil/interbase/