First, it is a clause, not cause ^_^
Second, the "in" clause works off of a set of inputs, that is a table like structure with one column. What you have in your "in" clause is one item. A varchar.
If it was written like this:
WHERE category.CategoryID IN (2,3)
That would work. You have:
WHERE category.CategoryID IN ('2,3') which won't even match by type.
If you must use a csv to pass in your list, then you will need to convert it into a table column. You can use a csv parser Table Valued Function for this. Many examples exist and I will link one at the bottom.
This can be used as follows:
WHERE category.CategoryID IN ([dbo].[GetMyTable](@MultipleCategory))
here is the link:
SQL function to return CSV as a table[
^]
The function steps through the string one char at a time so it quite scaleable