启用事务完成转账存储过程
use StudentManager
go
if exists(select * from sysobjects where name='usp_TransferAccounts')
drop procedure usp_TransferAccounts
go
create procedure usp_TransferAccounts
@inputAccount int,--转入账户
@outputAccount int,--转出账户
@transferMoney int --交易金额
as
declare @errorSum int
set @errorSum=0
begin transaction
begin
update CardAccount set CurrentMoney=CurrentMoney-@transferMoney
where StudentId=@outputAccount
set @errorSum=@errorSum+@@error
update CardAccount set CurrentMoney=CurrentMoney+@transferMoney
where StudentId=@inputAccount
set @errorSum=@errorSum+@@error
if(@errorSum>0)
rollback transaction
else
commit transaction
end
go
--测试失败的转账
exec usp_TransferAccounts 100002,100001,1000
--查询余额
select Students.StudentId,StudentName,CurrentMoney from Students
inner join CardAccount on Students.StudentId=CardAccount.StudentId
--测试成功的转账
exec usp_TransferAccounts 100002,100001,800
--查询余额
select Students.StudentId,StudentName,CurrentMoney from Students
inner join CardAccount on Students.StudentId=CardAccount.StudentId