Can't figure the IN statement

Transact-SQL
1

declare @codesViewList varchar(100) = '''HR'',''EAGLE'''

SELECT CodesViewName, SiteCode
FROM dbo.tblCodesView
where CodesViewName in (@codesViewList)

print @codesViewList

if I paste the print in.... 'HR','EAGLE'
SELECT CodesViewName, SiteCode
FROM dbo.tblCodesView
where CodesViewName in ( 'HR','EAGLE')

It works.....

2

You can't do

where MyColumnName in (@MyParameterList)

Similar question to one just recently asked:

Or you can use a Splitter Table Valued Function to convert the @List into a one-value-per-row and JOIN that to your table.

3

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.

1 Like