CODEWITHSUNDEEP
sqlsql-servert-sql
Nick Petrie
Nick Petrie

Reputation: 5598

SQL If-Else in the WHERE clause with conditions based on column values

How would you rewrite the WHERE clause in this pseudo-SQL?

SELECT *
FROM   MyTable
WHERE  IF ( Col1 <> '' ) Col1 = @Val1
       ELSEIF ( Col2 <> '' ) Col2 = @Val2

Upvotes: 3

Views: 28431

Answers (3)

Moe Sisko
Moe Sisko

Reputation: 12005

Well, it is possible to do something like this using the CASE function:

SELECT *
FROM   MyTable
WHERE 1 =   CASE WHEN Col1 <> '' THEN CASE WHEN Col1 = @Val1 THEN 1 END
                WHEN Col2 <> '' THEN CASE WHEN Col2 = @Val2 THEN 1 END
            END

A case function returns a value. So, when 1 = 1, the condition is met. Since the ELSE parts of the case are left out, NULL is returned by the case when the condition is not met. Since 1 = null can never be true, the WHERE condition will not be met.

Sometimes the extra complexity added by code like this is not warranted - it would be up to your exact situation after checking for performance etc.

EDIT: added ELSE parts. These are not needed, but might make it clearer for some. 

SELECT *
FROM   MyTable
WHERE 1 =   (CASE WHEN Col1 <> '' THEN (CASE WHEN Col1 = @Val1 THEN 1 ELSE 0 END)
                  WHEN Col2 <> '' THEN (CASE WHEN Col2 = @Val2 THEN 1 ELSE 0 END)
                 ELSE 0 
            END)

Upvotes: 2

Eli
Eli

Reputation: 2608

Assuming that I understood your logic correctly. You want to match on either col1 = @val1 or col1 is a blank string OR col2 = @val2 or col2 is a blank string.

SELECT *
FROM   MyTable
WHERE  (Col1 = @Val1 OR Col1 = '' ) 
OR (Col2 = @Val2 OR Col2  = '')

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269573

The correct equivalent for your pseudo code is:

WHERE (Col1 <> '' AND Col1 = @Val1) OR
      (Col1 = '' AND Col2 <> '' AND Col2 = @Val2)

This matches in priority order, first on Col1 and then on Col2. It only moves on to Col2 when Col1 is an empty string.

Note: This version assumes that Col1 is not NULL. That can easily be incorporated into the logic, if you need to support NULL values -- especially with a little guidance on how they should be handled.

Upvotes: 6

Related Questions