CODEWITHSUNDEEP
t-sqlcasewhere-clause
Peter
Peter

Reputation: 808

CASE statement to switch WHERE conditions

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?

Upvotes: 1

Views: 1151

Answers (5)

KM.
KM.

Reputation: 103667

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

Upvotes: 1

Alex K.
Alex K.

Reputation: 175916

How about simply;

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

Upvotes: 0

JonH
JonH

Reputation: 33173

You could also use boolean logic:

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

Upvotes: 0

jdot
jdot

Reputation: 811

Will this work for you?

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

Upvotes: 4

BeachBlocker
BeachBlocker

Reputation: 246

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.

Upvotes: 1

Related Questions