3

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?

Dave Lartigue
  • 33
  • 1
  • 5
  • 1
    You will need a split function to put these into the table. `Insert Into @pEmails (EmailAddress) Select * From fn_split(@ReportParameter1,',') – SS_DBA Nov 10 '16 at 18:04
  • 2
    [Split strings the right way – or the next best way](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) – GarethD Nov 10 '16 at 18:05
  • Thank you! This was the pointer I needed! – Dave Lartigue Nov 10 '16 at 18:44
  • Still having an issue. When I use the "Allow multiple values" checkbox for my parameter, if I enter the values as such: val1 val2 val3 it shows up in the parameter field as "val1, val2, val3" yet the split function won't work (says it has too many parameters). If I enter only val1 or if I enter as "val1, val2, val3" on a single line, it works. I don't know what format BIDS is passing the one-per-line multiple values as. – Dave Lartigue Nov 10 '16 at 19:30

2 Answers2

5

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, ",")

enter image description here

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.

GarethD
  • 68,045
  • 10
  • 83
  • 123
  • Thank you! To be more precise, here were the elements needed: 1) splitstring function added 2) the main query: DECLARE `@pEmails` TABLE (EmailAddress varchar(255)); INSERT `@pEmails` (EmailAddress) SELECT * FROM dbo.splitstring(`@pEmailString`); Select EmailAddress from `@pEmails` 3) The internal parameter: @pEmailString = =Join(Parameters!pEmailList.Value, ",") 4) The external (user-entered) Parameter: `@pEmailList`, text, allow multiple values Works great. Thanks! – Dave Lartigue Nov 11 '16 at 14:36
0

Here are some learnings on this topic (standing on the shoulders of the information elsewhere in this thread).

Set a parameter (select 'multiple values' checkbox):

InputList

Establish dataset query:

SELECT * 
INTO #InputTemp 
FROM  STRING_SPLIT(@InputListJoin, ',')

SELECT value as ValueName
FROM #InputTemp T2
WHERE NOT EXISTS (
    SELECT MyValue
    FROM MyTable T1
    WHERE T1.MyValue = T2.value
    )

Establish dataset parameters:

Name: @InputList  |  Value: [@InputList]
Name: @InputListJoin  |  Value(expression): =Join(Parameters!InputList.Value,",")

The element names can be changed as needed.

Somewhat on topic, other details that might be helpful:

[@InputList.IsMultiValue] --> true/false whether your parameter is multi-value (not whether there are multiple values)
[@InputList.Count] --> count of items in input list (excludes blank lines)
=Parameters!InputList.Value(2) --> return third value from list (counting from zero)
snyderj
  • 811
  • 7
  • 6