Using multi-valued parameters together in main query

Question

I have a report that uses two multi-value parameters in the query.

@garageId and @createdYear are both set as multi-value parameters with a default value of zero(0).

If I run it in SQL Server Management Studio, like below, I get the expected results.

declare @garageId int = 121212;
declare @createdYear int = 0;

SELECT DISTINCT(ml.methodID) methodID, methodTitle 
FROM methodList ml
INNER JOIN methodChallenge mc ON ml.methodID = mc.methodID
WHERE type = 1
AND  (mc.garageId IN(@garageId) OR @garageId IN(0))
AND  (ml.createdYear IN (@createdYear) OR @createdYear IN(0))
ORDER BY methodTitle

However, I go through the SSRS interace, and select multiple values from each parameter column, I get this error:

"An expression of non-boolean type specified in a context where a condition is expected, near ','."

It works fine if I only select one row from each parameter column, but I need to be able to select multiple rows.

How do you correctly format the query to accept all the possible choices between multi-valued paramets?

Thanks!

Can you allow the parameters to be `NULL`, make that the default value and check for that in your query? On a side-note: the `DISTINCT` works on your whole `select` list, not only on `ml.methodID`, is that the supposed way of working? — NickyvV, Sep 19 '16 at 19:14
How will you set multiple value to @garageId which is declared as an INT? — S3S, Sep 19 '16 at 19:37
Change your last condition to `0 IN (@createdYear)` since your **PARAMETER** can have the multiple values (not the 0). — Hannover Fist, Sep 19 '16 at 20:04
@scsimon - I think the DECLAREs were just for testing. SSRS will convert a list of numbers to `1, 2, 3, 4` where the parameter occurs in the query when running the report. i.e. `mc.garageId IN(1, 2, 3, 4)` — Hannover Fist, Sep 19 '16 at 20:07
Thanks @HannoverFist I just assumed they were using this in a procedure. Cheers! — S3S, Sep 19 '16 at 20:14

score 1 · Answer 1 · answered Sep 19 '16 at 20:21

SSRS converts multi-value parameters to a list of items when running the query.

If @garageId = 1, 2, 3 and @createdYear = 2015, 2016 then your WHERE conditions:

WHERE type = 1
AND  (mc.garageId IN(@garageId) OR @garageId IN(0))
AND  (ml.createdYear IN (@createdYear) OR @createdYear IN(0))

would be converted to

WHERE type = 1
AND  (mc.garageId IN(1, 2, 3) OR 1, 2, 3 IN (0))
AND  (ml.createdYear IN (2015, 2016) OR 2015, 2016 IN (0))

Both the 1, 2, 3 IN (0) and 2015, 2016 IN (0) will give the error because of the comma.

Rework it like

WHERE type = 1
AND  (mc.garageId IN (@garageId) OR 0 IN (@garageId))
AND  (ml.createdYear IN (@createdYear) OR 0 IN (@createdYear))

Which converts to:

WHERE type = 1
AND  (mc.garageId IN (1, 2, 3) OR 0 IN (1, 2, 3))
AND  (ml.createdYear IN (2015, 2016) OR 0 IN (2015, 2016))

score 1 · Answer 2 · answered Sep 20 '16 at 08:56

You're query will work fine if you are not using it on a Stored procedure. You should create a function that will parse each value from the multi-value parameter.

I use this function. You can use it too.

CREATE FUNCTION [dbo].[FnSplit]
(
@List nvarchar(MAX),
@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(

Id int identity(1,1),
Value nvarchar(100)
) 
AS  
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin 
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End 

Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END

What the function does is it splits value of the string into rows.

Example:

DECLARE @TEST NVARCHAR(255) = 'value1,value2,value3'    
SELECT VALUE FROM [dbo].[FnSplit](@TEST,',')

The output will be:

  VALUE
1 value1
2 value2
3 value3

Then from the SQL Query of your data set, you should change your where clause into this.

AND  (mc.garageId IN(SELECT VALUE FROM dbo.FnSplit(@garageId,',')) OR @garageId IN(0))
AND  (ml.createdYear IN (SELECT VALUE FROM dbo.FnSplit(@createdYear)) OR @createdYear IN(0))

Since you are using a home brew function to split... i think you'd benefit from this link, and adopting one of the methods therein. http://www.sqlservercentral.com/articles/Tally+Table/72993/ — S3S, Sep 20 '16 at 16:13

Using multi-valued parameters together in main query

2 Answers2