'Are all statements in a stored procedure ONE transaction
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?
Solution 1:[1]
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.
Solution 2:[2]
insert and update have to be commited but delete Statement was rolled back
Solution 3:[3]
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?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | HABO |
| Solution 2 | Saeideh miri |
| Solution 3 | pedram |