CODEWITHSUNDEEP
sqlcasewhere-clause
Maxrem
Maxrem

Reputation: 45

sql server select where case IN

Please help with syntax, i need where IN clause only when parameter = 1 otherwise all records

not working

SELECT * FROM T 
    WHERE T.C = 'AAA' 
    AND CASE WHEN @Param = 1 THEN T.ID IN ('1','2','3') END - this condition only needed when @param = 1

Im try below, but how get all records?

   AND T.ID IN CASE WHEN @Param = 1 THEN ('1','2','3') ELSE -all rows- END

Upvotes: 1

Views: 511

Answers (1)

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391306

Don't use CASE for this.

Rewrite your SQL like this:

WHERE
    T.C = 'AAA'
AND (
    (@Param = 1 AND T.ID IN ('1', '2', '3'))
    OR
    (@Param <> 1)
    )

Remove whitespace as needed when you understand how/why it works.

It can even be shortened down if we change the order of the OR part (not necessary really but easier to understand for us humans then):

WHERE
    T.C = 'AAA'
AND (
    @Param <> 1
    OR
    T.ID IN ('1', '2', '3')
    )

If @Param is not different from 1, it has to be 1, so then you don't need to explicitly check this in the other branch.

Thus the best way to do this is this:

WHERE
    T.C = 'AAA'
AND (@Param <> 1 OR T.ID IN ('1', '2', '3'))

Upvotes: 4

Related Questions