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

Question

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 '[email protected]', '[email protected]', 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|  
|------------|--------------|  
|[email protected]|  Y           |  
|[email protected] |  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?

Answer 1

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.