An example of a cloud-recharging SQL Server stored procedure

Source: Internet
Author: User
Tags getdate sql server stored procedure example rollback stored procedure example

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.