Learn T-SQL With Erik: Getting Defensive with CASE Expressions
Video Summary
In this video, I delve into the importance of writing defensive case expressions in SQL queries, drawing from my experience at Darling Data. I highlight how unexpected or problematic data can lead to errors and bad results, emphasizing the need for robust coding practices. By using examples like dividing by zero or handling null values, I demonstrate practical techniques such as CASE expressions and NULLIF functions to ensure your code remains error-free even when faced with unforeseen data scenarios.
Full Transcript
Erik Darling here with Darling Data, and today’s video we are going to talk about defensive case expressions. Now, I don’t mean the kind of case expressions that, you know, practice like beating up mannequins and whatnot. I mean the kind of defensive case expressions that keep you from hitting all sorts of weird errors and bad things that happen in your queries. Writing defensive code is a big part of your queries. You know, it’s hard of working with databases because you never know what’s going to end up in them. So, uh, be prepared, right? Anyway, uh, this, this, like this particular module, um, there’s way more to it in the full material, but this particular module is very near and dear to me because I have had to do this in so many of my stored procedures where I started running into things that, like, I didn’t anticipate or like, like, that doesn’t make any sense. Like, why, why is that an error now? Like, oh, there’s a zero, right? So, or like, oh, there’s something wrong with this string. Okay. Well, we got to write, got to be more defensive with our code. So if, if you look through this, the code that I write, a lot of it is defensive by default because I have been to too many bad places. Um, not talking about restaurants in Boston. So let’s create a table and let’s put some initial data into that table. Uh, just 10 rows is all we need to start with.
But, uh, now let’s say that, uh, we want to, um, figure out the percentage of something from this table, right? So we’re just going to run this query. And this isn’t a very, like, honestly, it’s a very simple table, very simple query. We’re not going to see anything amazing or groundbreaking here, but I just want to show you what happens when, uh, unexpected or even unwanted data ends up in our, in our databases. Now, uh, up in the table create statement, there were a couple notes like, Hey, should we have a check constraint to reject values of zero, like zero valid data points? So some stuff to think about, but you know, not necessarily stuff that you need to think about immediately. Um, so one thing, uh, before we go on is that when you’re returning results like this, uh, you do need to be explicit about converting data to the type that you expect. If you look at the results that we got back here, we got 20 with a whole mess of zeros.
All right. We don’t, do we need all those zeros? Do we want all those zeros? Do those zeros add anything to us? No, but because of the way that SQL Server chose to implicitly convert that, that division math, we ended up with a whole bunch of zeros. So if we want to control our results, we must convert our results to, uh, to display the way that we want. So here we go with a percent of 20 with only two zeros. And again, honestly, don’t add much to the equation. Right. We could just do, we could just have a, we could just have an integer there, honestly, but you know, makes it a little, makes it a little bit more interesting.
But now let’s pretend that a very problematic row appears in our table. And all of a sudden we are inserting a number zero into our table, right? And this is going to mess up everything. The whole works are going down. I thought the Titanic had a bad night. Wait till we start running this. Now, uh, if we try to run this query now, we are of course going to get a divide by zero error. All right. SQL Server will have returned some results, right? We get the first 10 rows back, but row 11, no can do, no can do. Now, of course we could write a where clause.
We could add this where clause in and say, Hey, divisor greater than zero only. We don’t get any results, but now we’re missing that row from the results. And we don’t necessarily want that either. So this is where we have to get a little defensive in our code, right? Again, this is where we get to strike the mannequin or the boxing dummy or whatever you call it. But, uh, what we can do here is we can use a case expression and we can say when the divisor equals zero, then just replace it with zero dot zero zero.
And if the else condition would be to, uh, do our convert to a decimal 10, two, uh, and then just have our division math in there. Right. And this will get us, uh, error-free code back, uh, which, which includes, uh, row 11 without having, uh, without, without any issues, right? No, no red text. That’s the big important thing. Uh, another way you can do that is by using the nullif function.
And I’m totally okay if you want to use the nullif function. Where this gets a little bit wonky though, at least for me, is that, uh, when you, when you use nullifs, what we’re going to say is, uh, if the divisor, right, the way nullif works is the first thing is the test, right? So if this thing is going to say nullif, like, there’s going to, like nullif means, uh, if this condition is true, then it’s going to, then it’s going to emit a null.
Right. So nullif divisor zero, right? So if divisor equals zero, then we’re going to emit a null here. This does, this does run successfully. And this does produce the results that we want, kinda, except now we just have a null down here. And where this, it’s kind of annoying is that now we have to wrap this whole thing in is null.
And we end up with a much, much larger expression than we did when we just use case to do the case when zero, then zero dot zero, zero. So we can run this and get back what we want with replace, replacing the null at the end with zero dot zero zero. And all is well here. So again, when you’re, when you’re writing, uh, queries, uh, and like there’s anything involved, uh, whether it’s division, substring, left, uh, you know, like so many different ways that you can write a query where the results might hit some sort of internal error, whether it’s mathematical or, uh, like invalid, like substring, uh, chopping, like, uh, bite placement, uh, please be very, very careful.
Please always write your code defensively. Uh, even if you know the data very, very well as it exists today, that does not stop bad data from eventually and they are just different data than you expect ending up in there in your code, all of a sudden throwing problems. So always think ahead a little bit when you’re writing these queries and always try to write your queries in as defensive as a way possible so that you don’t end up getting surprised by weird errors.
And all of a sudden, like getting page in the middle of the night and someone saying, Hey, the ETL process won’t run. Everything’s dividing by zero. It’s all falling over. It’s burning. Help us. Save us. We can’t live without you. And then you go find that one place where you’re doing some division and you, and you fix it and then you look like a big hero and the ETL runs and, um, I don’t know.
You have a dashboard in a day. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video where apparently we’re going to talk about subqueries. What are we going to talk about?
Well, you just, you just have to show up, won’t you? All right. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.