Learn T-SQL With Erik: Fun with RAISERROR!
Video Summary
In this video, I delve into some of the often-overlooked aspects of the `RAISERROR` function in SQL Server. While many have moved on to using `THROW`, I still find significant value and utility in `RAISERROR`. I explore its various substitution parameters, such as `%d` for signed integers, `%x` for unsigned hexadecimal, and `%i64d` for bigints, pointing out the absence of native support for bits or temporal data types. This can be a bit cumbersome but also opens up creative possibilities for error handling in dynamic SQL scenarios.
I also cover how `RAISERROR` can be used effectively within loops to print messages without waiting for buffer fills, and demonstrate how adjusting severity levels can change message appearance. Additionally, I show how you can dynamically assign values to parameters like severity and state based on your code logic, providing a more flexible error handling mechanism. The video concludes with practical examples of using `RAISERROR` in dynamic SQL contexts, highlighting its benefits over traditional error functions.
Full Transcript
Erik Darling here with Darling Data. And today we’re going to be talking about some of the somewhat overlooked things about raise error. Because I still find value and use and utility in raise error. You know, some folks have graduated to throw or semicolon throw, as I often see in the world for no particular good reason. So you just terminate your previous T-SQL statement, you won’t have a problem. So there’s that. Anyway, we’re going to do that. In the meantime, if you feel so bold and you look a little bit further down in the video description, right? My face is up here, but I don’t care if you look down here for a minute. You’ll see all sorts of useful and helpful links for ways where you can interact with me further. You can hire me for consulting. I do that as a full-time job. It’s nice. You can buy my training, right? Including the Learn T-SQL with Erik course that this is teaser material from. It’s down there. You can become a subscribed member of the channel. You can give me a small amount of money every month to keep producing this wonderful content. You can ask me office hours questions for those episodes where I answer five user submitted questions. I get a bit of a backlog now, but you know, times are busy. So, we’re rolling these videos out as we can. And of course, if you enjoy this content and you feel that it would be a remarkable, it would be a remarkable improvement in someone’s life if they were to view this content on a regular basis, I would suggest that you not only like and subscribe, but you tell that person about this channel. Perhaps send them a link and tell them, encourage them to like and subscribe. So, with that out of the way, we need to discuss my travel plans.
For the remainder of the year, where I will be in Dallas, September 15th through 16th and Utrecht October 1st and 2nd for a couple of mini pass on tour events that Redgate is putting on. And of course, this all leads up to the greater, larger, past data community summit taking place in Seattle, Washington from November 17th to 21st, where I will be doing two days of T-SQL pre-cons with Kendra Little. So, we do have many things to look forward to there. But with that out of the way, let’s talk about some of the fun stuff with raise error.
Now, the first rule of raise error is to make sure that you understand how to use it. Now, if you look at things that you can use as substitution parameters or strings with raise error, or substitution wildcards or something with raise error, you’ll notice that you have %d or %i for signed integers.
You have %x or that should be % uppercase x. Let’s fix that. Let me don’t say zoomed in on that for too long. Got lost in the copy paste or something. We have %o for an unsigned octal. I’m not sure what that is.
I’m not a math person. I don’t know what an octal is. You have %s for string. You have %u for unsigned integer. Well, apparently the… I don’t know.
I don’t know why you wouldn’t sign an integer. And then you have %i64d for big ints. There are a couple notable things that are missing here.
Like say, bits. Right? Or say dates or times or date times. If you want to use bits, or you want to use some temporal thing in here, you have to store that as a different data type before you are allowed to use it with raise error.
So if you have a bit, you often have to store it as a tiny int. And if you have a date, you often have to convert it to a string in order to present it as a substitution in raise error.
So there are no native replacements for bits and strings with it, which is quite annoying. I find that quite obnoxious about raise error, because now I have to declare all these additional parameters and hang on to them, and it’s just annoying.
Why we couldn’t get bits and strings for raise error? I don’t know. It’s just the way it is. So there are some neat things with raise error. Or if you just want a normal printed message, you can use log if you want to send it to the error log.
I’ve never found a lot of use for this. Maybe people who do more dedicated SQL development work, who plow through the error log for various things, find this a useful addition to it.
I’ve never really used it all that much. Most of the code that I write these days is my store procedures, where I don’t need to send anything to the error log.
There wouldn’t be any use in that. And most of the client code that I work on, there’s no sense in polluting their error logs with anything that would happen in there. But if I were writing some sort of maintenance procedure, this might be useful.
But notice if we use 10 for a severity here. I am an error gets printed in, well, because I’m using dark mode. So this text is some form of white.
If I were using light mode, this text would be some form of black. So local factors will apply. But the most important thing is that this doesn’t look much like an error, does it? It just looks like a regular printed string.
That’s why raise error can be very, very useful when you’re working with things like loops. So if you are writing a loop of any variety or any sort of task where you want to print a message when things happen, this with no wait is very, very useful because you do not have to wait for these messages to buffer anywhere before SSMS starts sending them out.
If you just use print, you do have to wait for certain buffers to fill before the print messages come out. If you want your text to come out red, you can use severity 11 or higher. And now notice we get some additional stuff here along with the text being red.
This would be red regardless of dark mode or light mode or anything in between. System default, if you will. One thing that you do want to avoid though is not using an aggressively high error message.
So here I, the last one is severity 11. Here I’m going to use severity 20. And severity 20 is just about as high as you can get. But when I run this, well, we get a whole lot of stuff back.
I once worked with a client who was doing, was using raise error for stuff. And people like the one developer was putting a level 20 in there. And everyone thought that the database was corrupt and that there was some problem.
Like every time this, like this, the store procedure had like hit an error, it was like, it would spit all this stuff back. And people would be like, we got corruption run check DB there, run check DB. Nothing came back.
And it’s very confused until we looked at the code and I said, ah, well, guess what? We’re all safe. We all live another day. So what, what, getting to sort of what I was talking about first with a sort of wildcard replacement is that you can put whatever you want in here, as long as it’s not a bit or a date or a time or a date time, those you have to store differently.
But you can, you can substitute things in either as a string or as an integer. These are the replacements that I’ve chosen here with percent S and percent I. Well, I guess there’s already a little line underneath it.
So we’ll give this one a hat. But what’s kind of nice in here is that if we run this, we will see the replacements all worked very nicely. Now, one thing that people do miss sometimes is that you can substitute anything in raise error with a parameter or variable, right?
So in a lot of these, we were using just a literal value for severity and state. But you can, you can have, you can assign this dynamically based on whatever your code does. And you might, you know, assign some things a severity of 10.
If you don’t care about it being read and printing an error and maybe like halting execution, you, if you just want to like, you can assign things any way you want. So like, if you look at this, I’ve declared some variables in here in order to do a little bit of replacement.
So I’ve got error message. I’m just going to highlight this part. So you can add a little bit more, uh, severity, state, error number, error procedure, and error line. Now in real life, like if you were writing like real legit error handling, I don’t know, you would probably just use the various built-in error functions to assign these in your try catch block.
And that would be totally fine. You could also assign your own things to them. If you want to a little bit more control over what gets spit out to people, right? Like you might like, instead of like a full crazy error message, you just might say, retry your procedure or something, right?
You put whatever you want in there. It’s nice. It’s a good time. So what you can do is have any one of these, uh, replace things. So we have severity and state this time is, uh, well, obviously local variables, because I just declared them up there, but then we have, uh, all the replacement stuff that I would be using down here.
Right? So, uh, you’ll see number procedure line message, all the stuff that you would put in like a normal, good verbose error message to help you figure things out. And if we run all this, we will get all that back.
Uh, I’m going to put that on a new line just so we have things a little bit more friendly for zooming in and we have our number, right? Good for us.
Right. And we see, well, we used 11 and one up here, so that doesn’t really do anything, but we, uh, we did mess with our line number a little bit. Uh, and then we have, uh, the number that we wanted to replace, uh, the procedure name, right?
So like, if you write a lot of dynamic SQL and, uh, maybe, you know, you want to raise an error and you like, you know, procedure name is cool and all, but you know, you could say like dynamic SQL block 10 from whatever procedure, and you would be able to locate the dynamic SQL block that you, that you, uh, you like name or like label dynamic SQL block 10, like a comment in the code or something.
And, you know, I’m grant, you know, I write some long procedures, but that’s, that’s getting out of control. And, you know, we can put in whatever message we want here. So there’s all sorts of fun stuff that you can do with raise error to make things, uh, better suited to your environment, more descriptive and a little bit more dynamic than what you might get with sort of like just normal assignment to the various built-in error functions that SQL Server has.
Um, I like using this stuff, particularly, particularly with dynamic SQL, uh, because I can, uh, maybe like in the error message, I can do a preview of the dynamic SQL, or I can put the error procedure as, you know, whatever block that I like labeled in the procedure with a comment, something like that.
So there’s all sorts of neat things you can do to help you get exactly to the code that’s having a problem that maybe wouldn’t be so obvious if you were just using like, um, like the normal results of error functions.
So, uh, easy Friday video, nothing too crazy in here, but I hope you enjoyed yourselves. I hope you learned something and I will see you over in the next video, which will be on Monday, which will be office hours, reliable office hours, where I will answer five user submitted questions because I will, I will count carefully.
I will endeavor to count more carefully in the future. 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. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.