title: SELECT statement using NOT IN against a text string
body: <p>I want to write a <code>SELECT</code> statement that returns value but excludes any that appear in a text list</p>
  
  <p>So I have a text list <code>COL1, COl2, COL4, COL5</code> (called <code>myString</code>)</p>
  
  <p>And I want to say...</p>
  
  <pre><code>SELECT COLUMN_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'INTERFACE' AND COLUMN_NAME NOT IN(myString);
  </code></pre>
  
  <p>How would I do that?</p>
  
  <p>I am trying to solve this problem in a stored procedure and though that just getting back to basics would help me understand. My SP code looks like this...</p>
  
  <pre><code>INSERT INTO @excludeFieldTable VALUES ('CASENO'),('INTLINENO'),('INTMEMBNO'),('INTSTATUS'),('RECTYPE'),('EFFDTE'),('INTERR'),('INTERR2'),
  ('INTWARN'),('AUTHORISED'),('EXCLUDE'),('UPDATED'),('INTMEMBNO2'),('INTSTATUS2'),('ALTKEY');
  
      INSERT INTO @intColumnList SELECT DISTINCT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='INTERFACE' AND COLUMN_NAME NOT IN (SELECT DISTINCT(excludeFieldName) FROM @excludeFieldTable);
  </code></pre>
  
  <p>but the <code>NOT IN</code> against the <code>@excludeFieldTable</code> is not evaluating for some reason (so <code>intColumnList</code> still has the values 'CASENO', 'INTLINENO' etc)</p>