3

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

richard
  • 12,263
  • 23
  • 95
  • 151

1 Answers1

3

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • @gbn: Thanks. Because that was in a try...catch...if there are statements after that, they will execute, correct? And if they do, will they react to xact_abort the same way, ie. will xact_abort apply to every transaction in the SP? Or just the first one? Sounds like a dumb question I know but I just want to be sure. – richard May 11 '11 at 19:22
  • And just to clarify . . . without the try/catch, the whole thing terminates, no statements after are going to be executed. With a try/catch, it will abort just that transaction, but then move on to the next statement? – richard May 11 '11 at 19:23
  • XACT_ABORT ON applies to the whole scope/batch. Which is a stored proc. And yes, code after the CATCH executes – gbn May 11 '11 at 19:24
  • To your 2nd comment: you need TRY/CATCH for *predictable* behaviour which means "error handling like I'd expect". Without T/C you get it is "batch aborting" which means "no more code runs" – gbn May 11 '11 at 19:24
  • @gbn: Thanks! Severity 20 because I want everything to stop. This is a data warehouse transaction import SP and I want to rollback to the previous state of the database if there is an error. I don't want any half-updates. Any suggestions? Should I do something else instead of severity 20? – richard May 11 '11 at 19:26
  • I'd maybe have an outer try/catch to swallow the raiserror in each catch... but hard to say without full details (which is out of scope here anyway) – gbn May 11 '11 at 19:44