Two formats of SQL Server Transaction Processing

One is to use the try... catch statement, for example:

 --  -This is just an instance.  
  Create   Procedure   [  DBO  ] . [  Mytrytran  ]   --  Create a stored procedure  
--  @ Toid int, -- the account that receives the transfer  
-- @ Fromid int, -- transfer out your account  
--  @ Momeys money -- transfer amount  
  As  
 Begin   Transaction 
 Begin Try
 Select   1  /  0 

Insert IntoT_sex (FID, fsex)Values(3,'Na')
Commit Transaction
EndTry
BeginCatch
Begin
Rollback Transaction
End
EndCatch
 

The second is to capture error errors.

Create procedure [DBO]. [mytran] -- create a stored procedure and define several variables -- @ toid int and -- account for receiving transfers -- @ fromid int, -- transfer out your account -- @ momeys money -- transfer amount as begin transactiondeclare @ errorsum int -- Define the variable, error set @ errorsum = 0 select 1/0 set @ errorsum = @ errorsum + @ error used to accumulate the error insert into t_sex (FID, fsex) during transaction execution) values ('3', 'n') set @ errorsum = @ errorsum + @ error -- whether an error exists if @ errorsum> 0 begin rollback transaction endelse begin commit transaction end
 

There is an incorrect method, please refer:

 

 
Alter procedure [DBO]. [myerrortran] As begin transactionselect 1/0 insert into t_sex (FID, fsex) values ('3', 'n') Commit transactionif @ error> 0 rollback transaction
 

-- In fact, it does not work, because @ error only works for the previous statement, so in this example, although an error occurs, it still does not roll back.