CODEWITHSUNDEEP
sql-servert-sqltransactionsrollback
xhr489
xhr489

Reputation: 2319

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?

Upvotes: 7

Views: 1785

Answers (3)

pedram
pedram

Reputation: 1093

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?

Upvotes: 0

Saeideh miri
Saeideh miri

Reputation: 79

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

Upvotes: 0

HABO
HABO

Reputation: 15816

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.

Upvotes: 7

Related Questions