This is to perform the where clause
select *
from tbl
where id in (1,2,3)
but with the values to test as a paramter.

so we are looking to test
declare @csv varchar(20)
select @csv = '1,2,3'

Of course 
where id in (@csv)
does not work because it will test the parameter as a single value - and try to convert it to an int
hence
where 1 in (@csv)
gives an error because @csv cannot be converted to an int
where '1' in (@csv)
returns false
where '1,2,3' in (@csv)
returns true - and is the only statement that does (trailing white space excluded).

There are several ways of dealing with the csv parameter - which to use depends on the situation.


Simple where clause

Select *
from tbl
where ',' + @csv + ',' like '%,' + convert(varchar(20),id) + ',%'
is a simple method but it cannot use any indexes so is inefficient for larcge tables.
Note the addition of leading and trailing ","'s to cater for multi digit values.


Dynamic sql

exec ('select * from tbl where id in (' + @csv + ')')

This can use an index on id in tbl but requires that the user has permission on tbl and can also suffer from injection.
I would not recommend this method.



Function to return resultset of values to test


see fn_ParseCSVString for the function
	
select tbl.*
from tbl
join dbo.fn_ParseCSVString(@csv, ',') f
on tbl.id = f.s

This can use an index on id in tbl so is probably more efficient at the expense of an extra object.
The resultset returned by the function could be included in the SP to populate a table variable or temp table.