> I want to know how to do or simulate a
>
> where fieldname1 in("opt1","opt2","opt3")
>
> but with a varchar parameters
>
> so in the stored proc, i want a things like that:
>
> select fielname1,fieldname2
> where fieldname1 in (:paramstring)
>
> the ':' seem to generate an error, cause it's not "" and the
> operator 'IN' want ("something") with the quote(")
> how you do that??

I don't think this is possible as you are attempting.

You would have to do it like this:

val1 = <get first value>
val2 = <get second value>
val3 = <get third value>
val4 = <get fourth value>

if ( <my input contains one value> ) then
select ...
where fieldname1 = :val1
...
else
if ( <my input contains two values> ) then
select ...
where fieldname1 in ( :val1, :val2 )
...
else if ( <my input contains three values> ) then
select ...
where fieldname1 in ( :val1, :val2, :val3 )
...
else
if ( <my input contains four values> ) then
select ...
where fieldname1 in ( :val1, :val2, :val3, :val4 )
...
else
...


I have stored procedures that are literally thousands of lines of code in
order to case out numerous potential combinations of input parameters. It's
worth it to abstract all the complexity away from the client applications
and deal with it on the server.

Don't be bashful about large stored procedures...

HTH,
Jason Wharton
www.ibobjects.com