SET XACT_ABORT ON :
SET XACT_ABORT specifies what action SQL Server should take following run-time errors. The default session setting is SET XACT_ABORT OFF, which indicates that only the Transact-SQL statement that raised the error is rolled back and the transaction continues. Depending on the severity of the error, the entire transaction may be rolled back and batch aborted, even with SET XACT_ABORT is OFF.
SET XACT_ABORT ON be included in all stored procedures with explicit transactions unless you have a specific reason to do otherwise. The consequences of an application unwittingly performing work on a connection with an open transaction are disastrous.
