title: Turning a multi-value parameter into a temp table in SQL Server Business Intelligence Development Studio
body: <p>I want to create a report in MS SQL Server BIDS (SSMS and Visual Studio). The user would enter a list of email addresses as a parameter. So @pEmails would be 'foo@bluh.com', 'bar@meh.org', etc. These email addresses may or may not be in a table.</p>
  
  <p>I can simply do:</p>
  
  <p><code>and Table.Email in (@pEmails)</code></p>
  
  <p>and that works, except I need to return the email address if it's NOT found as well. So the results would be something like:</p>
  
  <pre><code>|email       |found in table|  
  |------------|--------------|  
  |foo@bluh.com|  Y           |  
  |bar@meh.org |  N           |
  </code></pre>
  
  <p>I was thinking I could take the list of values entered as the @pEmails parameter and create a temp table with them, which I could then left join with, but my attempts to do so have not worked out. </p>
  
  <pre><code>declare @pEmails table (EmailAddress varchar(255));
  insert into @pEmails values (@ReportParameter1);
  
  select
  *
  from
  @pEmails
  </code></pre>
  
  <p>The above works if only a single value is put into @ReportParameter1, but not if multiples are in it. </p>
  
  <p>I am using SQL Server 2008. Any suggestions on how best to proceed?</p>