What happens with this type of scenario?
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 guess is that because XACT_ABORT is ON the COMMIT TRANSACTION never happens (because the whole thing is rolled back and terminated), and neither does the last statement (checking for @@error and then calling raiseerror).
Correct.
SET XACT_ABORT jumps out of the batch. Your IF is part of the same batch.
If you want some processing on error, then 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'm also intrigued by severity 20 because it breaks the connection. Usually you'd use 16 which is user defined error.