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