'Where in' T-SQL implementation

Question

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

This has been asked SOOO many times on SO. You are aware that you can search other questions, and read their answers? — Adriaan Stander, Oct 21 '10 at 05:03
@astandar / @mitch a great approach here is to link to http://stackoverflow.com/questions/209686/passing-list-to-sql-stored-procedure or another canonical, so it shows up in the faq http://stackoverflow.com/tags/tsql/faq ... or update the wiki http://stackoverflow.com/tags/tsql/info to include it — Sam Saffron, Oct 21 '10 at 05:22
why not passing the userID's in a table-variable, e.g.@TuserID. inside the sp you could do an inner join @TuserID on @TuserID.UserID = userID... — Ice, Oct 22 '10 at 22:55

score 1 · Answer 1 · edited Nov 26 '13 at 17:27

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.

score 0 · Answer 2 · answered Oct 21 '10 at 05:01

0

Arrays and Lists in SQL Server

answered Oct 21 '10 at 05:01

Mitch Wheat

295,962
43
465
541

score 0 · Answer 3 · answered Oct 22 '10 at 18:33

0

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.

answered Oct 22 '10 at 18:33

HLGEM

94,695
15
113
186

'Where in' T-SQL implementation

3 Answers3