Learn T-SQL With Erik: Control Your Flow
Video Summary
In this video, I dive into the world of control flow language in T-SQL, explaining key elements like branching, looping, and error handling. I highlight common pitfalls such as null evaluation and improper use of `go-to` labels, emphasizing the importance of proper termination to avoid unintended code execution. The discussion is part of a series aimed at helping developers navigate these often-overlooked but crucial aspects of T-SQL programming. Whether you’re a seasoned SQL Server professional or just starting out, there’s always something new to learn when it comes to mastering control flow. So, if you enjoyed this content and want more insights like this, consider becoming a member of the channel or even joining me on my upcoming tour events with Red Gate in Dallas and Utrecht. Your support helps keep the knowledge flowing!
Full Transcript
Erik Darling here with Darling Data. Sometimes I want to say Darkwing Duck Data, but that wouldn’t make any sense. We’re going to keep going with the Learn T-SQL sort of preview material. And today we’re going to talk about some elements of control flow language. We’ll get more into exactly what all that means when we get to the video, but for now, just understand that I am controlling the flow of information and that’s how I choose to do it. So if you look at the down in the video description, there are many helpful links where you can do things like hire me for consulting, buy my training with discounts included in there, become a member, a supporting member of the channel, ask me office hours questions, and all that good stuff. And of course, if you enjoy this content, but you don’t want to give me money for anything, that’s cool too. Just do all the usual like, subscribe, tell a friend stuff. And I guess we can consider ourselves maybe even in some way. You never know, right? The bigger your reach gets, the better chance you are that someone will maybe want to give you money someday. I am going to be going on a partial tour of the world. Just finished up Pass On Tour New York City, but the fine folks at Red Gate are going to be wheeling me out for events in Dallas, September 15th to 16th, and Utrecht. That’s in the Netherlands, a beautiful little hamlet, October 1st and 2nd.
And then of course, there will be the Pass Data Community Summit taking place in Seattle, November 17th to 21st, where I have two days of T-SQL pre-cons with Ms. Kendra Little. And I do look forward to all of that. So with that out of the way, let us do our T-SQL partying here. Now, SQL Server has a variety of control flow elements in it. ZoomIt is doing this weird thing. Like, what happens down there, ZoomIt? What are you doing? Why are you messing with me? So if you want to do some branching, there’s if and else. If you want to contain certain things within a little flow of control, you have begin and end.
You have navigational stuff like a go-to label, and then you have looping elements like while loops. And then of course, we have the usual things that we can do within a loop to make or break progress. And then additional control flow stuff is also error handling.
Now, one thing worth noting here is that even though this, I’m going to cover this more when we talk about performance stuff in the advanced material, is that control flow elements don’t necessarily control which query plans are compiled at all, ever. You have to do some extra work in order for that to happen. But branching queries within code often has very, very profound effects on cardinality estimation and query plans.
So be very careful if you are the type of person who does that. Anyway, we’re going to cover just some like basic control flow stuff in here that have sort of more specific modules that we’re going to get to over the next couple of weeks. few weeks to talk about things more in depth where it makes more sense.
But one thing that I see quite a few people mess up and be caught off guard by is exactly what branching really controls once you start sort of evaluating expressions. For example, if we look at this right here, you know, like I see a lot of stored procedures start with these if things. And I’m looking at the code and I’m like, I think there’s a bug here.
And they’re like, no, no, no, it’s fine. Why? And I’m like, well, because like this will only do this first thing. And by that, I mean, like if we look at this code branch right here, we have a local variable declared. It’s an integer and it has a value of 10.
And then we say if the value is greater than five, then we print a couple of things. And if the value is less than or equal to five, then we’ll print a couple other things. But look what happens when we run this and we look at the messages tab.
We get value is greater than five. Hello world. Goodbye world. So what happened was we printed the value was greater than five. Let me move this thing.
That little highlight line can get a little annoying. So because the value was greater than five, we printed this as an immediate consequence of that. But then we also printed this. And then the second if branch, which was the value is less than or equal to five because it wasn’t, we didn’t immediately print this, but we did print goodbye world.
Right. So we like this conceptually is difficult for a lot of people. And I, you know, I do run into bugs around this sort of logic quite a bit, where if you want to contain these things, you do need to have explicit begin and end around them in order to make sure that you only print, you only follow the correct path.
And in these sort of branched logic places, you like the when you have something like this, the if will only do the immediate will only do or not do the first thing immediately after it. So in this case, it’s the print, this first print statement. So when we ran this one, it didn’t print this, but it did go and print this, which, you know, that is something that I see people struggle with quite a bit.
Another thing that people will struggle with quite a bit is null evaluation or like what to do if you hit a null. A lot of people just don’t plan for it. So if we were to run this, what SQL Server just says the value is not greater than five.
Goodbye world, which, you know, is correct. Null is not greater than five, which I agree with. That’s not where that’s not where the thing is.
It’s just that if you were to go and try to do something with a null, well, that might that might upset whatever logic you’re working with to to figure things out or whatever logic you might have to you might be like, you know, running queries for a null value ending up in there might not be a good thing. So when you’re doing this sort of stuff, it’s best to explicitly handle nulls in some way and either like stop the execution of whatever like like like either take maybe take a different branch like this sort of like a no op that just says no, this was null. We can’t do anything with null from there and move on.
You know, you could like you could maybe like use is null or something, but it wouldn’t get you the same like like handling for something like a situation where you didn’t want to work with nulls further down. So always make sure to plan for nulls in your data and handle them accordingly, even in this sort of branching logic, because, you know, this make sure you like always test with a null at some point, because if you don’t and you end up with a null, even if you swear up and down, this could never be null. Well, I don’t believe you because I’ve seen too many people be wrong about this sort of thing.
Another thing that is worth talking about here a little bit, I don’t see these used a lot, but where I do see them used in code, people often do not terminate go to logic correctly. So if we were to run this whole thing, right, which we have the local variable declared, it’s an integer set to null. And we’re going to have these different go to units in here or go to labels in here.
And I have the these returns coded out just for a little bit of convenience. But this is where each one of these things would end up. Right.
And a lot of people think that when you go to a label, that just means the end of something happening. But that’s not true at all. If we run this, look what we get back. Value is null.
Unknown world value is greater than five. Hello, world value is not greater than five. Goodbye, world. Why are we here? So if you want sort of the termination of code after you have hit a label, you really do need to add these returns in or else you will just keep on going. You will just keep on running right through code.
I think that was yeah, that was all. So if we run this, then we get the correct termination of whatever block we entered, whatever label we jumped to after that. So be careful with if branching using begin and end properly.
Always test for nulls. And if you are going to use sort of go to labels and you’re going to jump around within code, make sure that you have a return to to like the stop execution after you have done whatever you need to do within that label. Otherwise, you will end up doing probably a lot more than you bargained for.
Anyway, next up, we’re going to be talking about while loops. So I’m generally far more excited about that. But I don’t know.
These are things that I find people struggling with. So, you know, I talk about them because I don’t want you to struggle. I like you. You seem like a nice person. You know, I don’t necessarily think we need to hug, but you seem OK.
So I want you to have a nice life. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
And I will see you in the next video about while loops where we will. Well, I guess it’ll it’ll be a surprise, right? If I tell you what I’m going to do, then ruins everything.
I’m going to keep you on your toes out there. Anyway, 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.