Subject Re: [firebird-support] Bug in In-put parameter of store-procedure
Author Helen Borrie
At 07:48 PM 9/06/2007, you wrote:
> I am passing a value FL002,FL003 in store-procedure which is used in
>IN clause of sql statement
>for example
> Delete from container where fileid in (:new_para);
>internally it converts into 'FL002,FL003'
>but it should be 'FL002','FL003'

Well, it's your bug, not a bug in PSQL. A parameter has to be a
single constant element. Prior to 2.1, the PSQL language cannot make
sense of a variable list of values.

What you need to do is pass your list as a string with comma
separators. If you are using Firebird 1.5 or higher, you can use
this string to include in a string that you construct using
concatenation, and pass to the EXECUTE STATEMENT.

Another way to approach is to write a 'breakapart' sub-procedure that
you call in a loop, breaking off the leftmost value in your list each
time and returning it to variables in the main procedure for the
latest and the balance of the string. Then your SQL fetches each row
one by one using the latest value in a simple search clause. (This
is the one I prefer.)