TSQL Event Handling with TRY and CATCH in SQL Server 2005 - simple method

Event Handling is a major part in any application development that includes the error-handling code that is producing errors.

Until previous versions of SQL Server capturing error-handling code is bit hard to implement, as you may be aware within SQL 2005 TRY and CATCH are new methods to make your life easier, these two constructs allow you to handle transaction abort errors that would otherwise have resulted in the termination of a batch, provided that those errors do not cause severance of the connection. The following is the method I have been implementing within our code whenever spurious errors are returned to resolve:

Firstly you need to create worktables with a primary key and another table with a foreign key that constructs values to primary key colum of the first table.

USE AdventureWorks

GO

 

CREATE TABLE ExceptionT1

(

 a int NOT NULL PRIMARY KEY

)

CREATE TABLE ExceptionT2

(

 a int NOT NULL REFERENCES ExceptionT1(a)

)

GO

 

INSERT INTO ExceptionT1 VALUES (1)

INSERT INTO ExceptionT1 VALUES (3)

INSERT INTO ExceptionT1 VALUES (4)

INSERT INTO ExceptionT1 VALUES (6)

Execute the above set of TSQL statements that will create the tables along with set of sample data values.

 Now comes generating run-time error for your code, you may be aware when using SET XACT_ABORT option to OFF then only TSQL statement that raised the corresponding error will be rolled back and transaction will continue processing.

SET XACT_ABORT OFF

BEGIN TRAN

 INSERT INTO ExceptionT2 VALUES (1)

 INSERT INTO ExceptionT2 VALUES (2) /* Foreign Key Error */

 INSERT INTO ExceptionT2 VALUES (3)

COMMIT TRAN

GO

Now you will get the following error:

Msg 547, Level 16, State 0, Line 1
INSERT statement conflicted with FOREIGN KEY constraint 'FK__ExceptionT2__a__36D11DD4. The conflict occurred in database 'AdventureWorks', table 'ExceptionT1', column 'a'.
The statement has been terminated

You can now view the resulting worktables values by using following query: 

SELECT *

FROM ExceptionT1

GO

 

SELECT *

FROM ExceptionT2

GO

What you will see will be interesting where the first table that is populated will have all the four records that are inserted with first script. These values are corresponded to valid primary key values and with SET XACT_ABORT OFF statement the error is simply handled but remaining statements are committed as usual which is a expected process. Now we can implement using TRY/CATCH constructs to invoke the run-time error. 

SET XACT_ABORT OFF

BEGIN TRY

 BEGIN TRAN

  INSERT INTO ExceptionT2 VALUES (4)

  INSERT INTO ExceptionT2 VALUES (5) /* Foreign Key Error */

  INSERT INTO ExceptionT2 VALUES (6)

 COMMIT TRAN

 PRINT 'Transaction committed'

END TRY

BEGIN CATCH

 ROLLBACK

 PRINT 'Transaction rolled back'

END CATCH

GO

You will notice a 'Transaction rolled back' value within the RESULTS pane of query editor window, again you will need to view the values from worktables.

SELECT *

FROM ExceptionT1

GO

 

SELECT *

FROM ExceptionT2

GO

Here is intersting final part of this process, what you will notice is the same two records in ExceptionT2 table. Even though remaining two INSERT statements for second table would not have violed the foreign key constraint. Withint he unconditional ROLLBACK statment on the above script has issued within a CATCH block where the entire transaction is terminated and rolled back to starting point on TRY block. THis is nothing but regardless of SET XACT_ABORT OFF statement whatever has been capture within TRY block has terminated the process.

Books online refers:

RAISERROR can be used as an alternative to PRINT to return messages to calling applications. RAISERROR supports character substitution similar to the functionality of the printf function in the C standard library, while the Transact-SQL PRINT statement does not. The PRINT statement is not affected by TRY blocks, while a RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block. Specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block.

You can also write your user-defined error messages within the system table to identify which part of query is failing to identify the set of resolution to the problem.