Stored Procedures and transactions

Use the Book go--Delete table if object_id (' Tb_money ') is not nulldrop table tb_moneygo--CREATE table created tables Tb_money (ID INT IDENTITY)  PRIMARY KEY, Name VARCHAR (() not NULL, Mymoney DECIMAL NOT NULL)--inserts data insert into Dbo.tb_money (Name, Mymoney) VALUES (' Liu Bei ', 100) INSERT into Dbo.tb_money (Name, Mymoney) VALUES (' Guan Yu ', 20)--Start the transaction begin TRAN Tran_moneydeclare @tran_error INT; SET @tran_error = 0;declare @FromPeple varchar (;D eclare @ToPeple varchar;D eclare @HowMoney DECIMAL; SET @FromPeple = ' Guan Yu '; SET @ToPeple = ' Liu Bei '; SET @HowMoney = 30;       Begin Tryif (((SELECT Mymoney from Dbo.tb_money WHERE Name = @FromPeple)-@HowMoney) < 0) begin PRINT ' Insufficient balance ';  SET @tran_error = @tran_error + 1;  Endelse BEGIN UPDATE dbo.tb_money SET Mymoney = MyMoney-30 WHERE Name = @FromPeple;   SET @tran_error = @tran_error + @ @ERROR;  UPDATE Dbo.tb_money SET Mymoney = Mymoney + WHERE Name = @ToPeple;   SET @tran_error = @tran_error + @ @ERROR; EndEnd trybegin CATCH PRINT ' exception, error number: ' + conveRT (Varchar,error_number ()) + ' error message: ' + error_message () SET @tran_error = @tran_error + 1END catchif (@tran_error > 0) be  GIN ROLLBACK tran;--PRINT ' transfer failed, cancel transaction ' Endelsebegin COMMIT TRAN; PRINT ' transfer successful ' end--transfer stored procedure if object_id (' Pro_tranmoney ') is not nulldrop PROCEDURE Pro_tranmoney GO CREATE PROCEDURE Pro_tran Money@frompeple varchar (a), @ToPeple varchar, @HowMoney transactions in decimalasbegin--stored procedures--the start of a transaction begin TRAN Tran_ Moneydeclare @tran_error INT; SET @tran_error = 0;       Begin Tryif (((SELECT Mymoney from Dbo.tb_money WHERE Name = @FromPeple)-@HowMoney) < 0) begin PRINT ' Insufficient balance ';  SET @tran_error = @tran_error + 1;  Endelse BEGIN UPDATE dbo.tb_money SET Mymoney = MyMoney-30 WHERE Name = @FromPeple;   SET @tran_error = @tran_error + @ @ERROR;  UPDATE Dbo.tb_money SET Mymoney = Mymoney + WHERE Name = @ToPeple;   SET @tran_error = @tran_error + @ @ERROR; EndEnd trybegin CATCH PRINT ' exception, error number: ' + CONVERT (Varchar,error_number ()) + ' error message: ' + error_message () SET @tran_error = @tran_error + 1END catchif (@tran_error > 0) BEGIN ROLLBACK tran;--PRINT ' transfer failed, cancel transaction ' Endelsebegin COMMIT Tran; PRINT ' transfer succeeded ' endend--test transfer stored procedure EXEC dbo. Pro_tranmoney @FromPeple = ' Liu Bei ', @ToPeple = ' Guan Yu ', @HowMoney = 30

　　

Stored Procedures and transactions