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.