I want to pass the 'IN' clause value into a stored procedure 1

[dwhdwh]

Does anyone know how to pass a value into a stored procedure which can be used in an 'IN' clause. It works fine passing 1 value, but I cannot get it to work passing in multiple values.

Example:
Select * from Members
Where State_Id in ('FL', 'MA')

How can I pass 'FL', 'MA' as the parameter. I do not want to specify a separate parameter for each value as I may have from 1 to 50 values possible.

Thanks,

 

[sem]

You may use ref cursor opened for a string literal. In this case you may build select statement on the fly. Though this statement will be reparsed every time. You may also use some [comma] delimited string as a parameter and instr function in select - works on earlier versions, but also not very efficient approach (not using index if one exists, delimiter must not be contained in possible values).

 

[listener22]

Two other ways:

1. Named notation. You can include only the parameters you want in
the parameter list; any parameter with a default value can be left out
of the call. Named notation means using this format:
formal_parameter_name => argument_value.
ex. i_am_calling_your_select ( state1_in => 'FL');

2. Overloaded modules. In your case, if you want 1-50 states,
you would write 50 different functions, each with its own list
of arguments. Well that would be tedious, although you could do
a lot of copying. But if it were only 1-10 states, that wouldn't be
so bad. Jim

oracle, vb