Is there a scenario whereby you would create a stored procedure with the option
Set Xact_abort ON
And validly need to create transactions with commits inside that stored procedure?
I have looked around online and have not had any clear answer as most refer to xact_abort and try catch blocks.
My feeling is that Xact_abort overrides the need for begin trans with a commit, but would like to be sure.
For a picture of my example see below
create procedure MyProc (@Var int)
set xact_abort on
Begin transaction trans1
Commit transaction Trans1
Rollback transaction Trans1
Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]