Are all statements in a stored procedure ONE transaction

user_10623301 0
Viewed 470

If a stored procedure contains multiple statements e.g. first an insert, then an update and finally a deletes and the stored procedure gets killed in the middle of the delete, does the insert and update also have to be rolled back? Or does it only roll back the delete i.e. the implicit transaction?

rollback transactions tsql sql-server
3 Answers

As explained in Transactions, each statement will be executed as an implicit transaction. If a statement fails then the effect of prior statements will remain committed.

Note that the entire stored procedure may be executed within an explicit transaction created by the calling code.

user_925460

insert and update have to be commited but delete Statement was rolled back

user_145382160

since you have no explicit BEGIN TRANSACTION in the stored procedure, each statement will run on its own with no ability to rollback any changes if there is an error.

However, if before you call the stored procedure you issue a BEGIN TRANSACTION, then all statements are grouped within a transaction and can either be COMMITted or ROLLBACKed following stored procedure execution.

Reference: KM. How does SQL Server treat statements inside stored procedures with respect to transactions?

user_66979460
Related