Using CASE in WHERE Statement when parameter has multiple values

Question

I have a problem which I think relates to having a multiple value parameter.

In my TblActivity there are two fields TblActivity.ActivityServActId and TblActivity.ActivityContractId which I want to include in my WHERE statement.

Filtering by these is optional. If the user selects 'Yes' for the parameter @YESNOActivity, then I want to filter the query looking for rows where TblActivity.ActivityServActId matches one of the options in the parameter @ServiceActivity.

The same goes for the @YESNOContract, TblActivity.ActivityContractId and @Contract respectively

I managed to get to this:

WHERE
(CASE WHEN @YESNOActivity = 'Yes' THEN TblActivity.ActivityServActId ELSE 0 END) 
IN (CASE WHEN @YESNOActivity = 'Yes' THEN @ServiceActivity ELSE 0 END)  

AND (CASE WHEN @YESNOContract = 'Yes' THEN TblActivity.ActivityContractId ELSE 0 END) 
IN (CASE WHEN @YESNOContract = 'Yes' THEN @Contract ELSE 0 END)

However, although this code works fine if there is only one value selected in the parameter @ServiceActivity or @Contract, as soon as I have more than one value in these parameters, I get the error:

Incorrect syntax near ','.
Query execution failed for dataset 'Activity'. (rsErrorExecutingCommand)
An error has occurred during report processing. (rsProcessingAborted)

Can anyone see what I'm doing wrong? I could understand it if I had an = instead of IN in the WHERE statement but can't figure this one out.

Using SQL Server 2008 and SSRS 2008-r2

I can guess, but you should explain how you storage multiple parameters in `@ServiceActivity` — Juan Carlos Oropeza, Jan 15 '16 at 16:07
Hi Juan, Thanks for tidying my question :) @ServiceActivity is an integer, and I've ticked 'Allow multiple values'. Is this what you mean? — EileenS, Jan 15 '16 at 16:12
You say when `@ServiceActivity` has one value works ok. So we need to know how you store multiple values in one Integer — Juan Carlos Oropeza, Jan 15 '16 at 16:13
I just use the standard settings for a multi value parameter in SSRS, which is to tick 'Allow multiple values'. From what I can see, this turns it into a comma separated list. — EileenS, Jan 15 '16 at 16:19

score 2 · Answer 1 · edited May 23 '17 at 11:52

2

In SQL, the IN clause does not support parameters the way you are using them. The general syntax is

IN (1, 2, 3, 4)

you have

IN (@Param)

where something like @Param = '1, 2, 3, 4'

Internally, SQL will turn this into

IN ('1, 2, 3, 4')

Note the quotes... you are now matching against a string!

There are a number of ways to address this. Search SO for "sql in clause parameter", pick one that works for you, and upvote it.

(Added)

