Turning a multi-value parameter into a temp table in SQL Server Business Intelligence Development Studio

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.

I can simply do:

and Table.Email in (@pEmails)

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:

|email       |found in table|  
|------------|--------------|  
|foo@bluh.com|  Y           |  
|bar@meh.org |  N           |

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.

declare @pEmails table (EmailAddress varchar(255));
insert into @pEmails values (@ReportParameter1);

select
*
from
@pEmails

The above works if only a single value is put into @ReportParameter1, but not if multiples are in it.

I am using SQL Server 2008. Any suggestions on how best to proceed?

As has been stated, you need some kind of split function, for analysis on the performance of various methods Split strings the right way – or the next best way is an excellent read. Once you have your function, you then need to define your query parameter as a string, rather than a table:

So your query would actually become:

DECLARE @pEmails TABLE (EmailAddress varchar(255));

INSERT @pEmails (EmailAddress)
SELECT  Value
FROM    dbo.Split(@pEmallString);

Then go to your dataset properties, and instead of passing the multivalue parameter @pEmails to the dataset, instead create a new one @pEmailString, and set the value as an expression, which should be:

=Join(Parameters!pEmails.Value, ",")

This turns your multivalue parameter into a single comma delimited string. It seems pretty backwards that you need to convert it to a delimited string, only to then split it in SQL, unfortunately I don't know of a better way.