title: SQL Server - search comma separated list against all possible values
body: <p>Scenario: I have a SSRS report which has a bunch of filters. One of those filters is for "Area", I get the areas like so:</p>
  
  <pre><code>SELECT DISTINCT Area FROM tblArea ORDER BY Area
  </code></pre>
  
  <p>I have a default value of "All" for the parameter that gets its available values from that, so the output would be like:</p>
  
  <p>All
  Area1
  Area2
  China
  US
  etc</p>
  
  <p>In the report they could select "All" which when passed to the main stored procedure the parameter would look like:</p>
  
  <p>All, Area1, Area2, China, US, etc</p>
  
  <p>Now here is the issue: In the table which I query the Area column might have data like:</p>
  
  <pre><code>Area
  --------
  Area2,US
  </code></pre>
  
  <p>And the query is</p>
  
  <pre><code>SELECT * FROM tbl WHERE TPC IN (@Area) -- All,Area1,Area2,China,US,etc
  </code></pre>
  
  <p>And this would not find the record I am looking for.</p>
  
  <p>In short, how do I compare each value in a comma separated column against a comma separated parameter?</p>
  
  <p>Thanks in advance,</p>