CASE Statement in WHERE statement (equal to and not equal to)

Question

I have the following query where I'm trying to return different values from a table based off the values of a bit variable.

Is there a way I can substitute the where condition to get that to work?

DECLARE @isAggregate bit = 0

SELECT 
    *
FROM
    Fields
WHERE
    FieldType  
    CASE 
        WHEN @isAggregate = 1 THEN = 'Aggregate'
        WHEN @isAggregate = 0 THEN <> 'Aggregate'
    END

[This answer](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) demonstrates how to use a `case` _expression_ in a `join` condition. It works the same way in a `where` clause. Depending on the logic involved there may be much more efficient ways to write a query. — HABO, Aug 08 '18 at 13:46

score 4 · Accepted Answer · answered Aug 08 '18 at 11:08

4

You can use boolean logic, case expression will not work in this way :

where (@isAggregate = 1 and FieldType = 'Aggregate') or
      (@isAggregate = 0 and FieldType <> 'Aggregate')

answered Aug 08 '18 at 11:08

Yogesh Sharma

49,870
5
26
52

CASE Statement in WHERE statement (equal to and not equal to)

1 Answers1