-- ================================================

-- 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