SQL, SQL Server 2008, SQL Tips, Transact-SQL, tSQL

SQL Server 2008 – Using TRY – CATCH Blocks

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:
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s