MSSQL Running an extra AND statement

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