Hi,

Maybe someone will come up with a more elegant solution, but if not,
here's a simple trick.
Pass your list of values as a string, separating values with commas
(take care not to add extra spaces between values etc.). Suppose the
parameter name in the procedure is par_list and the string
"7,9,34,2,372" was passed as the actual argument.
Then, inside the procedure, you add extra delimiters at the beginning
and end of the parameter:

par_list = ','||par_list||',';

to make sure each value has commas on both sides (we'll need it to
prevent e.g. 7 to be matched with 17).
Then you compose your select this way:

select field1, field2 from table1
where :param_list like '%,'||field3||',%'

If field3=34, the condition is satisfied (',7,9,34,2,372,' is like
'%,34,%'). If, on the other hand, field3=37, the condition is not
satisfied (',7,9,34,2,372,' is not like '%,37,%').
You can use :param_list containing ','||field3||',' as well.

I know it's not very sophisticated, but I've used it on some occasions
and it does the job. However, alternative solutions are welcome.

regards
Tomasz

on 2012-01-17 17:44, Net Newbie wrote:

> (my previous post appeared under another thread, as I did reply. I write
> again to start new thread - sorry if someone gets 2 times).
>
> how to create a stored procedure having a list of values as input parameter?
> I need to retrieve result for query looking like
>
> select field1, field2 from table1 where field3 in (1, 2, 3, 4);
>
> is there a way to give list of values as input parameter? but sometimes
> there could be just one value for input. so it should work also for
> select field1, field2 from table1 where field3 in (1);
>
> thank you.
>

--
__--==============================--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==============================--__