SQL Where Case with multiple criteria error

Question

I'm trying to write a statement with a conditional where clause. The issue is due to needing to return multiple criteria using IN. Currently I'm returning the error:

Incorrect syntax near the keyword 'IN'.

/****** Script for SelectTopNRows command from SSMS  ******/
DECLARE @paramTime VARCHAR(50);
DECLARE @paramView VARCHAR(50);
SET @paramTime = '2020M2';
SET @paramView = 'YTD';

SELECT *
FROM [dbo].[vBiB_AllAccounts]

WHERE 
[sTime] =

CASE 
WHEN @paramView = 'YTD' AND @paramTime = '2020M1' THEN
'2020M1'
WHEN @paramView = 'YTD' AND @paramTime = '2020M1' THEN
In('2020M1','2020M2')
END

`CASE` is an *expression* it results a Scalar value, not a boolean result. — Thom A, Mar 03 '20 at 16:41
The "correct" way to use `case` in a `where` or `on` clause is shown in [this](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) answer. As others have shown, there are better ways than `case` to solve the problem. — HABO, Mar 03 '20 at 17:56

score 2 · Answer 1 · answered Mar 03 '20 at 16:41

Don't use case in the where clause. Especially when it can be easily replaced:

WHERE (@paramView = 'YTD' AND @paramTime = '2020M1' and [sTime] = '2020M1')

Your two when conditions are the same, so this is equivalent to the logic in your question.

You can add additional logic using or.

score 1 · Answer 2 · answered Mar 03 '20 at 16:41

1

Assuming that the sets of conditions are actually different (which is not the case in the code that you showed, but would make sense for your question), use OR:

WHERE 
    (
        @paramView = 'YTD' 
        AND @paramTime = '2020M1'
        AND [sTime] = '2020M1'
    ) OR (
        @paramView = 'YTD' 
        AND @paramTime = '2020M1'
        AND [sTime] IN ('2020M1','2020M2')
    )

answered Mar 03 '20 at 16:41

GMB

216,147
25
84
135

Thanks, This is where I got to myself, I had hoped to be able to use a Case purely from a readability perspective but over complicated the solution My problem is I'm going to need quite a few ORs to complete the Statement to account for YTD and QTD views of the transactions. 17 or so. Unfortunately my hands are tied in terms of the data source. It is a flat table of transactions to date so was hoping to parametise the selection a bit. – Ghoggi Mar 03 '20 at 20:07

SQL Where Case with multiple criteria error

2 Answers2