TRY...CATCH in SQL Server 2005

http://www.4guysfromrolla.com/webtech/041906-1.shtml

because the @@ERROR variable is set after every SQL statement. Therefore, if the first DELETE statement has an error the @@ERROR variable will be set to its error number. Then, the second DELETE will execute. If this second DELETE succeeds, @@ERROR will be set back to 0, in which case the transaction will be committed even though there was a problem with the first statement! Whoops!

The SQL Server 2005 TRY...CATCH block executes a number of statements in the TRY block. If there are no errors in any of the statements, control proceeds to after the CATCH block. If, however, one of the statements causes an error, control branches immediately to the start of the CATCH block.

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

BEGIN TRY
BEGIN TRANSACTION -- Start the transaction

-- Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers
WHERE EmployeeID = @EmployeeID

-- Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID

-- If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH