<svein.erling.tysvaer@...> wrote:

>
> A workaround would be
>
> select * from TABLE
> where FIELD = coalesce(:param, FIELD)
>
> This will never use any index, but there's no way to use indexes with WHERE clauses like this anyway.
>
> HTH,
> Set

> -----Original Message-----
> I'm trying to do something like this:
>
> select * from TABLE
> where FIELD = :param or :param is null

Thanks Sven, I also wanted something similar but I can't get this to work in an SP.

create procedure TEST(CLIENT_NO varchar(6))
returns(JobNo integer, ClientNum smallint)
as
begin
for select JOBNO, CLIENTNO
from JOBS
where CLIENTNO = coalesce(:CLIENT_NO, CLIENTNO)
into :JobNo, :ClientNum
do
suspend;
end^

It works for non-null CLIENT_NO parameters, but gives a
conversion error from string " "
error when the parameter is null.

It works fine outside an SP. I'm using V1.55.

Any obvious error here?