With SQL Server 2008 Microsoft has added .NET like error handling to SQL Server by adding support for TRY – CATCH blocks. I like this new feature since the old @@ERROR syntax was quite unwieldy and the TRY – CATCH just seems more intuitive to use.
Here are some of the features of the new TRY – CATCH blocks in SQL Server 2008:
- When an error is encountered in a TRY block, then the code passes into the CATCH block where the error can be handled.
- Having multiple statements in the TRY block is equivalent to adding the @@ERROR command after each statement.
- TRY – CATCH blocks can be nested
- SQL Server 11 (Denali) will introduce the THROW command that allows one to re-raise the error (and would support re-raising errors in a nested hierarchy)
Below is an example I’ve written of using a TRY-CATCH statement with a Transaction to handle multiple insert statements, one of which causes a data-type conversion error. As expected, the Insert statements execute until the error is thrown, after which no further insert statements are run, but the code enters into the CATCH part of the block and outputs the encountered error number and description.
DECLARE @ProductTotals TABLE
(
ProductID int,
Revenue money
)
INSERT INTO @ProductTotals (ProductID,Revenue) VALUES (12,100)
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO @ProductTotals (ProductID,Revenue) VALUES (5,200)
INSERT INTO @ProductTotals (ProductID,Revenue) VALUES ('abc',300)
INSERT INTO @ProductTotals (ProductID,Revenue) VALUES (7,400)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE() as ErrorMessage;
ROLLBACK TRANSACTION
END CATCH
SELECT * FROM @ProductTotals
Running this code in Query Analyzer results in an error message from the CATCH portion of the block:
245 Conversion failed when converting the varchar value 'abc' to data type int.
And the Select query on the @ProductTotals table variable results in two records returned:
12 100.00 5 200.00
So one can see how this is definitely a useful feature of SQL Server 2008!
References:
Justin Cooney 