Parameterize an SQL IN clause seems pretty definitive on the subject. While long ago I upvoted the third reply (the one with table-value parameters), any of the high-vote answers could do the trick. The ideal answer depends on the overall problem you are working with. (I am not familiar with SSRS, and can't give more specific advice.)

edited May 23 '17 at 11:52

Community

1
1

answered Jan 15 '16 at 16:16

Philip Kelley

39,426
11
57
92

I tought this was the issue, but in comment OP say is an integer. – Juan Carlos Oropeza Jan 15 '16 at 16:17
I'm not sure what it means, but I think the "ticked 'Allow multiple values'" is something from the application side...? – Philip Kelley Jan 15 '16 at 16:18
Thanks, yes I can see that this is the problem. I've had a search for 'SQL in clause parameter' and and struggling to find a solution I can understand (not SQL expert as you can prob tell). Is there any one in particular you would recommend? – EileenS Jan 15 '16 at 16:30
Added it as a link in my reply. – Philip Kelley Jan 15 '16 at 18:25

Juan Carlos Oropeza · Accepted Answer · 2016-01-15T16:57:25.793

2

If your @ServiceActivity is something like 1,2,3

You can do something like this

WHERE  `,1,2,3,` LIKE `%,1,%`

So you format your variables

WHERE  ',' + @ServiceActivity + ',' LIKE '%,' + ID + ',%'

SQL FIDDLE DEMO

SELECT *
FROM 
    (SELECT '1,2,3,4' as X UNION ALL
     SELECT '2,3,4,5' as X UNION ALL
     SELECT '3,4,5,6' as X UNION ALL
     SELECT '1,3,4,5' as X 
     ) as T
WHERE ',' + X + ','  LIKE  '%,1,%'

For Your Case

(CASE WHEN @YESNOActivity = 'Yes' 
           THEN ',' + @ServiceActivity + ','
           ELSE NULL 
 END)
LIKE
(CASE WHEN @YESNOActivity = 'Yes' 
           THEN '%,' + TblActivity.ActivityServActId + ',%'
           ELSE 0 
 END)

edited Jan 15 '16 at 16:57

answered Jan 15 '16 at 16:37

Juan Carlos Oropeza

47,252
12
78
118

Is this what you mean? – EileenS Jan 15 '16 at 16:49
' (CASE WHEN (@YESNOActivity) = 'Yes' THEN (',' + TblActivity.ActivityServActId + ',')ELSE 0 END) – EileenS Jan 15 '16 at 16:50
LIKE (CASE WHEN ATYESNOActivity = 'Yes' THEN (',' + ATServiceActivity + ',') ELSE 0 END) – EileenS Jan 15 '16 at 16:52
Had to use AT instead of @ to get the code to post there. Not sure why – EileenS Jan 15 '16 at 16:52
I include a DEMO, let me know if that is enought for you understand. – Juan Carlos Oropeza Jan 15 '16 at 16:53
I manage rewrite your code to fix that solution, try it and let me know – Juan Carlos Oropeza Jan 15 '16 at 16:57
Hi, sorry for the delay, weekend got in the way. I've tried the code above. When I try running it with param YESNOActivity = 'Yes' and one integer value in param ServiceActivity I get the msg: Conversion failed when converting the varchar value ',' to data type int – EileenS Jan 18 '16 at 08:33
Also, When I try running it with multiple values where YESNOActivity = 'Yes' and several comma separated integers in param ServiceActivity I get the msg: Conversion failed when converting the varchar value '%,' to data type int – EileenS Jan 18 '16 at 08:34
USE convert instead `SELECT '%' + CONVERT(varchar(10), ServiceActivity) + '%' FROM table_name` – Juan Carlos Oropeza Jan 18 '16 at 13:25
'SELECT TblActivity.ActivityRefNo ,TblActivity.ActivityContractId ,TblActivity.ActivityServActId ,TblContract.ContractId ,TblContract.ContractName FROM TblActivity LEFT OUTER JOIN TblContract ON TblActivity.ActivityContractId = TblContract.ContractId WHERE (CASE WHEN ATYESNOActivity = 'Yes' THEN ',' + CONVERT(varchar(10), @ServiceActivity) + ',' ELSE 0 END) LIKE (CASE WHEN ATYESNOActivity = 'Yes' THEN '%,' + CONVERT(varchar(10), TblActivity.ActivityServActId) + ',%' ELSE 0 END) ' – EileenS Jan 20 '16 at 14:26
I'm not 100% sure where you suggest the convert goes - I've been playing about with it in the whereand the select part of the query, but can't get any combination to work. Above is the last version of the code I have tried (with the @ symbols replaced as SO doesn't seem to like this symbol repeated in a comment!) – EileenS Jan 20 '16 at 14:28
Sorry EileenS dont know what else I can do to help, I prepare a sqlFidlle with a demo and I wrote the code as I think your schema should be. You didnt say if you are getting error or something wrong. Why you dont try update the fiddle to match your db schema? – Juan Carlos Oropeza Jan 20 '16 at 16:18
thanks Juan, although I haven't got this working yet I think this is along the right lines so voting this as the answer as most helpful in pointing towards the solution. Philips link was useful too but can't vote twice! – EileenS Feb 08 '16 at 08:07
@EileenS You can also leave an upvote if think the answer is helpfull. And I told you if you update the sqlFiddle I can take another look – Juan Carlos Oropeza Feb 08 '16 at 12:56

score 0 · Answer 3 · answered May 03 '16 at 14:37

So after a lot of messing around I put together a simple workaround for this by dropping my use of CASE altogether - but I have a suspicion that this is not a terribly efficient way of doing things.

WHERE
(@YESNOActivity = 'No' OR (@YESNOActivity = 'Yes' AND
TblActivity.ActivityServActId IN (@ServiceActivity)))

AND

(@YESNOContract = 'No' OR (@YESNOContract = 'Yes' AND
TblActivity.ActivityContractId IN (@Contract)))

Using CASE in WHERE Statement when parameter has multiple values

3 Answers3

Linked