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:
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
DECLARE @d char(1)
SET @d = '~';
WITH CSVCte (StartPos, EndPos) AS
( SELECT 1 AS StartPos, CHARINDEX(@d , @CategoryName + @d) AS EndPos
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
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.