using sqlcommand with multiple ids

So this works great:

select name from users where id = @id

However, that only selects 1 guy. Say I have 3 IDs instead, normally the SQL would look something like this (without using parameters)

select name from users where id in (4,6,9)

However, it doesn't seem to work when I write

select name from users where id in (@IDs)

and insert a list into @IDs, like this

cmd.Parameters.AddWithValue("@IDs", userIDs);

Is there any way to do what I'm trying to? It's important to note that the sql I'm calling is (and has to be) a stored procedure.

Solution

There are two ways to do this. The first is by passing a string to a stored procedure and then adding it to a dynamic query:

-- @IDs  = '4,6,9'
DECLARE @MyQuery nvarchar(max) 
SET @MyQuery = N'SELECT name FROM users WHERE id IN (' + @IDs + ')'
EXEC(@MyQuery)

On the other hand, if you are using SQL Server 2008 or later, you can use a table-valued parameter (this is my preference).

First, create a user-defined table type:

CREATE TYPE IDList AS TABLE (
  id int
)

THEN, use the user defined type as the type for your parameter:

DECLARE @IDs IDList 
INSERT INTO @IDs (ID) VALUES (4),(6),(9)
SELECT name FROM users u INNER JOIN @IDs i WHERE u.id = i.id

If you are using .NET to consume a stored procedure, you can find sample code for user-defined SQL types on MSDN.