SQL事务+存储过程

id 　　name 　　price1 　　 张三 　　 850.002 　　 李四 　　 850.00
--事务declare @error int
set @error=0;
begin tran
update Bank
set price-=100
where
id=1
set @error +=@@ERROR

update Bank
set price+=100
where
id=2
set @error+=@@ERROR

if( @error=0)
begin
print '转账成功'
commit tran
end
else
begin
print '转账失败'
rollback tran
end
go

select * from Bank

 

--存储过程

drop proc usp_changemoney
go

create proc usp_changemoney
--参数列表
@ToId int,
@FromId int,
@money decimal(18,2)

as
--SQL代码
declare @error int
set @error=0
begin tran
update Bank
set price-=@money
where id=@FromId
set @error+=@@error
update Bank
set price+=@money
where id=@ToId
set @error+=@@error
if(@error=0)
begin
--转义
print '成功转账'+cast(@money as nvarchar)+'元'
commit tran
end
else
begin
print '转账失败'
rollback tran
end
go

--调用存储过程
exec 存储过程名称 fromId toId 转帐金额
exec usp_changemoney 2,1,150

 

 

--存储过程-(带输出值)

drop proc usp_changemoney
go

create proc usp_changemoney
--参数列表
@ToId int,
@FromId int,
@money decimal(18,2),
@ToName nvarchar(50) output,
@UnitPrice decimal(18,2)output

as
--SQL代码
declare @error int
set @error=0
begin tran
update Bank
set price-=@money
where id=@FromId
set @error+=@@error
update Bank
set price+=@money
where id=@ToId
set @error+=@@error

select @ToName= name from Bank
where id=@ToId

if(@error=0)
begin
-- 转义cast
print '成功转账'+cast(@money as nvarchar)+'元'
commit tran
select @UnitPrice= price from Bank
where id=@FromId
end
else
begin
print '转账失败'
rollback tran
end
go

--调用存储过程
declare @ToName nvarchar(50) ,@UnitPrice decimal(18,2)
exec usp_changemoney 2,1,150,@ToName output,@UnitPrice output
print '被转账人:'+@ToName
print '余额'+cast(@UnitPrice as nvarchar(50))

select * from Bank