Simple example of a stored procedure with transactions in SQL Server

First look at a concept:

A database transaction is a series of operations performed as a single logical unit of work, either completely or Transaction. Then adding a transaction to the stored procedure ensures that all the SQL code in the transaction is either fully executed or not executed at all.

For a simple stored procedure with a transaction:

Begin
Set NOCOUNT on; --Does not return the number of rows affected
Set Xact_abort on; --Use stored procedure to execute transaction need to turn on Xact_abort parameter (default is OFF)

Delete from table1 where name= "--delete data sql1
BEGIN Tran Tran1--Start a transaction tran1
Delete from table1 where name= "--delete data sql2
Save Tran Tran2--Saves a transaction point tran2
Update table2 set name= ' where id= '--Modify data Sql3

If @ @error <>0--Determine if there is an error in modifying the data (@ @error represents a nonzero error code that returns the last statement (that is, SQL3) with the @ @ERROR, and returns 0 without errors)

Begin
Rollback TRAN Tran2--rollback TRANSACTION to tran2 restore point
Commit Tran Tran1--Commit TRANSACTION Tran1
End
Else-commits the transaction without error Tran1
Commit Tran Tran1--Commit TRANSACTION Tran1
End

If Sql3 execution fails, it is rolled back to the creation of the transaction tran2 (which is equivalent to SQL1 and sql2 execution).