May
18
2012
Generally in my day to day duties I have no need for SQL based error catching but lately I have been working on a newspaper subscription system and a lot of key business logic is held in stored procedures for processing of subscriptions week by week and issuing lost or non-delivered vouchers as required. Now as I am talking about customers actually receiving a product I needed to be sure that everything that needed to happen, actually happened and if an error occurred I would first like to try and resolve any of these issues and carry on but when or if an error occurred that would make me less than 100% confident that everything that needed to happen actually happened then I want all changes to be reverted and to be notified.
Firstly I started off with a simple TRY CATCH statement, because this is a mission critical procedure, if any errors occurred I want everything to revert so I only need to capture a generic exception.
BEGIN TRY
-- SQL COMMANDS
END TRY
BEGIN CATCH
-- ERROR HANDELING, IN THIS CASE I SENT AN EMAIL
EXEC msdb.dbo.sp_send_dbmail @profile_name='DB Error Email',
@recipients='email@yourdomain.com',
@subject='Procedure ABC Failed',
@body='The stored procedure #ABC# Failed, all changes have been rolled back.'
END CATCH
Now that I am catching all errors I need to commit all changes if my procedure executed successfully or I need to revert my changes back. Luckily MSSQL 2008 makes this very simple. Taking the above example I did the following.
BEGIN TRY
BEGIN TRANSACTION
-- SQL COMMANDS
EXEC msdb.dbo.sp_send_dbmail @profile_name='DB Email',
@recipients='email@yourdomain.com',
@subject='Procedure ABC Successful',
@body='The stored procedure #ABC# completed successfully, all changes have been comited.'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- ERROR HANDELING, IN THIS CASE I SENT AN EMAIL
EXEC msdb.dbo.sp_send_dbmail @profile_name='DB Email',
@recipients='email@yourdomain.com',
@subject='Procedure ABC Failed',
@body='The stored procedure #ABC# Failed, all changes have been rolled back.'
END CATCH
This now ensures that my procedure runs successfully, commits changes and then emails me to let me know, or it rollbacks any changes upon an error and lets me know. Very cool stuff and very very simple.