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)
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
245 Conversion failed when converting the varchar value 'abc' to data type int.
12 100.00 5 200.00
So one can see how this is definitely a useful feature of SQL Server 2008!