A variable of a standard type is a "scalar" variable by definition. That is, it holds a single value. SQL cannot, and will not, arbitrarily assume it holds multiple values sometimes. SQL just can't, because if it did processing would be random and inconsistent.
For example, a column in your table could actually hold this value:
'HR','EAGLE'
quotes and all. If so, and you specified this in a query:
where CodesViewName in (@codesViewList)
You'd expect SQL to match that combined value, quotes and all, and so it does. SQL will not split the value apart and look separately for HR and EAGLE (without quotes) because it has no reason to, indeed is not allowed to arbitrarily decide to split single values.