@@ERROR and/or TRY - CATCH

The following article is a must read by Erland Sommarskog, SQL Server MVP: Implementing Error Handling with Stored Procedures

Also note that Your TRY block may fail, and your CATCH block may be bypassed

One more thing: Stored procedures using old-style error handling and savepoints may not work as intended when they are used together with TRY … CATCH blocks.Avoid mixing old and new styles of error handling.

TRY/CATCH traps more. It's hugely and amazingly better.

DECLARE @foo int

SET @foo = 'bob' --batch aborting pre-SQL 2005
SELECT @@ERROR
GO
SELECT @@ERROR  --detects 245. But not much use, really if the batch was a stored proc
GO


DECLARE @foo int
BEGIN TRY
    SET @foo = 'bob'
    SELECT @@ERROR
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE(), ERROR_NUMBER()
END CATCH
GO

Using TRY/CATCH in triggers also works. Trigger rollbacks used to be batch aborting too: no longer if TRY/CATCH is used in the trigger too.

Your example would be better if the BEGIN/ROLLBACK/COMMIT is inside, not outside, the construct