Follow @endyourif rss Facebook LinkedIn

MSSQL Running an extra AND statement MSSQL Running an extra AND statement

Published on Jan 24, 2020

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




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...

Tags: SQL | case | mssql

My Books
ASP.NET MVC 5 With Bootstrap and Knockout.js
Knockout.js Building Dynamic Client-Side Applications
20 Recipes for Programming MVC 3
20 Recipes for Programming PhoneGap
Rapid Application Development with CakePHP