Tutorial :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?


Solution:1

Will this work for you?

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


Solution: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.


Solution:3

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  


Solution:4

You could also use boolean logic:

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


Solution:5

How about simply;

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

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »