Let’s start with a concept
Database transaction refers to a series of operations executed as a single logical unit of work, either executed completely or not. Then, adding a transaction to a stored procedure can ensure that all SQL codes in the transaction are either executed completely or not executed at all.
For example, a simple stored procedure with transactions:
Begin Set NOCOUNT ON; -- Does not return the number of affected rows Set XACT_ ABORT ON; -- Xact needs to be turned on to execute transactions using stored procedures_ 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 -- save a transaction point tran2 Update table2 set name = '"where id =' -- modify data SQL3 If @ @ error < > 0 -- judge whether there is an error in modifying the data (@ error means to return the non-zero error code of the statement closest to @ @ error (i.e. SQL3). If there is no error, return 0) begin Rollback tran tran2 -- rollback the transaction to the restore point of tran2 Commit tran tran1 -- commit tran1 end Else -- commit transaction tran1 if there is no error Commit tran tran1 -- commit tran1 End
If the execution of SQL3 fails, it will be rolled back to the creation of tran2 (equivalent to no execution except the execution of sql1 and SQL2).