Error handling in transact SQL used to be a tortures process prior to SQL Server 2005 and for some SQL developers still is, but in SQL Server 2005 and above Microsoft have introduced
TRY ... CATCH blocks similar to the functionality in C# and vb.net.
Before we take a look at error handling with the
TRY ... CATCH block lets look at the traditional custom way we would have handled errors in SQL, the below examples use a user defined table type that we created in an earlier article which you can find here.
The traditional method would have been to test for an error after certain or all SQL statements within a procedure so for example below we define our table type variable called @EmployeeTempTable which has a primary key constraint on the employee ID field as per our earlier article. Then we start a transaction after which we have two insert statements and after each insert statement we check to see if there is an error code and if there is we direct the flow of the code to a ERR_Handler label where we handle the error and roll-back the transaction.
(Note, the below code should throw an error as we are trying to insert a duplicate employee ID.)
DECLARE @EmployeeTempTable EmployeeTableType BEGIN TRANSACTION INSERT INTO @EmployeeTempTable VALUES(1,'J Smith',123.12,0,'20140830') IF @@ERROR <> 0 GOTO ERR_Handler INSERT INTO @EmployeeTempTable VALUES(1,'S Hancock',89.12,23,'20140830') IF @@ERROR <> 0 GOTO ERR_Handler SELECT * FROM @EmployeeTempTable COMMIT TRANSACTION ERR_Handler: IF @@ERROR <> 0 BEGIN /* Handle Error */ PRINT ERROR_MESSAGE() ROLLBACK TRANSACTION END
Just from reading the above example you can see that potentially there could be a lot of duplication of the
IF @@ERROR <> 0 GOTO ERR_Handler line depending on the size and complexity of the procedure you are working with which if nothing else will make just reading the code that little bit harder.
Now lets look at the same code example but using a
TRY ... CATCH instead, I have removed the
IF @@ERROR <> 0 GOTO ERR_Handler lines and this time the error is handled in the catch block.
BEGIN TRY DECLARE @EmployeeTempTable EmployeeTableType BEGIN TRANSACTION INSERT INTO @EmployeeTempTable VALUES(1,'J Smith',123.12,0,'20140830') INSERT INTO @EmployeeTempTable VALUES(1,'S Hancock',89.12,23,'20140830') SELECT * FROM @EmployeeTempTable COMMIT TRANSACTION END TRY BEGIN CATCH /* Handle Error */ PRINT ERROR_MESSAGE() ROLLBACK TRANSACTION END CATCH
If you have used
TRY ... CATCH in C# or vb.net then the above code should look familiar to you, if not then essentialy you place the code you want to execute within the TRY block and if any error occurs during execution of any SQL statement within the TRY block scope then the CATCH block will be executed by SQL server and within here you are able to handle the error gracefully and rollback any transactions as needed.
For more information on
TRY ... CATCH in SQL server check out the MSDN page http://msdn.microsoft.com/en-us/library/ms175976(v=sql.90).aspx