Question: When I try this query it works:

```
SELECT *
FROM tbl_Users
WHERE UserId IN (555, 3695, 8787))

```

But when I put it in stored procedure it get value only if I pass one ID. If I pass more ID's 

I got nothing returned from database:

```
CREATE PROCEDURE myStoredProcedure
    @m_UserIdList varchar(500)
AS    
    SELECT u.*, p.*
    FROM tbl_Users u 
    INNER JOIN tbl_Bunker b 
    ON u.BunkerId = b.Bunker
    WHERE u.UserId IN (@m_UserIdList)

```

Answer: 
Yes, it is treating @m\_UserIdList as one single value and comparing that to u.UserId. You need to split out the values and test them separately. Oded beat me to the punch, but he is right -- you should use a table-valued param. Here is the link from Oded:

<http://www.sommarskog.se/arrays-in-sql-2008.html>

This site offers a bunch of different approaches to this problem:

<http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm>

One of the approaches would be to use dynamic SQL, however you should be aware of the dangers of doing this. Read this first:

<http://www.sommarskog.se/dynamic_sql.html>

If you decide to go that route, something like this could work:

```
DECLARE @SQL varchar(600)

SET @SQL = 
'SELECT u.*, p.*
FROM tbl_Users u
    INNER JOIN tbl_Bunker b ON u.BunkerId = b.Bunker
WHERE u.UserId IN (' + @m_UserIdList+ ')'

EXEC(@SQL)  

```
Since it wasn't mentioned, you can use the XML data type.

```
BEGIN
DECLARE @xml XML
SELECT @xml = '<n>1</n><n>2</n><n>3</n>'

SELECT t.n.value('.','int')  FROM @xml.nodes('*') as t(n)
IF 1 IN (SELECT t.n.value('.','int')  FROM @xml.nodes('*') as t(n))
    PRINT('Yep')
IF 4 NOT IN (SELECT t.n.value('.','int')  FROM @xml.nodes('*') as t(n))
    PRINT('It Works')
END

```

It's not as efficient as table valued columns but it's easier to setup/use for the "Caller".

If the caller already has an array or list of elements to convert them to XML he just has to do a String.Join with "</n><n>" as the delimiter then if the resulting string is not empty prepend "<n>" and append "</n>"