SQL CASE Statement
SELECT * FROM MyTable
WHERE
CASE
WHEN Something = '1'
AND IntSomething = NonIntValue
WHEN
Something = '2'
AND StringSomething = NonIntValue
END
So when the value Something is 1 I want to compare a specific value to a field and a different one when the value is 2. This was inconsistently throwing an error. I mean that on another developer's computer SQL correctly did not throw an error because the value of Something was 2. Baffled.
Let's take a look at an even simpler example:
DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;
This results in an error:
Msg 8134, Level 16, State 1
Divide by zero error encountered.
Thanks to this site: https://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression to help demonstrate the problem and another thanks to my fellow developer for providing the workaround solution to use a second case statement.
To circumvent this oddity I sometimes write a second CASE statement to check that the value is not 0 which works as well in the first example:
SELECT * FROM MyTable
WHERE 1 =
CASE
WHEN Something = '1'
CASE
WHEN IntSomething = NonIntValue
ELSE 0
END
WHEN
Something = '2'
CASE
WHEN StringSomething = NonIntValue
ELSE 0
END
END
Pain in the ass, but whatever solves the problem...
Published on Jan 24, 2020
Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users
| case
