Subject Re: Problem with query parameter
Author sdbeames
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

create procedure TEST(CLIENT_NO varchar(6))
returns(ClientNum smallint)
if (CLIENT_NO = '') then

for select CLIENTNO
from JOBS
into :ClientNum

Thanks all.

--- In, 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: [] On Behalf Of sdbeames
> Sent: 8. september 2009 08:09
> To:
> 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?