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
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?