SQL Server 2005 introduced a new way of error handling, using try catch blocks similar to what you'll find in Java, C#, or VB.NET.
Here is a template I've been using on the project that I'm currently on to support transactions:
CREATE PROCEDUR ...
Read post