if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_RegPay_ADD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[UP_RegPay_ADD]

GO

------------------------------------

--用途：增加一条记录

--项目名称：CodematicDemo

--说明：

--时间：2009-10-28 11:03:09

------------------------------------

CREATE PROCEDURE UP_RegPay_ADD

@RID int output,

@R_UserName nvarchar(500),

@R_UserType int,

@R_PayMoney money,

@R_CommissionMoney money,

@R_RegUserIP nvarchar(50),

@R_RegDate datetime,

@R_PayDate datetime,

@R_Remarks nvarchar(4000)

AS

Declare @ErrorMessage varchar(1000),@R_PayBankName nvarchar(250),

@R_PayBankNumer varchar(100),@R_PayBankAddress varchar(1000),

@R_PayBankUserName varchar(150),@R_PayLog nvarchar(4000),

@returnValue int

Set @R_PayLog = @R_UserName+'在'+@R_RegDate+'申请账户金额支付,支付金额:'+@R_PayMoney+',手续费:'+@R_CommissionMoney

Set @R_PayLog = @R_PayLog + ',备注:'+@R_Remarks

--Set @ErrorMessage = '操作失败,SQL错误,错误号:'

Begin Transaction

IF @R_UserType = 1

Begin

Update [WebSiteHost] Set

[Money] = [Money] - @R_PayMoney - @R_CommissionMoney,[W_FreezingMoney] = [W_FreezingMoney] + @R_PayMoney

Where [Money] > @R_PayMoney + @R_CommissionMoney And [Appearance] = 1 And [UserName] = @R_UserName

if @@rowCount < 1 Or @@Error <> 0 GOTO Error

Select @R_PayBankName = [Bank],@R_PayBankNumer = [CardNumber],@R_PayBankAddress = [BankAddress],@R_PayBankUsername=[BankName]

From [WebSiteHost] Where [Appearance] = 1 And [UserName] = @R_UserName

if @@rowCount < 1 Or @@Error <> 0 GOTO Error

INSERT INTO [RegPay](

[R_UserName],[R_UserType],[R_PayMoney],[R_CommissionMoney],[R_RegUserIP],[R_RegDate],

[R_Remarks],[R_PayLog],[R_PayBankName],[R_PayBankNumer],[R_PayBankAddress],[R_PayBankUserName]

)VALUES(

@R_UserName,@R_UserType,@R_PayMoney,@R_CommissionMoney,@R_RegUserIP,@R_RegDate,

@R_Remarks,@R_PayLog,@R_PayBankName,@R_PayBankNumer,@R_PayBankAddress,@R_PayBankUserName

)

if @@rowCount < 1 Or @@Error <> 0 GOTO Error

SET @RID = @@IDENTITY

Set @ErrorMessage = '支付申请成功'

Set @returnValue = 1

GOTO RecordLog

End

Commit Transaction

Error:

RollBack Transaction --回滚操作

Set @ErrorMessage = '支付申请操作失败,SQL错误,错误号:' + @@Error

Set @RID = 0

Set @returnValue=0

GOTO RecordLog

return 0

RecordLog:

Insert into [FinanceLogin](

[Affairs],[AffairsType],[UserName],[DataAndTime],[IP],[Remarks]

)Values(

'1','申请支付',@R_UserName,@R_RegDate,@R_RegUserIP,@ErrorMessage

)--记录操作

return @returnValue

GO

一个关于支付的SQL-Procedure