Subject Re: [firebird-support] In firebird store_procedure IN clause is not giving the desired result
Author Martijn Tonies

> I am having one problem in store-procedure. I am passing a variable
> value as a input parameter in a store-procedure and that value is used
> in following statement (:varValue and its value is 'FL000001','FL000003')
> Delete fileid from container where fileid in (:varValue)
> I can not get the desired result.
> If i use the hard coded value then it works fine. for ex.
> Delete fileid from container where fileid in ('FL000001','FL000003')
> What could be the problem?

Because what you're doing is wrong. A variable holds a single value. In
your case, the statement will become (more or less) the following:

Delete fileid from container where fileid in (' ''FL000001'',''FL000003'' ')

I've quoted your string (and doubled the single quotes to escape each

See the difference? You're passing a string value to a parameter, and I
the parameter with its actual value.

In other words: you cannot use parameters like this.

Try using a dynamic statement by using EXECUTE STATEMENT.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
My thoughts:
Database development questions? Check the forum!