Subject [firebird-support] Re: Problem with query parameter
Author Svein Erling Tysvær
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.


-----Original Message-----
From: [] On Behalf Of sdbeames
Sent: 8. september 2009 08:09
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)
for select JOBNO, CLIENTNO
from JOBS
into :JobNo, :ClientNum

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?