Hi Mauleen!

There's no bug, you just assume that one parameter may be an entire set,
and that is not true. If you want to pass two parameters, you ought to
write something like

Delete from container where fileid in (:new_para1, :new_para2);

and pass each value as a separate parameter.

As others have said, EXECUTE STATEMENT is one of the alternatives if you
really don't know the number of parameters. Then you would actually have
something like:

declare variable
MyStatement Char(100);
...
MyStatement = 'Delete from container where fileid in ('
MyStatement = MyStatement || :new_para ||')'
EXECUTE STATEMENT MyStatement

This is a bit more dangerous since the statement is built and executed
on the fly. E.g. if someone wanted to sabotage and knew (guessed, or
just wanted to check whether) you'd used EXECUTE STATEMENT, then he
could just call your procedure with the following parameter:
'1) or (1=1', which your procedure would transform into

MyStatement = 'Delete from container where fileid in (1) or (1=1)'

with the result that all records were deleted. A safer option would be
to parse your string in a loop and delete each fileid separately.

HTH,
Set

mauleen2001 wrote:

> Hi,
>
> 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'
>
> so that, i am passing FL002','FL003 from the front end
> then it internally converts into 'FL002","FL003'
>
> so how can i evaluate this statement?
>
> Mauleen