Error Handling

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look into error handling and exception handling in our SQL queries. So just like and normal programming languages, like try and try accept or try catch, we can do similar error handling in our SQL queries. So in order to throw a custom error, we’re going to use the word the statement throw, and then we would define an error number message for that error and then state for that error as well. error number has to be bigger than 50,000. This is just kind of the standard number for Microsoft SQL Server. State is a tiny, tiny end with additional identification. And I’ll show an example of what that state is here in just a minute.

In order to handle these errors that are actually thrown, this also accounts for both customers and errors that are just uncaught through normal execution of SQL code. So this begin try catch is going to handle both types of situations. So begin try and try begin catch in catch, since we don’t have curly brackets, or white spacing to denote structure and SQL code, we do this we with begin and end for beginning and for try and begin and end for catch. We can use, there’s some additional mess of functions that we can use as part of the try catch, including error, getting the airline error number, what was actually error, the error message that was thrown originally, as well as the error state as well. But let’s see an example of the try catch and action here.

So I showcased how to throw an error message. Before using this line here, I’m using an F inside of our stored procedure before I’m going to create another stored procedure very similar. So create alter procedure update person, begin try. So this is inside my stored procedure, right. So inside my stored procedure begin try, and then I’m going to try to update person. If row count is zero, that means that the person that I’m trying to update does not exist in my table. Therefore, I’m going to throw a custom error message as a result, right. So if and then if I scroll down here to my try, or there’s my intro, and here’s my begin catch. So I’m going to declare a custom error message here, an error has occurred at line section such when updating the person such and such. And so there is this is also this is a way to essentially create our own custom error message. That’s the result, I don’t necessarily have to rethrow the error. But if I don’t, it’s actually doesn’t showcase this as an actual error.

So if I give this a sorry, give this a quick run. And so here is here is my print statement right here. That is this line here. But then it got thrown yet again, as as the end here. So if I comment this out, give us another run, you see that my query, actually my store procedure executes. And it doesn’t actually show as the return value from the stored procedure, not an error actually occurred. So this is kind of a tricky situation. Because if if I have a human person and executing the stored procedure, I can actually see this error message being printed out here for me. But if I’m executing the stored procedure as part of a program, I don’t get this a different state. So state one, meaning that there’s an error that actually happened. So since the state is not an error and an error state, everything supposedly went fine, even though we caught an exception being thrown. If I if I don’t have this custom error message here, then you can still put things inside of the try catch without this throw that will just catch standard errors that happen.

And we’ve seen all sorts of standard errors when we’ve been running our SQL queries, like for example trying to insert something a duplicate key or what Never that violates a constraint. And those are errors that are commonly thrown, we can catch those as part of our stored procedure, if you would like to, that is very common to produce custom error messages for the people using those stored procedures. But those are not necessarily required. But custom error handling is very useful to generate more useful error messages. Because again, right the standard error message that is produced by SQL Server is not necessarily going to be as helpful as something like this, which provides context for the reason behind the error rather than just the standard error message that SQL Server provides.

But that is all I’m really going to cover for now for Eric custom error messages. This kind of covers the gist of it. This is said typically going to happen only inside store procedures, you’re not really going to have throws and catches or tries to try and catch in anything other than a stored procedure. But again, custom error handling just like what we see in programming languages is a really great way to handle unexpected exceptions and provide error messages that are more meaningful to the people who are using your functions.