1. TRY & CATCH those errors!
Let's learn how to include error handling in stored procedures.
2. To handle errors or not
An entire Datacamp course could be devoted to effective error handling in SQL Server. Here, we will cover part of this topic to get you started with incorporating error handling in stored procedures.
What is error handling? It happens when errors are anticipated, detected, and resolved in a way that controls the normal flow of execution. To do this, error handling needs to be integrated into the query design from the beginning.
What happens when errors are NOT effectively handled? You have probably experienced this. An application can shut down or get stuck and can't continue to execute. A generic error message might be provided that doesn't include helpful context. This can be frustrating for users and developers so it's important to anticipate errors regardless of how well your queries are written.
3. Let's TRY
Let's intentionally create an error to illustrate how error handling can be incorporated. Here is our TripSummaryCreate stored procedure, but the data type of the @TripDate parameter is changed to a nvarchar instead of a date. When we pass an invalid date value to the stored procedure it will be accepted into the parameter, but an error will occur when we attempt to insert a non-date value into the TripSummary table. The data type of the table column is a date and SQL Server can't convert a string to a date.
A third output parameter has also been added, named @ErrorMsg. It's an optional parameter as indicated by setting the initial value to null.
The INSERT INTO statement is enclosed within a BEGIN TRY and END TRY block, where the code that could cause an error should be located.
4. Time to CATCH
The BEGIN TRY and END TRY block should be followed by the BEGIN CATCH and END CATCH block. This is where you can capture details about the error that occurs within the BEGIN and END TRY. Here we capture the Error number, severity, and message. These three error functions are concatenated together into a string with some additional text labels and assigned to the @ErrorMsg output parameter. If you didn't want to return this in an output parameter, it could also be inserted into an error message table.
5. Show me the ERROR...
What happens now when we pass 1/32/2018 to the TripSummaryCreate stored procedure? An error occurs when SQL attempts to convert 1/32/2018 to a valid date when inserting into the TripSummary table.
The @ErrorMsgOut variable we declared now contains the value of the @ErrorMsg output parameter. It's important to capture and store details about errors when they occur because those details can't be accessed after they happen.
6. THROW vs RAISERROR
As you educate yourself further on SQL Server error handling you may want to generate your own errors and not just handle those that SQL Server generates. To switch control from a Try block to a CATCH block, you can use the THROW keyword. This is helpful when you need to initiate an error that could cause a problem in your query.
RAISERROR was the main error handling tool in SQL Server until THROW was introduced in 2012. THROW is easier to use and statements that follow THROW will not be executed if an error occurs. RAISERROR generates a new error and can't access the details about the original error. Statements that follow RAISERROR can still be executed. It's recommended to utilize THROW instead of RAISERROR, but it's helpful to know what predates it and their distinctions.
7. Your turn to CATCH!
It's time for practice!