T-SQL | Conditions in Where Clause

Question

I've a parameter that is a integer -> @Flag and I've a table with this structure:

ID     Date    Value_A  Value_B Value_C
A   2017-07-14  1          1       1
A   2017-07-13  1          0       1
A   2017-07-12  1          0       1

What I'm trying to get is: - If the parameter @Flag is > 0 then get the rows where Value_A, Value_B or Value_C don't have the same values. In the example above, if the @Flag is 1 then it returns second and third row. - If the parameter @Flag is = 0 then returns all the rows.

I'm trying with this script but I'm getting errors in Sub-Queries:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

DECLARE @Flag INT
SET @Flag = NULL

SELECT *
FROM teste
WHERE Value_A = CASE  
                  WHEN @Flag > 0 THEN 
                   (SELECT Value_B FROM teste WHERE Value_A <> Value_B or Value_A <> Value_C) 
                  ELSE (SELECT Value_A FROM teste) 
                END

How can I do this?

Thanks!

[This](https://stackoverflow.com/a/10260297/92546) answer shows how to use a `CASE` expression in a `JOIN` or `WHERE`. Andy's answer is still a clearer way to express your intent. — HABO, Jul 14 '17 at 11:38

score 2 · Accepted Answer · answered Jul 14 '17 at 11:28

You can't compare single value with set as Value_A = (some set of values).

But in your particular case it is not needed at all, since your query can be simplified as:

SELECT *
FROM teste
where
    (@Flag > 0 and ( Value_A <> Value_B or Value_A <> Value_C))
    or @Flag = 0

score 0 · Answer 2 · answered Jul 14 '17 at 12:16

0

Assuming the values are not the NULL, and you only care about flag being 0 or not zero:

SELECT *
FROM teste
WHERE @Flag = 0 OR
      (Value_A <> Value_B OR Value_A <> Value_C)

answered Jul 14 '17 at 12:16

Gordon Linoff

1,242,037
58
646
786

T-SQL | Conditions in Where Clause

2 Answers2