Once we’ve set up our table, the next step is to create a stored procedure that demonstrates how to handle errors. The procedure, UpdateSales, modifies the value in the SalesLastYear column in the LastYearSales table for a specified salesperson. It works by adding or subtracting an amount from the current value in that column. Listing 3 shows the script I used to create the procedure. Notice that I include two input parameters-@SalesPersonID and @SalesAmt-which coincide with the table’s SalesPersonID and SalesLastYear columns.
IF OBJECT_ID('UpdateSales', 'P') IS NOT NULL
DROP PROCEDURE UpdateSales;
CREATE PROCEDURE UpdateSales
@SalesAmt MONEY = 0
SET SalesLastYear = SalesLastYear + @SalesAmt
WHERE SalesPersonID = @SalesPersonID;
IF @@TRANCOUNT > 0
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
The main body of the procedure definition, enclosed in the BEGIN…END block, contains the TRY…CATCH block, which itself is divided into the TRY block and the CATCH block. The TRY block starts with BEGINTRY and ends with ENDTRY and encloses the T-SQL necessary to carry out the procedure’s actions. In this case, I include an UPDATE statement that adds the @SalesAmount value to the SalesLastYear column. The statement is enclosed in BEGINTRANSACTION and COMMITTRANSACTION statements to explicitly start and commit the transaction. Examples vary in terms of where they include the transaction-related statements. (Some don’t include the statements at all.) Just keep in mind that you want to commit or rollback your transactions at the appropriate times, depending on whether an error has been generated.
If the UPDATE statement runs successfully, the SalesLastYear value is updated and the operation is completed, in which case, the code in the CATCH block is never executed. However, if the UPDATE statement fails and SQL Server generates an error, the transaction is terminated and the database engine jumps to the CATCH block. The CATCH block starts with BEGINCATCH and ends with ENDCATCH and encloses the statements necessary to handle the error.
For the stored procedure in Listing 3, the first step I take in the CATCH block is to roll back the transaction if it is still running. I start by using the @@TRANCOUNT function to determine whether any transactions are still open. @@TRANCOUNT is a built-in SQL Server function that returns the number of running transactions in the current session. In this case, there should be only one (if an error occurs), so I roll back that transaction.
Next, I declare a set of variables based on system functions that SQL Server makes available within the scope of the CATCH block. The functions return error-related information that you can reference in your T-SQL statements. Currently, SQL Server supports the following functions for this purpose:
- ERROR_NUMBER(): The number assigned to the error.
- ERROR_LINE(): The line number inside the routine that caused the error.
- ERROR_MESSAGE(): The error message text, which includes the values supplied for any substitutable parameters, such as times or object names.
- ERROR_SEVERITY(): The error’s severity.
- ERROR_STATE(): The error’s state number.
- ERROR_PROCEDURE(): The name of the stored procedure or trigger that generated the error.
For this example, I use all but the last function, though in a production environment, you might want to use that one as well.
After I declare the variables, I include two PRINT statements that display the values of the @ErrorNumber and @ErrorLine variables (along with some explanatory text). The reason I do this is to demonstrate the difference between what the actual values are and what the RAISERROR statement returns, as you’ll see shortly.
The RAISERROR statement comes after the PRINT statements. The statement returns error information to the calling application. Generally, when using RAISERROR, you should include an error message, error severity level, and error state. The rules that govern the RAISERROR arguments and the values they return are a bit complex and beyond the scope of this article, but for the purposes of this example, I simply pass in the @ErrorMessage, @ErrorSeverity, and @ErrorState variables as arguments.
That’s basically all you need to do to create a stored procedure that contains a TRY…CATCH block. In a moment, we’ll try out our work. But first, let’s retrieve a row from the LastYearSales table to see what the current value is for salesperson 288. Listing 4 shows the SELECT statement I used to retrieve the data.