Recently I tried to tune one of our SP for locking issue by breaking the lengthy transaction to 5 different logical blocks. The SP was written in Sql 2000 and using @@ERROR logic to handle Errors.
After breaking the transactions, I was trying to test what if any transaction block fails and how good the data integrity is. How ever, In case of error (I tried to change the table column name or tried to have update query with in the transaction which will update char value to numeric column), the control is NOT even coming down where the next statement tries to capture @@error and assigning error number to variable since SP execution stops in the errored statment itself .
After seeing the failure of Error handling (using @@Error), I tried with TRY..CATCH and could see the error is caught and I could roll back the transaction.
Now my confusion is how powerful is using @@ERROR and what to choose between TRY..CATCH and @@ERROR?