CASE statement to switch WHERE conditions

Question

I would love to have a t-sql statement like the following...

SELECT [field]
FROM [table]
WHERE CASE @flag
        WHEN 1 THEN col1 = @value
        ELSE col2 = @different_value
      END

The point being I want to put a flag in my function to use different where clauses in the same query. I just can't get it to work. Is this possible?

Answer 1

Perhaps this could be of interest

http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

Answer 2

With mySQL your statement should work, because mySQL supports binary expressions.

So you have to try this

SELECT [field] 
FROM [table] 
WHERE CASE @flag 
        WHEN 1 
        THEN case when col1 = @value then 1 else 0 end
        ELSE case when col2 = @different_value then 1 else 0 end 
      END = 1

That isn't pretty good readable. Please be aware of performance issues, because the optimizer may struggle here.

Answer 3

Will this work for you?

Where (@flag = 1 and col1 = @value) or (@flag != 1 and col2 = @different_value). 

Answer 4

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).

Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

this will produce the best execution plan:

IF @flag=1
BEGIN
    SELECT
        [field]
        FROM [table]
        WHERE col1 = @value
END
ELSE
BEGIN
    SELECT
        [field]
        FROM [table]
        WHERE col2 = @different_value
END

However, if you have to have a single query, this is your best bet at using an index

SELECT
    [field]
    FROM [table]
    WHERE @flag=1
        AND col1 = @value
UNION ALL
SELECT
    [field]
    FROM [table]
    WHERE @flag!=1
        AND col2 = @different_value

Answer 5

You could also use boolean logic:

SELECT blah FROM myTable WHERE (@i IS NULL OR AnotherCondition)

Answer 6

How about simply;

WHERE
  (@flag = 1 AND col1 = @value)
OR
  (@flag = 2 AND col2 = @different_value)
...