存储过程转账

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <王>
-- Create date: <2017-9-14>
-- Description: <用户借款>
-- =============================================
alter PROCEDURE Pro_jk
-- 定义参数
@jekuanr int,--借款人
@bejkuanr int,--被借款人
@moeny decimal(18,2),--借款金额
@result nvarchar(200) output--结果输出
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
begin tran;--事物开启
begin try
declare @oldmoney decimal ;--被借款人的总金额
declare @oldname nvarchar(200);--被借款人名字
select @oldmoney=Usermoney,@oldname=Username from Userb where Userid=@bejkuanr;--查询被借款人的金额
if(@oldmoney>=@moeny and @moeny>0)
begin
declare @err int=0;--定义错误编号
update Userb set Usermoney=Usermoney-@moeny where Userid=@bejkuanr;--修改被借款人的金额
set @err=@err+@@ERROR;--每次sql执行 获取一次错误编码
update Userb set Usermoney=Usermoney+@moeny where Userid=@jekuanr;--修改借款人的金额
set @err=@err+@@ERROR;--每次sql执行 获取一次错误编码
COMMIT TRAN;
declare @jekuaname nvarchar(200);--借款人名字
select @jekuaname=Username from Userb where Userid=@jekuanr;--查询借款人的名字
insert into Userlogn values
(
@jekuanr,
@bejkuanr,
@jekuaname+'在时间:'+convert(varchar(50),getdate(),121)+'借了'+@oldname+'的'+cast(@moeny as varchar(20))+'元'
);
set @err=@err+@@ERROR;--每次sql执行 获取一次错误编码

IF(@err =0)
BEGIN
SET @result ='借款成功';
COMMIT TRAN; --执行成功 事物提交
END
ELSE
begin
SET @result ='借款失败';
ROLLBACK TRAN; --执行失败 事物回滚
END

end
end try
begin catch
rollback TRAN
end catch
END
GO