'Where in' T-SQL implementation

I have a stored procedure that take many input 开发者_Python百科parameters including an @userID.

Now, in the core of the SP I select from a view using the following Where close :

Where userID = @userID

This works fine. Now I want to pass multiple users to be able wo query it like this :

where userID in (1,2,...)

How can I pass the value 1,2 from one input parameter ?

Thanks

Create a function split:

create FUNCTION [dbo].[Split]
(
       @List nvarchar(2000),
       @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(

       Id int identity(1,1),
       Value nvarchar(2000)
)
AS
BEGIN

   While (Charindex(@SplitOn,@List)>0)
   Begin
           Insert Into @RtnValue (value)
           Select  Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
           Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
   End

   Insert Into @RtnValue (Value)
           Select Value = ltrim(rtrim(@List))

           Return

--select Value from dbo.Split('item1, item2, item3',',')
END
GO

And call the function with @PASS and use it inside a cursor.

Arrays and Lists in SQL Server

Create an input parameter which is a table varaible and send in the array as a table that you join to instead of using in. Read about this in BOL as it can be a little tricky to set up.