All About SQL Server Stored Procedures: Error And Situation Handling
Video Summary
In this video, I dive into the world of error handling and situation management within stored procedures, emphasizing their importance in SQL Server environments. Whether you’re a seasoned DBA or just starting out, understanding how to handle errors and unexpected situations can significantly improve your stored procedure’s robustness and reliability. I explore various scenarios where you might want to implement error handling, such as validating input parameters, managing empty result sets, and dealing with critical processing issues. Additionally, I discuss the nuances of using row count big for efficient data retrieval and how it can be integrated into triggers. For those looking to support my channel or get deeper insights, I provide information on becoming a member, asking questions during office hours, hiring me for consulting services, and attending SQL Saturday New York City in May 2025. Whether you’re a beginner or an expert, there’s always something new to learn about stored procedures and error handling.
Full Transcript
Erik Darling here with Darling Data. And we have a very interesting topic for today’s video, which we’re going to continue on learning about stored procedures because stored procedures are an important part of most, you know, DBA performance tuning development work in most SQL Server environments. Granted, even if you are using an ORM, well, golly and gosh, you can call stored procedures from an ORM. Successfully in most of them. So they are worth talking about. Now we’ve covered a few things and today we’re going to talk a little bit about, well, as soon as I click on the right thing, we’re going to talk about error handling. I’m going to tell you ahead of time. We’re going to talk about some theoretical stuff around error handling. This is not just going to be like specifically error handling, like, oh, I hit an error and now I need to handle it in some way, but also sort of like situation handling with like parameter values and like local variables and stuff because sometimes you might need to do a little bit of magic on those to make sure that nothing too screwy happens.
So it’s not just error handling. It’s like bad situation handling in store procedures. If you would like to support this channel, well, man, do I have good news for you. You can become a member of the channel and join about 60 other folks who have so kindly offered me up some money every month in response to the incredibly high quality content that I produce here for the masses.
It’s a nice way to say, think of a little tip jar, except not one of those like, you know, annoying ones where someone’s like, give me a 90% tip for the croissants I put in a piece of tissue paper. It’s like this, you get better at your job in exchange for a little bit of money. If you, if you, if you, if you’ve been shaken down by some criminal organization and you no longer, they’ve collected their debts, you are missing a finger, your wallet, your life savings, your house, your car, all of your wife’s jewelry or whatever.
You can also comment, like, subscribe and all that other stuff. I recently started or rather resumed answering questions from the public, not just in comments and whatnot. If you want to ask me a question that will be answered publicly in my office hours episodes, you can do that by clicking on that link, which is again available just about there ish.
Might be off by a few pixels, but I promise you I’ll get you in the right general area. If you need help with your SQL Server, you are in need of a consultant to help you with the health, performance, well-being and sort of general attitude of your database system. RDBMS.
You can hire me to do all sorts of things. And as the wonderful editors of BeerGut Magazine have noted for many years running now, I am the best SQL Server in the world outside, well, in like three of the four hemispheres. All right.
I’ve got world domination in 75% of the hemispheres. At least until, at least until China steals, steals my tech. And then, then we’re all, then we’re all doomed.
They’ll be deep seeked. If you would like to get access to my core training bundle, you can get, you can get all of that for a hundred and, but 150 US dollars for the rest of your life. Again, in the video description, it is, it is available there.
And I promise you, it’s a wonderful deal. You pay the fee once and you have entry forever. SQL Saturday, New York City is coming up this May 10th in 2025.
It’s taking place in the scenic, now congestion-free Times Square, New York City. So you can, you can show up there. You can learn a whole lot more about data stuff and you can leave a happier, maybe not healthier person because you’re going to drink, you’re going to eat a sandwich.
You’re going to eat a, eat a cookie and you’re going to drink a lot of soda. That is, that is generally the tenor of how these days go. So that’s, that’s, that’s going to be what happens.
I don’t know that you’re going to be healthier. You’re also going to do a lot of sitting. There’s not, not a lot of physical activity. You’re probably not going to get your 10,000 steps in, but you can show up and you can, you can learn. So if you can get a break of mental sweat for whatever that’s worth.
With that out of the way though, let’s talk about error handling stuff. Now, no talk about error handling would ever be complete without noting that Erland Somerskog, our dear friend, our dear friend of the summer, summer bog or something, has written a, a very long article on error handling. Just like Erland, it has three parts and three appendices.
So if you ever meet Erland, you can ask him about his three appendices. Don’t, don’t ask him about his three parts. It’s a private matter.
But if you need to learn like the whole in and out of error handling, that’s the best place to go. That link will be in the video description, just like all the other helpful links I provide to you. So I would, I would go there if you are just completely new and lost on it.
What I want to talk about is sort of like what, when and why you would want to implement error handling and situations where you would maybe want to do something that raises an error because someone has like, like something has just screwed up along the way. It might not be like you hit, like you did like a primary key violation, foreign key violation, something like that. Like stuff that throws errors, like divide by zero, whatever it is.
There’s all sorts of stuff that SQL Server would be like, whoa, that’s an error. What I’m talking about are situations that might arise in your store procedures that you might want to check for and say, hey, this happened. Bail out, right?
Don’t go no further. Everything’s going to be screwy. So the type of like certain errors and conditions you might want to like, or rather the outcomes of them that you might care about are stuff like this. So you might want to like, depending on like where the, where the procedure is getting run, you might want to print or log an informational message and keep chugging along.
Right. You might want to say like, hey, like zero rows came out of this thing where like nothing else is going to happen. Let’s stop here.
Or like just log like zero rows came out of this thing. None of these results are going to be good. You could also do something like return that message to the user. If it’s something that they would actually see and be able to take action on.
You might want to either keep going. You might want to like roll back or like, you know, or, you know, just kind of stop execution. You might want to like just log the error somewhere and keep going.
Or you might want to use the type of error that you hit to retry the thing and like move on from it. If you’re not going to do any of this stuff, right? If you’re not going to do anything at all with your, with the error that pops up, there’s really no sense in doing like begin, try, like and catch anything.
Right. There’s just not a lot of sense unless you’re going to handle the error in some way or like, like raise the error to something else in some way. There’s no sense in error handling.
Like if you say like begin, try, begin, train, and then there’s just like a commit and there’s like no, like nothing substantial happens on like a catch block or like, you know, you’re just not doing anything with it. Then don’t even bother. Right.
Like if you just say like begin, train, blah, blah, blah, commit. And there’s no like, what if for that? There’s no, there’s no like alternative. You hit something crappy along the way. Nothing rolls out of our stuff back.
Like you just end up with bad data, right? It’s not a good situation. So unless you’re going to like do something that like offers some kind of like secondary outcome to everything happening just fine. There’s no sense in you implementing any of the logic at all.
So like just, just to start with this sort of simple example, let’s say we have a store procedure that accepts start and end date parameters. Right. So like someone’s like, well, I want to start a week ago and I want to end up with like, you know, whatever happened today.
Right. So you’ve got seven days worth of stuff in there. You could, if, if, you know, for various reasons, someone might make a typo or someone might like transpose the order of your parameters and put like the wrong date in somewhere. Like all sorts of this, you know, errors could like mental errors could happen.
Like they’re working with a dropdown. They’re like, oh, like, you know, oh, I thought I picked like the seventh of last month and the seventh of this month. But I accidentally picked the seventh of like, like the last month.
And then the seventh of the month before or something. So, or like they just messed stuff up. So like the dates wouldn’t return anything. You might want to think about helping them out in some way or letting them know or just something along those lines.
Now, just as an example here, and this is, this is something that I do in all of my analysis procedures, because with the analysis procedures, you know, like I’m running them in management studio. I’m not like running them from an application. There’s no front end to them.
It’s not like there’s no, there’s nothing, nothing like cool going on with them. I can, I can, I can make the decision to say, hey, I’m going to try to fix this for you. Right. So when you, so let’s say like, you know, like in my store procedures, one thing that I do like at the beginning is check for like weird conditions with parameters where things might not go the way people expect based on what got passed in. So like, I just, I usually have a simple check for something like, Hey, if the start date is greater than the end date, here’s what we can do.
So the first option is you can throw an error, right? This is just an informational message and then try to like fix the parameters. And I’ll show you examples of that below.
Right. The other thing you could do is say, Hey, why don’t you try that over again? Because you like the start date is greater than the end date. That’s not going to return anything.
There’s no sense in moving forward. Like, like, Hey, like reenter that stuff in the correct way. Right. So you have two options there. If you wanted to switch things around, you could do something like, uh, declare, um, a couple local variables, store the current start and end date values in there and then swap them around. Right.
So you could do something like that. Right. So that’s, that’s one option. Uh, the other thing you could do is mess with people and just like give them random values. Right. I’m not saying you should, it’s an option.
Right. Uh, the other thing you could do is say like, you know, like I would print an informational message and be like, start date was greater than end date. We’re going to default to something sane. So just, you know, make the start date seven days ago and make the end date like, you know, a minute from like right now.
So you get like all that, that, all that data for that week. You certainly have lots of options in there. There are other times where you might hit some like critical processing issue where maybe like data from something else wasn’t ready or like you didn’t pick anything up.
That’s like new to do anything with. And you might want to stop there. So like, let’s say you had some, like there’s some part of a query where you’re like, okay, I’m going to put data that I need to work with into a temp table and then figure it out from there.
If you hit a situation where like nothing comes from this, there’s nothing in that temp table, there’s no need to keep processing like empty set, empty steps. Right. There’s no need to keep going like, oh, well, join to this, join to this, join to this, join to this, join to this.
Just, it’s just a, it’s just a waste of time. So you would probably want to just throw that out to an error here. Now, there was an article recently on SQL Server Central and it was like, how do you use ADAT row count?
And I was like, what year is this? We should, everyone in the world should be using row count big, not ADAT row count. Row count big is the preferred method for this now.
It’s why Microsoft put it in the product. There’s no use to, no need to use ADAT row count any longer. Row count big, it’s the way to go. That’s also very critical for when you’re using triggers.
Now, this is not an error handling thing. Again, this is a situation handling thing where at the beginning of every trigger, before you do anything else, just put this check in. If row count big is zero, bailout.
Right. It’s no need to go further from here. No need to like go into all the different steps and check inserted and deleted and whatever you do when you’re triggers. And try to do all this stuff and cursor over everything and audit data and all this other stuff.
It’s just useless. Right. It’s no good. Now, when it comes to retrying things, there are lots of good reasons to do that. I’ve, you know, in my life, I’ve written retry logic into a lot of stuff.
And I’m going to share with you like three different situations for doing that. Three different kind of, not completely different ways, but just different times, like different examples of ways I’ve done it. So stuff that I usually do is like around like either deadlocks or lock timeouts.
So get SP get app lock is another one where you can do like a lock timeout thing. And those are all stored over here. So just sort of like a loose sort of like a skeleton, right?
Like the sort of like outline of how I do this stuff is, you know, you have some, I mean, these can be parameters depending on how you’re doing stuff. But you can like local variables are fine for these. And what I usually do is say like, hey, if my retry count is less than my max retries and success is zero, which it currently is, then I want to try to do this.
Right. So I’ll try to, you know, redo some step. This is just a stupid update query to have something in place here.
If this is, if this goes through and does its thing, then I set success to one and I don’t, I won’t reenter that loop. Right. There’s no need to, but if something happens in here and let’s say I hit a deadlock, right? These are the, these are the things that I typically look for with deadlocks, even though I think one of those is technically locked timeout, which we’ll see below.
These are, these are the things that I typically look for with deadlocks. And if I hit one of those, I’m going to increment my retry count. And one thing that I like doing is this, where I set.
So this is, this is like, like exponential back off. Right. So every time I do this, I wait a little bit longer to, to retry. So I’m not just like hammering in a loop, like real quick.
I want to like wait a little bit longer because I might be waiting for something else to finish. Right. So I might want to like back off a little bit. I’m going to, might want to back off exponentially and then retry in there.
Right. So I’ll set my, I’ll, I’ll set up like this thing. And every time this, um, every time we enter this, this delay will get a little bit longer. And then, uh, we’ll do like the wait for delay thing in here.
If it’s not a deadlock or some other lock issue, then we’ll just throw the error and be like, Hey, I don’t know what’s going on. This is, this is not, this is not going well. Maybe it’s a primary key violation.
Maybe it’s a foreign key violation. Some other constraint violence. It’s like something else is happening here. That is not good. We’re not waiting on a locking problem anymore. We’re done. And then of course, if six, if success is still zero, we will just log in. We’ll just raise an error and say, Hey, we tried as many times as we’re allowed to.
We’re, we’re done here. You could run this thing some other time. Uh, so that’s one way for deadlocks. You can also do the same thing with lock timeout. So rather than like, like again, the same starting thing and the same like reasons for entering the loop here, but then we set a lock timeout.
And if we can’t get a lock to do this, then this thing throws an error here. And then if we hit, that’s what it is. If error number is one, one, two, two, two here, then we’ll start the retry process again. Now, one really cool way of doing this is with SP get app lock with SP get app lock.
You can set, you can set a lock timeout right in there. So if you’re not able to get the lock you want with SP get app lock, which again is like a, like a weird logical, like locking thing.
It’s like not a table or an index or like any other kind of structure. It’s just like this in memory placeholder where like if there’s a lock that exists here, if SQL Server, like put something in the lock manager that says, Hey, go no further.
Like there’s an exclusive lock on this thing. You, you like you, you’re blocked there. Right? So SP get app lock is great for that. Now, I want to make it perfectly clear.
SP get app lock can and will cause blocking. Um, it’s right in the name. It’s SP get app lock lock, right? Like it’s supposed to do what it does.
It’s, it’s like written out in the name. This is what it’s used for. Take a lock and hold it. So nothing else can like, like you can use it to serialize code, like access to code blocks, all sorts of neat things.
If you’re, if you’re going to complain about SP get app lock that you tried it and it caused blocking, well then you, then it, then it did it right. Right.
It was doing the right, it was doing what it’s named after, like right there. It’s very explicit. So if you get, if you’re able to get, um, if like, if you’re able to get the, the lock result from this, right, would be, which would be greater than zero.
Then you would begin and do your stuff. And then you would, so this is a, uh, good, uh, the way that I typically use SP get app lock is I make the lock owner a transaction, right?
But that means you have to do a little bit of transaction handling to support that. So if this, if this completes, right, then you have to do like the commit transaction here, right?
There’s like a, there’s a begin transaction up here for all this, right? So this transaction is what owns this. And then if, if this is successful, then we commit the transaction after that completes. If the lock times out, then we have to go in here and we have to roll, we have to do a rollback because our transaction will still be open up there.
So when we, we get into here, we have to roll that back. So we’re not, we’re not like, we don’t have another transaction open, but then we can do all the exact same retry stuff down in here.
And like, we’ll do the same thing where we like, you know, if, if it’s something else, then we’ll throw the error. And then we’ll, if, if we exhaust our retry attempts, then we’ll note that here. So that’s like three different ways that I’ve done retry things and different retry lodging in different ways that I think, I think have worked out pretty well.
Now, there are a lot of processes that you might write, like especially batch processes that might require robustness in various ways that normal code wouldn’t. Batch processes often need certain error handling and logging methods built in.
You might want to retry certain things like I just showed you. You might need some self-healing. So like if anything fails, you can restart where you left off rather than like, like, like, like start again from the beginning and like fail a bunch of times because stuff’s already done and then move on to the stuff that didn’t get done.
I’m not talking about, I’m not going to talk much about that because that’s, that can get really complicated depending on what you’re doing and how, like how you’re, how you’re, how you’re doing stuff with the data.
But just as like, like a, like a sort of easy example, let’s say you were building a process to migrate data from like one really big wide table to a bunch of normalized tables. You wouldn’t want to like, you wouldn’t want to have to like redo a bunch of stuff in there.
You would want to be able to pick up from where you started from. Granted, there are like, you know, you can’t destroy the data in the base table yet because like people are probably still going to be using it until everything’s in the normalized table.
So there’s a lot to sort of figure out in there. Now, uh, sort of recently on across social media, I asked a question because like every time you talk about table variables, someone’s like, oh, they survive errors and rollbacks.
You can do all this stuff with them. I’m like, oh, okay, fine. So I asked around, I was like, Hey, like, like everyone talks about this. How many people have actually done it?
Cause like, I never see anyone using table variables for this. I always see people use people using table variables for the normal dumb stuff where they should be using temp tables. So like I asked the question, like, Hey, you ever done this?
Like what do you do? And most of what I got back was that people would use it for like either extended verbose logging of like, um, steps in a process, like an ETL process or something else, or they would use it to like capture errors and log those to a table because they would be able to roll back a bunch of stuff, but maintain what’s in the table variable and then dump the contents of the table variable into a logging table after the rollback.
But before like the, like the, the, like whatever catch block completed, which is fine. Um, one thing that I think would be great with table variables is if they were like, if you were able to like capture bad data with them and like, I would never use them like for the base of an ETL process.
Cause table variables mess up a lot of stuff, right? Like you can’t do a parallel insert into a table variable, which hurts in a lot of ways. Like if you’re like really doing ETL where you’re moving a lot of data around, that’s a, that’s bad news.
And of course, like the sort of like the bad, like, like base cardinality estimates, like, you know, like, like prior to the table variable deferred compilation stuff that I’ve, I’ve talked about, you know, you get either like the one row or like, you know, like let’s use a recompile hand or you, it’s just, there’s a lot missing from table variables that would make them good for large scale ETL processes.
On top of that, like, like, like you get no like distribution, like histograms for them. Like there’s no statistics on them. So they just, they just fail in a lot of ways where like, like if they had that stuff, I mean, there wouldn’t be table, they’d be 10 tables, but like, or like rather nothing would distinguish them from 10 tables, but like they had all that stuff that might be useful.
But one thing where I get annoyed with table variables is like, uh, let’s say I wanted to like do something, right. And, uh, I want like, like, I wanted to like put data from like some ETL process into another table. And I also wanted to like figure out if, um, like, like I like, like typical, like primary key constraint violation, something, right. Like I wanted to know which rows mess things up. Like, let’s say I create this table and I do something like this, right. I declare a table variable called survivor. Uh, I insert stuff into my table here, and then I output the inserted data into the table variable here, right. And then I can select from the table variable afterwards on the first run.
This is fine because there’s nothing in the table, right. I just created it. But on the second run, we just hit an error with a constraint violation and nothing returns from here. So what I, what I think would be a really useful, um, really good use case for table variables that SQL Server doesn’t have as far as I’m aware, no, no one has this. I think it would be really useful if there were a way to like output, but say like errors only so that you could get data that threw it, like caused a problem that threw an error in the table variable without like before the whole thing fails. So it’d be nice if like there were a way to be like insert errors only into like in the output clause or like rather output errors only into the table variable so that you could like, like figure out what data caused the problem and then log that or return that somewhere and be like, Hey, this is what screwed up. This is what you have to watch out for. But of course we don’t have that. And I don’t know if we’ll ever get that. I don’t even know if that’s a thing anyone’s thought of before, but I think it would be very useful. Anyway, that’s all I have to say there. So thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I hope to see you over in the next video and good goodbye for now.
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.