How to pass a value to an 'IN' clause in a stored procedure?

[dwhdwh]

I want to be able to pass a parameter into a stored procedure to be used in an IN clause.

An example would be:
Select * from Members
Where State_Id IN ('NJ','NY','MD')

So I want to pass the states as a variable. Anyone know how to do this in either SQL Server or Oracle.

Thanks,
Dave.

 

[tlbroadbent]

Here is one solution. Create the stored procedure to accept the parameter.

Create procedure sp_myproc @inparam varchar(512) As

Declare @sql varchar(1024)
Select @sql=
"Select * from Members
Where State_Id IN (" + @inparam + &quot"
Exec(@sql)
Go

In your client program (VB?) create the query to execute the stored procedure.

Exec sp_myproc "'NJ','NY','MD'"

This is a rather simplistic but workable solution. Terry

X-) "Life would be easier if I had the source code." -Anonymous