All in transaction inside stored procedure

Question

I have a pretty big stored procedure with a lot of select/insert/delete statements. I would like all of this to be either commited or rolled back when something wrong happens during execution.

My question: is this "on" by default for a stored procedure, or do I need do add something? I've read somewhere that stored procedure commands which were already executed will not be rollback when any command after would fail. Is it true?

If this is the case putting this inside stored procedure: BEGIN TRAN and at the end COMMIT do the job?

CREATE PROCEDURE [dbo].[SP1]
    @param1 INT
AS
set xact_abort on;
BEGIN TRAN;
    SET NOCOUNT ON;
    DECLARE X INT;  

--stored procedure all business code

COMMIT TRAN;

Yes you need `begin tran;` and `commit tran;` I would also advise `set xact_abort on;` to ensure a transaction is not left hanging — Charlieface, Mar 07 '21 at 12:10
In addition to set xact_abort, depending on what you are doing within this procedure I would suggest breaking it into smaller steps, such as other separate queries, otherwise you may be susceptible to parameter sniffing and recompilations — Stu, Mar 07 '21 at 12:16
@Charlieface where exactly should i put this set xact_abort on;? — Arie, Mar 07 '21 at 14:16
At the top before `begin tran;`, which you should always end with a `;` — Charlieface, Mar 07 '21 at 14:58
@Charlieface so if you look at my main post again this will be valid to make sure my procedure running as trransaction and do not lock? — Arie, Mar 07 '21 at 19:22
@Charlieface sorry, i ment to nmot left transaction hanging... So by means the way i have now code in main post is equivalent tro run all commands inside procedure in transaction so either all executed fine will be commited otherwise rollback. Moreover having set xact_abort on; makes transaction not left hanging just in case. Can you confirm? — Arie, Mar 07 '21 at 23:03
See https://stackoverflow.com/questions/1150032/what-is-the-benefit-of-using-set-xact-abort-on-in-a-stored-procedure basically if you have it set `on` then any error always forces a batch abort, which in turn causes the transaction to rollback. You therefore do not need any `try/catch/rollback` — Charlieface, Mar 08 '21 at 00:01

score 2 · Accepted Answer · answered Mar 07 '21 at 12:14

Normal stored procedures in SQL Server are not atomic, so you need to handle be code with BEGIN TRAN/COMMIT to ensure all-or-nothing behaviour.

It is also good to wrap it with TRY/CATCH block to handle errors.

It is worth to note that natively compiled stored procedures are atomic.

Atomic Blocks in Native Procedures

Atomic blocks are executed (atomically) within the transaction. Either all statements in the block succeed or the entire block will be rolled back to the savepoint that was created at the start of the block. In addition, the session settings are fixed for the atomic block

All in transaction inside stored procedure

1 Answers1