Sorry, I forgot NULL fields

for select...
from....
where (FIELD1 = coalesce(:PARAM1, FIELD1) or coalesce(:PARAM1, FIELD1) is NULL) and FIELD2 = ....
into....

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvaer
Sent: 23. august 2008 09:24
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Optional FOR SELECT-WHERE parameters

You can of course do

for select...
from....
where FIELD1 = coalesce(:PARAM1, FIELD1) and FIELD2 = ....
into....
do...

but note that no index can then be used for FIELD1, so you probably do
not want to do this to every field for large tables.

HTH,
Set

sdbeames wrote:

> Hi all,
> is there a way to ignore certain input parameters to my selectable
> S.P. that are used in a FOR SELECT loop WHERE filter, if they are null?
>
> ie if PARAM1 IS NOT null.....
>
> for select...
> from....
> where FIELD1 = :PARAM1 and FIELD2 = ....
> into....
> do...
> ---------------------------------
> or if PARAM1 IS null....
>
> for select...
> from....
> where FIELD2 = ....
> into....
> do...
>
> I'm unsure if a FOR EXECUTE STATEMENT approach would be wise with a
> large FOR SELECT statement inside.
>
> FB1.5
> Thanks,
> Steve

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links