Raymond Lewallen

SQL Server 2005 provides a new exception-handling mechanism in the form of TRY...CATCH. In current version of Sql Server you have to include GOTO statements, and check @@ERROR after every statement is executed to determine if an error had occurred at that particular point and terminate your process or rollback your transaction. This is very ugly indeed. Now Sql Server 2005 provides us with a way to handle those exceptions with the familiar TRY...CATCH blocks. Note: TRY...CATCH blocks in Sql Server cannot handle errors that cause a connection to be lost, i.e. any severity 21 error.

Let's look at this simple block of code:

BEGIN TRY
  BEGIN TRAN
  INSERT Northwind.dbo.Region(RegionId, RegionDescription) VALUES (5,'CENTRAL')
  PRINT 'Added Central Region'
  INSERT Northwind.dbo.Region(RegionId, RegionDescription) VALUES (5,'ANOTHER REGION')
  PRINT 'Added Another Region'
  COMMIT TRAN
END TRY
BEGIN CATCH
  PRINT 'The following error has occurred:  ' + ERROR_MESSAGE()
  ROLLBACK TRAN
END CATCH

The following output will be produced:

Added Central Region
The following error has occurred: Violation of PRIMARY KEY constraint 'PK_Region'. Cannot insert duplicate key in object 'Region'.

Well that is simple enough. Note that unlike .Net, you cannot leave out the CATCH statement in T-SQL. Not much change to existing code in order to get this implemented, other than taking out a bunch of labels, GOTOs and @@ERROR > 0 checks.
Here's another example:

BEGIN TRY
  BEGIN TRAN
  INSERT Northwind.dbo.Region(RegionId, RegionDescription) VALUES (5,'CENTRAL')
  PRINT 'Added Central Region'
  INSERT Northwind.dbo.Region(RegionId, RegionDescription) VALUES (6,NULL)
  PRINT 'Added NULL Region'
  COMMIT TRAN
END TRY
BEGIN CATCH
  PRINT 'The following error has occurred:  ' + ERROR_MESSAGE()
  ROLLBACK TRAN
END CATCH

The following output will be produced:

Added Central Region
The following error has occurred: Cannot insert the value NULL into column 'RegionDescription', table 'Northwind.dbo.Region'; column does not allow nulls. INSERT fails.

Now lets combine the two examples together and look at how we capture a specific error that occurred using ERROR_NUMBER(). Also, I'm introducing the new XACT_STATE() into this example. XACT_STATE() does exactly what you think it does, it returns the state of the transaction. Return values 0 for no transaction is open and you cannot commit or rollback, 1 means that a transaction is open and can committed or rolled back based on how you need to handle your situation, and new to Sql Server 2005 is a transaction state of -1, which means that a transaction is open but cannot be committed. A transaction within a TRY block reaches this state when an error occurs that would normally cause the transaction to be aborted. You cannot continue to change data once the transaction reaches a point of not being able to be committed because an uncommittable transaction keeps all locks open and allows you to only read data. At this point, the only way to terminate the transaction is to roll it back. Let's look at this example:

BEGIN TRY
  BEGIN TRAN
  INSERT Northwind.dbo.Region(RegionId, RegionDescription) VALUES (5,'CENTRAL')
  PRINT 'Added Central Region'
  INSERT Northwind.dbo.Region(RegionId, RegionDescription) VALUES (5,'ANOTHER REGION')
  PRINT 'Added Another Region'
  INSERT Northwind.dbo.Region(RegionId, RegionDescription) VALUES (6,NULL)
  PRINT 'Added NULL Region'
  COMMIT TRAN
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627
BEGIN
    PRINT 'Statement violates primary key constraints.'
    IF (XACT_STATE()) <> 0 -- We are in a transaction and want to roll it back
        ROLLBACK TRAN
END
ELSE IF ERROR_NUMBER() = 515
BEGIN
    PRINT 'Statement attempted to insert a NULL where a NULL is not allowed.'
    IF (XACT_STATE()) = -1 -- Can only rollback
        ROLLBACK TRAN
    ELSE IF (XACT_STATE()) = 1 -- We can do whatever we want, commit or roll back
        COMMIT TRAN
END

PRINT 'Error Number:   ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
PRINT 'Error Message:  ' + ERROR_MESSAGE()
PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))
PRINT 'Error State   : ' + CAST(ERROR_STATE() AS VARCHAR(10))
END CATCH

Now the following output will be produced for this block of code:

Added Central Region
Statement violates primary key constraints.
Error Number: 2627
Error Message: Violation of PRIMARY KEY constraint 'PK_Region'. Cannot insert duplicate key in object 'Region'.
Error Severity: 14
Error State : 1

Notice how I can still COMMIT the transaction if I want to depending on the specific error that occurred. I may not care so much that the NULL didn't get inserted, but I want to go ahead and commit the other inserts. By checking the ERROR_NUMBER() in my CATCH block, I can exactly determine which error occurred and how I want to handle the situation.