Recently, I had a problem with calling a stored procedure where the parameter was set to the Multi-value option. I admit that Microsoft did an enormous job in order to make this feature available within SQL Server Reporting Services (SSRS). However, in using the Multi-value option with a stored procedure call I found a few issues. The tricky part was when I used embedded SQL within a Dataset, the Multi-value option works just fine. I needed to make sure that the IN statement had been placed in the WHERE clause instead of an equals (=) sign.

Everything worked just fine at this point, but when I converted the T-SQL into a stored procedure, I was say… disappointed that its execution retrieved nothing. The stored procedure call worked for a single selected value only. When I selected more than one value the report raised an error, which does not make any logical sense to me. The error was “execution failed … “Incorrect syntax near ‘=’”. What can you say in this case? Most likely, the answer is nothing.

When I researched the problem, I found that I was not alone in this situation. Many developers described the problem, but not many valuable solutions were suggested. Logically thinking, when the Multi-value option is selected, then the report converts all selected values into an array. Therefore, for embedded SQL, the report compiler could easily convert an array into a string line and then run the query multiple times. A stored procedure cannot take the array as a parameter, so I needed to do this job manually. In other words – pass the parameter string to the stored procedure and then parse the string into a table.

Now, let’s go to the solution. Problem #1: how do you tell the report not to pass the array? We need to open the report’s code (right mouse click on a report, then select View Code). The report appears to us as XML. The next step is we need to find a place where the report assigns a value to the parameter. I would suggest opening search box (Ctrl+F) when typing the name of the parameter. You will find this in the QueryParameters XML section. The section looks like sample the below:

 <QueryParameters>
  <QueryParameter Name="@CategoryName">
     <Value>=Parameters!CategoryName.Value</Value>
  </QueryParameter>
 </QueryParameters>

Take a closer look at <Value>=Parameters!CategoryName.Value</Value>. Now I understand why the error message says “Incorrect syntax near ‘=’”. The array is not compatible with a stored procedure parameter. I needed to stop the report to generate the array. To fix the problem, we need to use the Join function. Let’s correct the Value line using a Join function like this :

 <Value>=Join(Parameters!CategoryName.Value, "~") </Value>.

The Join function has two parameters: the first parameter is the array and the second is delimiter. This way we convert an array into a parameter string. The next step is parsing the parameter string. I used CTE to accomplish this task, but if you are not comfortable with CTE or you do not like this technique, use a WHILE loop. The final decision is left up to you.

My example based on the Northwind database.

CREATE PROC GetProductByCategory
             @CategoryName nvarchar(4000)
AS
 DECLARE     @d char(1)
SET @d = '~';
WITH CSVCte (StartPos, EndPos) AS
( SELECT 1 AS StartPos, CHARINDEX(@d , @CategoryName + @d) AS EndPos
             UNION ALL
  SELECT EndPos + 1 AS StartPos , CHARINDEX(@d,@CategoryName + @d , EndPos + 1
) AS EndPos FROM CSVCTE
             WHERE CHARINDEX(@d, @CategoryName + @d, EndPos + 1) <> 0)
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock,
             Discontinued = CASE Discontinued WHEN 0 THEN 'NO' ELSE 'YES' END
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
             JOIN CSVCte ON SUBSTRING(@CategoryName, StartPos,EndPos-StartPos) = Categories.CategoryName

Conclusion

SQL Server Reporting Services is a great product, which gives us easy report building features and at the same time flexibility to modify the report code. I would not consider the problem calling a stored procedure where the parameter was set to Multi-value option an SSRS bug. As long as we can modify the report code, we still have control over the reports. As a final word, I would also like to add that .NET knowledge is required if you want to be a successful SQL Server 2005 (and up) DBA.