Since the release of SQL Server 2005, you’ve been able to handle errors in your T-SQL code by including a TRY…CATCH
block that controls the flow of your script should an error occur,
similar to how procedural languages have traditionally handled errors.
The TRY…CATCH
block makes it easy to return or audit error-related data, as well as
take other actions. And within the block—specifically, the CATCH portion—you’ve been able to include a RAISERROR
statement in order to re-throw error-related data to the calling
application. However, with the release of SQL Server 2012, you now have a
replacement for RAISERROR, the THROW statement, which makes it easier than ever to capture the error-related data.
In this article, we’ll look at the
TRY…CATCH block used with both the
RAISERROR and
THROW statements. The examples are based on a table I created in the
AdventureWorks2012 sample database, on a local instance of SQL Server 2012. Listing 1 shows the T-SQL script I used to create the
LastYearSales table.
USE AdventureWorks2012;
GO
IF OBJECT_ID('LastYearSales', 'U') IS NOT NULL
DROP TABLE LastYearSales;
GO
SELECT
BusinessEntityID AS SalesPersonID,
FirstName + ' ' + LastName AS FullName,
SalesLastYear
INTO
LastYearSales
FROM
Sales.vSalesPerson
WHERE
SalesLastYear > 0;
GO
Listing 1: Creating the LastYearSales table
The script should be fairly straightforward. I use a
SELECT…INTO statement to retrieve data from the
Sales.vSalesPerson
view and insert it into the newly created table. However, to
demonstrate how to handle errors, we need to add one more element to our
table: a check constraint that ensures the
SalesLastYear value is never less than zero. Listing 2 shows the
ALTERTABLE statement I used to add the constraint.
ALTER TABLE LastYearSales
ADD CONSTRAINT ckSalesTotal CHECK (SalesLastYear >= 0);
GO
Listing 2: Adding a check constraint to the LastYearSales table
The constraint makes it easy to generate an error when updating the
table. All I have to do is try to add a negative amount to the
SalesLastYear
column, an amount large enough to cause SQL Server to throw an error.
Once we’ve created our table and added the check constraint, we have the
environment we need for the examples in this article. You can just as
easily come up with your own table and use in the examples. Just be sure
you have a way of violating a constraint or you come up with another
mechanism to generate an error. The goal is to create a script that
handles any errors.
Working with the TRY…CATCH Block
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.
USE AdventureWorks2012;
GO
IF OBJECT_ID('UpdateSales', 'P') IS NOT NULL
DROP PROCEDURE UpdateSales;
GO
CREATE PROCEDURE UpdateSales
@SalesPersonID INT,
@SalesAmt MONEY = 0
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE LastYearSales
SET SalesLastYear = SalesLastYear + @SalesAmt
WHERE SalesPersonID = @SalesPersonID;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
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);
END CATCH
END;
GO
Listing 3: Creating a stored procedure that contains a Try…Catch block
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.
NOTE: For more information about the RAISERROR statement, see the topic “
RAISERROR (Transact-SQL)” in SQL Server Books Online.
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.
SELECT FullName, SalesLastYear
FROM LastYearSales
WHERE SalesPersonID = 288
Listing 4: Retrieving date from the LastYearSales table
Not surprisingly, the statement returns the name and total sales for
this salesperson, as shown in Listing 5. As you can see, Rachel Valdez
shows over $1.3 million dollars in sales for last year.
FullName SalesLastYear
Rachel Valdez 1307949.7917
Listing 5: Data retrieved from the LastYearSales table
Now let’s try out the
UpdateSales stored procedure. Just for fun, let’s add a couple million dollars to Rachel Valdez’s totals. Listing 6 shows how I use the
EXEC statement to call the procedure and pass in the salesperson ID and the $2 million.
EXEC UpdateSales 288, 2000000;
Listing 6: Running the UpdateSales stored procedure
The stored procedure should run with no problem because we’re not violating the check constraint. If we were to execute the
SELECT statement again (the one in Listing 4), our results would look similar to those shown in Listing 7. Notice all the extra cash.
FullName SalesLastYear
Rachel Valdez 3307949.7917
Listing 7: Viewing the updated sales amount in the LastYearSales table
Now let’s look what happens if we subtract enough from her account to
bring her totals to below zero. In listing 8, I run the procedure once
again, but this time specify
-4000000 for the amount.
EXEC UpdateSales 288, -4000000;
Listing 8: Causing the UpdateSales stored procedure to throw an error
As you’ll recall, after I created the
LastYearSales
table, I added a check constraint to ensure that the amount could not
fall below zero. As a result, the stored procedure now generates an
error, which is shown in Listing 9.
(0 row(s) affected)
Actual error number: 547
Actual line number: 9
Msg 50000, Level 16, State 0, Procedure UpdateSales, Line 27
The UPDATE statement conflicted with the CHECK constraint "ckSalesTotal". The conflict occurred in database "AdventureWorks2012", table "dbo.LastYearSales", column 'SalesLastYear'.
Listing 9: The error message returned by the UpdateSales stored procedure
As expected, the information we included in the
CATCH block has been returned. But notice that the actual error number (
547) is different from the
RAISERROR message number (
50000) and that the actual line number (
9) is different from the
RAISERROR line number (
27). In theory, these values should coincide. But as I mentioned earlier, the rules that govern
RAISERROR are a bit quirky.
Working with the THROW Statement
To simplify returning errors in a
CATCH block, SQL Server 2012 introduced the
THROW statement. With the
THROW
statement, you don’t have to specify any parameters and the results are
more accurate. You simply include the statement as is in the
CATCH block.
NOTE: You can use the
THROW statement outside of the
CATCH block, but you must include parameter values to do so. For more information about the
THROW statement, see the topic “
THROW (Transact-SQL)” in SQL Server Books Online.
To demonstrate the
THROW statement, I defined an
ALTER PROCEDURE statement that modifies the
UpdateSales procedure, specifically the
CATCH block, as shown in Listing 10.
ALTER PROCEDURE UpdateSales
@SalesPersonID INT,
@SalesAmt MONEY = 0
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
UPDATE LastYearSales
SET SalesLastYear = SalesLastYear + @SalesAmt
WHERE SalesPersonID = @SalesPersonID;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));
THROW;
END CATCH
END;
GO
Listing 10: Altering the UpdateSales stored procedure
Notice that I retain the
@ErrorNumber and
@ErrorLine variable declarations and their related
PRINT statements. I do so only to demonstrate the
THROW statement’s accuracy. In actually, I need only to roll back the transaction and specify the
THROW statement, without any parameters.
Now let’s execute the stored procedure again, once more trying to
deduct $4 million from the sales amount, as shown in Listing 11.
EXEC UpdateSales 288, -4000000;
Listing 11: Causing the UpdateSales stored procedure to throw an error
Once again, SQL Server returns an error. Only this time, the
information is more accurate. As you can see in Listing 12, the message
numbers and line numbers now match. No longer do we need to declare
variables or call system functions to return error-related information
to the calling application.
(0 row(s) affected)
Actual error number: 547
Actual line number: 8
Msg 547, Level 16, State 0, Procedure UpdateSales, Line 8
The UPDATE statement conflicted with the CHECK constraint "ckSalesTotal". The conflict occurred in database "AdventureWorks2012", table "dbo.LastYearSales", column 'SalesLastYear'.
Listing 12: The error message returned by the UpdateSales stored procedure
As you can see, SQL Server 2012 makes handling errors easier than ever. Even if you’ve been using the
TRY…CATCH block for a while, the
THROW statement should prove a big benefit over
RAISERROR. And if you’re new to error handling in SQL Server, you’ll find that the
TRY…CATCH block and the
THROW
statement together make the process a fairly painless one, one well
worth the time and effort it takes to learn and implement them.
