An example of a cloud-recharging SQL Server stored procedure

A cloud-top-up SQL Server stored procedure example one, top-up related tables: 1, recharge to change Tb_customer form

Leftmoney, current balance, need to add recharge amount
Depositsum, recharge Amount, need to add recharge amount
Consumeno, card operation sequence number, need to add 1

Forceact with Leftmoney to force synchronization Cardleftmoney, is a "card repair behavior"
Uninformedmoney if the value of forceact is singular and does not need to be changed; if 0 or even number, add the recharge amount

2, add a record in the Tb_operlog two, implementation code

Use [Cash]goset ansi_nulls ongoset quoted_identifier ongocreate PROC [dbo]. [Pr_addmoney] @Cash money,--Recharge amount @CardIDH varchar (16),--the card number that needs to recharge @AccExist int OUTPUT,--the user exists or does not exist tag @LogID I NT OUTPUT--tb_operlog record of successful recharge Logidasbegindeclare @ERROR INT--Define Set @ERROR = 0--Transaction rollback judgment Condition: Error count set XACT_ABORT on BEG In TRAN Tran_addmoney--Start transaction IF exists (SELECT accountno from [cash].[ DBO].             [Tb_customer] WHERE [email protected])--Determine if the user is present, and the print card number and physical card number are consistent--where [email protected] and Cardidh=cardid)--Test            The card number is not the same as the physical card number, temporarily commented out the BEGIN SET @AccExist = 1--Existence tag 1--local variable DECLARE @AccountNo int,--User number @CustomerName varchar (32), User name @deptNo varchar (12),--Class number @c    Lassno tinyint,--person category @LeftMoney money,--Original balance @ConsumeNo int,--ordinal @ConsumeNoC int,        --card Operation serial number @CardLeftMoney money,--card balance    @ExChgDate datetime,--Recharge date @TempID int,--Temporary ID value @TempAcc INT--Temporary account value SET @ExChgDa Te=convert (Datetime,convert (char), GetDate (), 120), 20); --Assigning an initial value to a field in a special format--check the name of the top-up person SELECT @AccountNo =accountno, @CustomerName =customerna                    Me, @deptNo =deptno, @ClassNo =classno, @LeftMoney =leftmoney, @ConsumeNo =consumeno, @ConsumeNoC =consumenoc, @CardLeftMoney =CARDLEFTM Oney from [CASH]. [dbo].            [Tb_customer]            WHERE [email protected]; BEGIN SELECT @TempID =max (logid) from [CASH]. [dbo]. [Tb_operlog]; --Query the maximum logid before adding a recharge record--add top-up details in Tb_operlog INSERT into [CASH]. [dbo].                    [Tb_operlog] (EDATE,OPERATORNO,ACCOUNTNO,CUSTOMERNAME,DEPTNO,CLASSNO,TIMES,ACCCOUNT,OLDLEFTMONEY,LEFTMONEY,ACCLEFTMONEY,GLF , Yj,cardfee,cash,itemno,consumeno,consumenoc,cardleftmoney,locaareano,operareano,exchgdate) VALUES (GETDATE (), +, @AccountNo, @ CustomerName, @deptNo, @ClassNo, 1,0, @LeftMoney, @[email protected], @Cash, 0,0,0,0,11, @ConsumeNo +1, @ConsumeNoC                , @CardLeftMoney, @ExChgDate); SELECT @LogID =max (logid) from [CASH]. [dbo]. [Tb_operlog]; --The maximum logid SELECT @TempAcc =accountno from [CASH] after the query adds a recharge record. [dbo].                [Tb_operlog] WHERE [email protected];                    --Query the user account of the maximum logid after adding the top-up record IF ((@LogID > @TempID) and (@[email protected])--Add the record and then perform the recharge operation                        BEGIN PRINT ' Add recharge details in Tb_operlog @logid is ' +cast (@LogID as nvarchar (10)); --tb_customer table Recharge (add top-up record successfully, then perform top-up operation) UPDATE [CASH]. [dbo].                        [Tb_customer] SET [email protected], [email protected], Consumeno=consum ENo+1, [email protected] WHERE [email protected];                        END ELSE BEGIN PRINT ' Add top-up details in Tb_operlog failed, recharge failed, please find out the cause '; SET @[email protected]+1 end End End ELSE B                Egin SET @AccExist = 0--no tag 0 PRINT ' No this person, or account exception, recharge failed '; Set @[email protected]+1 END set @[email protected][email protected] @ERROR--system error and logic error tired                Add IF @ERROR <>0 BEGIN ROLLBACK TRAN; PRINT ' Reload failed @ @ERROR: ' +cast (@ @ERROR as nvarchar) END ELSE BEGIN COMMIT                TRAN; PRINT ' account for ' +cast (@AccountNo as nvarchar (8)) + ' [email protected]+ ' Recharge ' +cast (@Cash as nvarchar (7)) + ' success '-- Print Reload Success Information endend**

An example of a cloud-recharging SQL Server stored procedure