NOT IN (@StrVariable) not working

Transact-SQL
1

I need to send a value to a sql statement that will hold a list of fields to include or exclude from my results.

DECLARE @ListOfFields NVARCHAR(500),
@MyField NVARCHAR(100)
SET @ListOfFields = '(''Test1'', ''Test2'', ''Test3'')'
SET @MyField = 'Test1'

IF @MyField NOT IN (@ListOfFields)
print 'does not exist'
ELSE
print 'exists'

My Test1 should be in my list of fields. Yet it doesn't match. I have tried to reformat the string a dozen different ways, and I cannot get 'exists'. Any Ideas what I am missing here on how to format the string list of field names?

2

That's because @ListOfFields is a string and not a comma separated list. You'll need to use a string splitter function. There are many out there that you can google for.

3

This will probably work as a quick & dirty solution

IF @ListOfFields NOT LIKE '%"' + @MyField + '"%'

but if you actually want to "process" the @ListOfFields then you need a splitter function as Tara described (or, I think, SQL 2016 which has that as a native function)