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

i want create report in ms sql server bids (ssms , visual studio). user enter list of email addresses parameter. @pemails 'foo@bluh.com', 'bar@meh.org', etc. these email addresses may or may not in table.

i can do:

and table.email in (@pemails)

and works, except need return email address if it's not found well. results like:

|email       |found in table|   |------------|--------------|   |foo@bluh.com|  y           |   |bar@meh.org |  n           | 

i thinking take list of values entered @pemails parameter , create temp table them, left join with, attempts have not worked out.

declare @pemails table (emailaddress varchar(255)); insert @pemails values (@reportparameter1);  select * @pemails 

the above works if single value put @reportparameter1, not if multiples in it.

i using sql server 2008. suggestions on how best proceed?

as has been stated, need kind of split function, analysis on performance of various methods split strings right way – or next best way excellent read. once have function, need define query parameter string, rather table:

so query become:

declare @pemails table (emailaddress varchar(255));  insert @pemails (emailaddress) select  value    dbo.split(@pemallstring); 

then go dataset properties, , instead of passing multivalue parameter @pemails dataset, instead create new 1 @pemailstring, , set value expression, should be:

=join(parameters!pemails.value, ",") 

this turns multivalue parameter single comma delimited string. seems pretty backwards need convert delimited string, split in sql, unfortunately don't know of better way.