> Is there any way to parameterize this WHERE clause so it only gets
> prepared once and I don't have to re-enter the SQL?

> If I have the following parameterized statement:
> ... WHERE ACTIVITYKEY=:AKEY AND AMODE=0 ...

> and I leave the ACTIVITYKEY param empty, will it be ignored?

This is one of those really nasty bits of SQL/IB/FB. What you may be
tempted to try (I know that I have in the past) something like...

WHERE (ACTIVITYKEY=:AKEY) OR (:AKEY IS NULL)

But IB/FB wont let you get away with this in a DSQL statement, it will
complain about not knowing the datatype for AKEY (or something like
that).

You can do this sort of thing inside stored procedures - because the
parameter is generally a declared variable that has a specific data
type. And this brings up a couple of possibilities...

You could change your where clause to use a UDF or stored procedure
that takes care of this situation. I have exactly this setup in some
of my applications - where I compare two input values of specific data
type and return true according to my requirements.

So you where clause becomes something like...

WHERE ((SELECT RESULT FROM MY_TEST_PROC(
THE_TABLE.ACTIVITYKEY, :AKEY)) = 1)

The procedure itself becomes something like...
CREATE PROCEDURE MY_TEST_PROC(
FIELDVAL INTEGER, /* or whatever */
PARAMVAL INTEGER /* a matching whatever */
)
RETURNING( RESULT SMALLINT )
AS
BEGIN
RESULT = 0; /* default to false */
IF( PARAMVAL IS NULL ) THEN
RESULT = 1;
ELSE
BEGIN
IF( FIELDVAL = PARAMVAL ) THEN
RESULT = 1;
END
SUSPEND; /* so I can use select */
END


I have variations of this setup in my apps according to the different
sorts of requirements (for example sometimes I want an equal
comparison to find null equals null).

The downside... This sort of setup, whether used by stored procedure
or UDF prevents IB/FB from performing any optimisation on the original
select statement. So if, for example, activitykey was available in an
index then the normal situatin with equals comparison would use that
index to optimise the select. Once you stick in a UDF or stored proc
you prevent IB/FB from being able to make that assumption.

From the names given I am guessing ACTIVITYKEY is a key value, in
which case you will be expecting the select to normally optimise via
the primary key index. In this case my solution does not help you.

This leaves you with either putting up with reprepare OR moving the
entire select into a stored procedure (which for live datasets means
setting up procedures for insert/edit/delete as well). This
arrangement is really only efficient for small result sets.

hth

--
Geoff Worboys
Telesis Computing