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.
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 TRANSACTIONNote 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.