Insert multiple value parameters into table

Question

I have a client who wants to drop a list of values into a parameter in Reporting Services, compare it to a table in her database and return the full list of values with a yes or no in the next field indicating whether the value is found in the database table. For example if her list is Duck, Horse, Chicken and only Duck exists in the table she wants the result to look like this:

Duck    Yes 
Horse   No 
Chicken No

She doesn't want to return only those values that match so a simple WHERE Animal IN (@ReportParameter1) isn't going to do it.

I can make this work for a single value parameter like this:

DECLARE @FarmAnimals AS TABLE (Animal varchar(50))

INSERT INTO @FarmAnimals VALUES (@ReportParameter1)

SELECT Animal
   ,'In Barnyard’ = CASE WHEN EXISTS
    (SELECT * 
    FROM tblBarnyard
    WHERE BarnyardAnimal = Animal)
    THEN 'Yes'
    ELSE 'No'
    END

FROM @FarmAnimals

But is it possible to loop through a multiple value parameter of unspecified length and create an INSERT INTO statement for each individual value? If this isn't possible I'm happy to tell her it can't be done but I can't think of a time I've found that something was impossible in SQL.

Answer 1

There's no need to do it in SQL, you can just write a custom code function to iterate through the selected parameter values to get the result that you want.

On the Report menu, access Report Properties... and select the Code tab. Insert the following code:

Function IsFieldSelected (fieldValue as String, ByVal parameter As Parameter) As String
  Dim Result As String
  Result = "No"
  If parameter.IsMultiValue Then
    For i As integer = 0 To parameter.Count-1 
      If (fieldValue = parameter.Value(i)) Then
          Result = "Yes"
      End If
    Next 
  Else 
      If (fieldValue = parameter.Value) Then
          Result = "Yes"
      End If
  End If 

  Return Result
End Function

then use this expression in the cell that you want the "Yes/No" to access the result:

=Code.IsFieldSelected(Fields!MyField.Value, Parameters!MyParameter)

Note that you are passing the parameter object here, not the Value property (so don't put .Value on the end). We access the Value property of the parameter object in the custom code function and compare it to the field value passed.

This will work for both single- and multi-value parameters.

Answer 2

You can do this using the STRING_SPLIT function in SQL Server.

--Placeholder table
DECLARE @ExistingValuesInTable TABLE (Val VARCHAR(255)) ;
INSERT INTO @ExistingValuesInTable (Val) VALUES ('Duck') ;
--
DECLARE @UserInput VARCHAR(255) = 'Duck, Horse, Chicken' ;
SELECT  ss.value,
        CASE WHEN evit.Val IS NULL THEN 'No' ELSE 'Yes' END AS AlreadyExists
FROM  STRING_SPLIT(@UserInput, ',') AS ss
  LEFT OUTER JOIN @ExistingValuesInTable AS evit ON ss.value = evit.Val ;