MSSQL Running an extra AND statement 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


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

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.

Jamie Munro
Jamie Munro

Jamie Munro is the author of ASP.NET MVC 5 with Bootstrap and Knockout.js, Knockout.js: Building Dynamic Client-Side Web Applications, 20 Recipes for Programming MVC 3, and 20 Recipes for Programming PhoneGap. He has been developing websites and web applications for over 20 years.

Jamie began his writing career in 2009. As the success of Jamie's blog grew, he turned his writing passion to books about web development in hopes that his many years of experience could be passed on to his readers.

Tutorials

Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.

Front-End

Server-Side

Database