At 04:09 PM 8/09/2009, you wrote:

>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?

Small but crucial: use of a variable's value in a parameter is an expression, so it needs brackets:

where CLIENTNO = coalesce((:CLIENT_NO), CLIENTNO)

./heLen