Why is part of transaction committed when stored procedure faults?

Question

I have a stored procedure that begins by declaring a few variables then contains begin tran; After this it performs some validations on the supplied arguments (and increments an error count each time a supplied argument fails validation). If there is no error count it then proceeds to carry out 7 inserts. Following this, it has commit tran;

Recently I added an 8th insert to the list. An implicit type conversion meant that some inserted data would be truncated if inserted. This threw an error to the SSMS screen but I found the first 7 inserts had committed while the 8th obviously didn't complete.

I appreciate that I could include a try ... catch block to handle errors but if an explicit begin tran; doesn't make the whole block of work autonomous down to the commit, then what is the point? What have I missed?

I get that I perhaps could have wrapped my procedure call in a transaction at that level - but can someone please explain what is going on and why the begin tran appears disrespected when included within the procedure body? If calling the procedure begins an implicit transaction, then shouldn't a faulting step in the proc roll back all changes being effected by the proc - even without explicitly including begin tran in the proc body?

Answer 1

Transactions don't automatically roll back on error--that's not what they are designed to do. They are designed to give you the ability to rollback. However, you still need to do something to make that happen.

As you mention, you can make that happen through TRY...CATCH, which gives you the most control over if and how you can rollback.

It sounds like you are expecting the behavior of SET XACT_ABORT ON, which you can set in your stored procedure, but is not the default behavior. The description of setting XACT_ABORT on vs off from the docs is:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.

---

When SET XACT_ABORT is ON, calling a faulting procedure returns messages in SSMS like:

Msg 8152, Level 16, State 14, Procedure spProcName, Line 298 [Batch Start Line 5]
String or binary data would be truncated.

Completion time: 2021-04-26T10:51:00.8420902+10:00

The first line is in red in SSMS.

When SET XACT_ABORT is OFF, calling a faulting procedure includes one additional message:

Msg 8152, Level 16, State 14, Procedure spProcName, Line 298 [Batch Start Line 5]
String or binary data would be truncated.
The statement has been terminated.

Completion time: 2021-04-26T10:53:52.5951780+10:00

In particular, when you see the following message, it means statements before the faulting statement within the procedure have been committed:

The statement has been terminated.

Answer 2

If calling the procedure begins an implicit transaction, then shouldn't a faulting step in the proc roll back all changes being effected by the proc

No. Even if you have SET IMPLICIT_TRANSACTIONS ON (and most likely you don't, and shouldn't), executing / calling a stored procedure does not begin a transaction. If I remember correctly, that is how PostgreSQL works (any error inside a function will automatically roll back the entire function), but not SQL Server.

Adding to AMtwo's answer, you could also use TRY...CATCH with a ROLLBACK TRAN; in the CATCH block to control this behavior. This is the approach that I prefer instead of using SET XACT_ABORT ON; (except when doing distributed transactions over linked servers to other SQL Server instances, in which case the SET XACT_ABORT ON; is required). Please see my answer to the following question (also here on DBA.SE) for details:

How to rollback when 3 stored procedures are started from one stored procedure