'Where in' T-SQL implementation

Question

Hi guys,

I have a stored procedure that take many input 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

Answer 1

Arrays and Lists in SQL Server

Answer 2

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 FUCNTION WITH @PASS AND USE IT INSIDE A CURSOR

Answer 3

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.