Giving information about errors
1. Giving information about errors
In this lesson, you will learn how to use some functions that can give you information about errors.2. Getting an error - review
Before learning these functions, let's recall first what happens if we don't surround our code by a TRY...CATCH construct, and we get an error. In the output, we get the complete error with the error number, severity, state, line, and message.3. Getting an error - review
However, if we surround the same code by a TRY...CATCH construct it seems that we lost the original error information, getting just what we code in the CATCH block. The information we lost can be useful sometimes. Luckily, we can still retrieve it using some functions in our CATCH blocks.4. Error functions
Let's see these functions. The ERROR_NUMBER function returns the number of the error. ERROR_SEVERITY returns the severity of the error. Remember that you can only catch those errors with a severity between 11 and 19. The ERROR_STATE function returns the state of the error. ERROR_LINE returns the number of the line where the error occurred. ERROR_PROCEDURE provides the name of the stored procedure or trigger where the error happened. It returns NULL if the error didn't happen within a stored procedure or a trigger. Finally, the ERROR_MESSAGE function returns the text of the error message.5. Error functions - examples
Let's see some examples. In this example, we added to the CATCH block the functions we just explained. We added them using the SELECT statement. If we execute this code, we get the following output. We can see the error number, severity level, error state, error line, and message. Notice that error procedure is NULL because this block of code is not within any stored procedure or trigger.6. Error functions - examples
If we compare this last output with the original error, we can see that the information is the same. The only difference is the error line. That's because in the example we just did, we added TRY syntax to the first line, so the INSERT statement is now on the second line.7. Error functions - examples
Let's see what happens when we try to use these functions outside a CATCH block. If we execute this code, all these functions will return NULL values.8. Error functions in nested TRY...CATCH constructs
You can also use these functions in nested TRY...CATCH constructs. In this example, the CATCH block has another TRY...CATCH construct to handle errors encountered by the CATCH code. Let's imagine that there is an error when trying to insert the product. The control passes to the CATCH block, and the insert error statement is executed. Then, another error occurs. Let's suppose that it happens because the text ERROR! exceeds the limit of characters. Consequently, the second CATCH block will be executed, showing the following output. This error information is about the last error that occurred, not about the first one.9. Let's practice!
Let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.