Passing a PARAMETER to a IN CLAUSE

Trying to pass a parameter to a "in clause" can be troublesome, because what you really want to do is to pass an array of an indefinite number of parameters and not a single parameter.

There are 2 solutions for this. Either you pass a table-value type, or you use a function to convert the nvarchar to a table.
Here is how such function might look like:

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END

For more information about this error and credits for the code here check out:
http://www.sommarskog.se/arrays-in-sql.html