Transaction management

In the context of a transaction each work-unit performed in a database must either complete in its entirety or rollback entirely.
In SQL Server an "normal" exception does not rollback a transaction by default.

In the old days the T-SQL  to manage a transaction was tedious.
An error variable must be checked to rollback the transaction if needed.
 Example:

BEGIN TRANSACTION 

...
IF (@@ERROR <> 0)
BEGIN
   ROLLBACK TRANSACTION 
    RAISERROR ('Error Description.', 16, 1)
END

COMMIT TRANSACTION

But the newer versions of SQL Server support TRY CATCH block, that simplifies the process:

BEGIN TRY
BEGIN TRANSACTION

...

IF @@TRANCOUNT > 0
BEGIN
    COMMIT TRANSACTION
END

END TRY
BEGIN CATCH

 DECLARE @ErrorMessage VARCHAR(4000)
  
 SET @ErrorMessage = 'Procedure: ' + ISNULL(ERROR_PROCEDURE(), '') + ' Line: ' + 
CAST(ERROR_LINE() AS VARCHAR(10)) + ' Message: ' + ERROR_MESSAGE()
  
 IF @@TRANCOUNT > 0
 BEGIN
  ROLLBACK TRANSACTION;
 END
  
 RAISERROR (@ErrorMessage, 16, 1)  

END CATCH;

Another way of doing the same is using SET XACT_ABORT. I don't recommend using it, but in some temporary scripts it can be useful.

The SET XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a statement raises a run-time error.

SET XACT_ABORT ON
BEGIN TRANSACTION

....

COMMIT TRANSACTION

Note also that the RAISERROR stament does not honor the XACT_ABORT setting, so if an error occurs there is no automatic rollback even if XACT_ABORT is ON.

Sql Server 2012 implements the THROW statement that is suggested by Microsoft to be used instead of the RAISERROR.
The main difference is that the THROW:
1) Can re-throw the original exception that is caught in the CATCH block.
2) Causes the statement batch to be ended and the next statements aren't executed.
3) There is no severity parameter.The exception severity is always set to 16.
4) Requires preceding statement to end with semicolon (;).
5) Default THROW statement will show the exact line where the exception was occurred


A simple usage example:

BEGIN TRY
BEGIN TRANSACTION

...

IF @@TRANCOUNT > 0
BEGIN
    COMMIT TRANSACTION
END

END TRY
BEGIN CATCH 
 IF @@TRANCOUNT > 0
 BEGIN
  ROLLBACK TRANSACTION;
 END;
  
  THROW  

END CATCH;

Note that Microsoft states that: "The THROW statement honors SET XACT_ABORT RAISERROR does not. New Applications should use THROW instead of RAISERROR."

The THROW syntax is:
THROW [error number] [message] [state]

The two first parameters are self explanatory.
The state parameter is a constant or variable between 0 and 255 that indicates the state to associate with the message.
CodeProject