MSSQL Running an extra AND statement

This had me baffled and shook me to the core as it went against everything I understand about an AND statement. I was using a standard CASE as I have done hundreds, if not thousands, of times that contained two AND statements. The first part of the statement returned false but, MSSQL still proceeded to execute the second statement. This may not seem like a big deal, but in this particular case I had an error producing data type mismatch problem. When the first part of the CASE was true the second part compared the data as a bigint whereas the actual value was an nvarchar, so it errored.

Let’s take a look at an example.

SQL CASE Statement

[code]
SELECT * FROM MyTable
WHERE
CASE
WHEN Something = ‘1’
AND IntSomething = NonIntValue

WHEN
Something = ‘2’
AND StringSomething = NonIntValue
END
[/code]

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:

[code]
DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;
[/code]

This results in an error:

[code]
Msg 8134, Level 16, State 1
Divide by zero error encountered.
[/code]

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:

[code]
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
[/code]

Pain in the ass, but whatever solves the problem…