Hi Martin!

First, I'm not answering from an SP point of view, but rather a general
optimization point of view (mostly based on experience with Fb 1.5,
though I'd be pleasantly surprised if this has changed in Fb 2.0).

For OR statements to use indexes, both sides of the OR has to have an
index. Since :departmentcode is unlikely to have any index, your query
is likely to get a plan with one of the table aliases using NATURAL. If
the table is big this will be an issue, it may not be too big a deal
otherwise.

If you really only have a handful of optional fields, you could do

if (departmentcode is null) then
begin
select bla
from bla
join depts d
end
else
begin
select bla
from bla
join depts d
where d.code = :departmentcode
end

which I would expect to be considerably faster when run against a
particular departmentcode if you have lots of departments (and the
department in question is small) and an index on d.code. If
departmentcode is null, well, then it will be slow anyway.

HTH,
Set

martinthrelly wrote:

> hi there
>
> i am writing a reporting application and am considering how best to
> get the data from the firebird 1.5 database. my reports all contain
> user selectable filters. as far as i can see my 2 options are SPs or
> to construct dynamic SQLs. my preferred route is definetely SPs,
> however i have a question please:
>
> if i write an SP with an OPTIONAL filter like this:
>
> (input param) DepartmentCode INTEGER;
>
> ...
>
> select bla
> from bla
> join depts d
> where ((:departmentcode is null) or (d.code = :departmentcode))
>
> does the last line here mean the SP is not optimised as i am
> dynamically changing the where clause depending upon whether the
> user has specified a filter or not?
>
> is SPs the way to go do you think?
>
> thanks