CODEWITHSUNDEEP
sqlsql-servert-sqlcasewhere-clause
Ghoggi
Ghoggi

Reputation: 1

SQL Where Case with multiple criteria error

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

Upvotes: 0

Views: 66

Answers (2)

GMB
GMB

Reputation: 222462

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')
    )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 2

Related Questions