create proc usp_transfer
@from char(4),
@to char(4),
@balance money,--转账金额
@reusltNumber int output--转账是否成功(1-成功,2-失败,3-余额不足)
as
begin
--1.判断金额是否足够转账
declare @money money
select @money=balance from bank where cid=@from
if @money-@balance>=10
begin
--开始转账
begin transaction
declare @sum int=0
--2.账户1扣钱
update bank set balance=balance-@balance where cid=@from
set @sum=@sum+@@error
--3.账户2加钱
update bank set balance=balance+@balance where cid=@to
set @sum=@sum+@@error
--4.判断执行是否成功,进行提交或者回滚
if @sum <>0
begin
set @reusltNumber=2--转账失败
rollback
end
else
begin
set @reusltNumber=1--转账成功
commit
end
end
else
begin
set @reusltNumber=3--余额不足
end
end
declare @r int
exec usp_transfer @from='0001',@to='0002',@balance=500,@reusltNumber=@r output
print @r
--执行
declare @r int
exec usp_transfer @from='0001',@to='0002',@balance=50,@resultnumber=@r output
print @r
--然后查询结果
--select * from bank
数据库表内容
表名:bank
cid char(4)
balance money