The one thing that puzzles me, is that your input parameter is varchar(6), whereas you return a smallint for the same field. Do you get the same error if you define both client_no and client_num to be the same type as your clientno field? If the problem persists, have you tried declaring a variable within the stored procedure, assign the parameter to the variable, explicitly setting the variable to NULL if the parameter is empty and use the variable in your FOR SELECT? I must admit I use SPs very rarely, and have no experience with fixing them. Luckily, others on this list are far more experienced in this field.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of sdbeames
Sent: 8. september 2009 08:09
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Problem with query parameter

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