The real problem for me arose because I want to feed in between
0 & 4 text parameters to one of several (depending on the report the
client want to produce) selectable SPs, where the params can
represent a number or text, or be empty or null. I needed a WHERE
statement that can handle the unknown input parameter combinations.

As svein suggested, I eventually found the following works OK
(without any extra brackets). It seems that IB_SQL treats an empty
param field as '' rather than NULL, which was causing me much
confusion.

create procedure TEST(CLIENT_NO varchar(6))
returns(ClientNum smallint)
as
begin
if (CLIENT_NO = '') then
CLIENT_NO = NULL;

for select CLIENTNO
from JOBS
where CLIENTNO = coalesce(:CLIENT_NO, CLIENTNO)
into :ClientNum
do
suspend;
end^

Thanks all.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> 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?
>