TRY CATCH Block (Exception Handling in SQL Server)
The TRY or CATCH block can contain a single T-SQL statement or a
series of statements. The CATCH block must follow immediately after the
TRY block. The TRY/CATCH block cannot span more than a single batch. In
addition, TRY/CATCH block cannot span an IF/ELSE statement.
Above code will return following result:
If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block, but instead skips over the CATCH block and executes the first statement following the END CATCH statement. Removing SET statement in above code PRINT ‘Error Detected’ statement is not executed, but the PRINT statement within the TRY block is executed, as well as the PRINT statement after the TRY/CATCH block. TRY/CATCH blocks can be nested.
Example of TRY…CATCH:
BEGIN TRY
DECLARE @X INT
---- Divide by zero to generate Error
SET @X = 1/0
PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
PRINT 'Error Detected'
END CATCH
PRINT 'Command after TRY/CATCH blocks'Above code will return following result:
Error Detected
Command after TRY/CATCH blocksIf all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block, but instead skips over the CATCH block and executes the first statement following the END CATCH statement. Removing SET statement in above code PRINT ‘Error Detected’ statement is not executed, but the PRINT statement within the TRY block is executed, as well as the PRINT statement after the TRY/CATCH block. TRY/CATCH blocks can be nested.
Limitation of TRY…CATCH:
- Compiled errors are not caught.
- Deferred name resolution errors created by statement level recompilations. (If process is terminated by Kill commands or broken client connections TRY…CATCH will be not effective)
- Errors with a severity greater than 10 that do not terminate their database connection are caught in the TRY/CATCH block.
For errors that are not trapped, SQL
Server 2005 passes control back to the application immediately, without
executing any CATCH block code.
–> Following rules should be taken care off while using TRY-CATCH constructs:
– A TRY block must be followed immediately by the CATCH block.
– Both TRY & CATCH blocks must be inside a Batch, Stored Procedure or a Trigger.
– Only Errors with severity between 10 & 20 that do not close the database connection are caught & handled by TRY-CATCH constructs.
– As per MS BOL, Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block. And Errors that have a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.
–> let’s check how to use TRY-CATCH block:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
| USE [tempdb]GO--// Create a test Stored ProcedureCREATE PROC testPrc (@val VARCHAR(10))ASBEGIN SELECT 1/@val AS operationENDGO--// Test for Divide by 0 (Divide by zero error encountered.)BEGIN TRY EXEC testPrc '0'END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINEEND CATCHGO--// Test for Datatype conversion (Conversion failed when converting the varchar value 'a' to data type int.)BEGIN TRY EXEC testPrc 'a'END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINEEND CATCHGO--// Test nested TRY-CATCH for "Divide by 0" & "Datatype conversion" errors both.BEGIN TRY EXEC testPrc 'a'END TRYBEGIN CATCH SELECT 'outer block', ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINE BEGIN TRY SELECT 1/0 AS operation END TRY BEGIN CATCH SELECT 'inner block', ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINE END CATCHEND CATCHGO--// Test for violation of PK Constraint (Violation of PRIMARY KEY constraint 'PK__testTable__2C3393D0'. Cannot insert duplicate key in object 'dbo.testTable'.)BEGIN TRY CREATE TABLE testTable (a INT PRIMARY KEY) INSERT INTO testTable VALUES(1) INSERT INTO testTable VALUES(1)END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINEEND CATCHGOSELECT * FROM testTable -- Contains single record with value 1--// Test for recreating a table that already exists (There is already an object named 'testTable' in the databASe.)BEGIN TRY CREATE TABLE testTable (a INT PRIMARY KEY)END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINEEND CATCHGO--// Test for inserting NULL value on Primary Key column (Cannot insert the value NULL into column 'a', table 'tempdb.dbo.testTable'; column does not allow nulls. INSERT fails.)BEGIN TRY INSERT INTO testTable VALUES(NULL)END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINEEND CATCHGO--// Final CleanupDROP TABLE testTableDROP PROC testPrc
GO
|
TRY…CATCH Block in Stored Procedures
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
ALTER TABLE LastYearSales
ADD CONSTRAINT ckSalesTotal CHECK (SalesLastYear >= 0);
GO
Listing 2: Adding a check constraint to the LastYearSales table
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
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.
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.
SELECT FullName, SalesLastYear
FROM LastYearSales
WHERE SalesPersonID = 288
Listing 4: Retrieving date from the LastYearSales table
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
EXEC UpdateSales 288, 2000000;
Listing 6: Running the UpdateSales stored procedure
Notice all the extra cash.
FullName SalesLastYear
Rachel Valdez 3307949.7917
Listing 7: Viewing the updated sales amount in the LastYearSales table
EXEC UpdateSales 288, -4000000;
Listing 8: Causing the UpdateSales stored procedure to throw an error
(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
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.
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
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
(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.
No comments:
Post a Comment