Geek asks and answers

What's going on here? SQL Server - XACT_ABORT ON + @@ ERROR Validation.,

What happens with this type of script?

SET XACT_ABORT ON

BEGIN TRANSACTION

    ---DO SOMETHING HERE THAT CAUSES AN ERROR

COMMIT TRANSACTION

if @@error != 0
raiserror('SP failed. Step 7.', 20, -1) with log GO

My assumption is that, since XACT_ABORT- ON, COMMIT TRANSACTIONnever happens (because all this is a rollback and completion), as well as the last statement (checking for @@error, and then calling raiseerror>).

+3
source share
1 answer

Correctly.

SET XACT_ABORT jumps out of the game. Your IF is part of the same batch.

If you want to handle the error, use BEGIN TRY, etc.

SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION

    ---DO SOMETHING HERE THAT CAUSES AN ERROR

COMMIT TRANSACTION
END TRY
BEGIN CATCH
    raiserror('SP failed. Step 7.', 20, -1) with log
END CATCH
GO

I am also intrigued by the seriousness of 20 because it breaks the connection. Usually you should use 16, which is a user-defined error.

+3
source

More articles:


All Articles