hi adam thanks for your reply.

i wrote a previous reporting app and the syntax i referred to does
work. i used a set of preset filters for each report and
conditionally ran them in the WHERE clause depending upon whether
the input parameter value was null or not. so it does work. where
the user wanted to define multiple filters such as "WHERE code in
(1, 3, 4, 6, etc) i appended this onto the end of the SP call.

i dont think i can use execute statement with firebird 1.5.

sounds like i should stick with my existing method then. its just
good to get some confirmation from you guys.

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> --- 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
>