--- In firebird-support@yahoogroups.com, "martinthrelly"
<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?

Well you wont be able to do it like that. You can dynamically build a
varchar that looks like that and use execute statement to run it to
achieve the same effect.

The problem you will get in terms of performance is that there is no
way of deciding the best plan for the query ahead of time. Every time
you run the query, the where clause could be different, so different
indices may be involved or it may need to go natural if no index is
available. Because of this fact, the prepare time for the query will
be a cost for every execution.

But given you are dealing with a reporting application and the prepare
time for such a query is likely to be sub 10ms, it is probably
insignificant in your case.

Adam