A sample of stored process learning for integral acquisition and consumption _mssql

1.GM_JF Customer account Integration form

2. Gm_jf_detail customer account points consumption record

3. Gm_jf_action _rules integral Action Rules table

4.gm_jf_goods _rules integral Commodity rules table

Copy Code code as follows:

--=============== Test =======================================================
/*
declare @StatusCode int = 1;
exec sp_gm_jf_addscore ' admin ', ' an_jf_001_001 ', 1,5,0, ', @StatusCode output
Print @StatusCode
*/
-- ===========================================================================
/*
* To determine whether to repeatedly gain points (first perfect personal data, first change password, etc. can not be repeated gain points)
* Judgment is based on the number of data from the query cycle of the Gm_jf_detail (Details table) and Gm_jf_action_rules (Action Rule table) in the cycle of repeated times
* If the number is greater than or equal to the cycle repeat, get the integral
* Next
* 1. Data Warehousing of Details table
* 2. To determine whether the total integration table has a corresponding customer's total score no, insert a new one, read its data, and update
*
*/
ALTER PROCEDURE [dbo]. [Sp_gm_jf_addscore]
@ACCOUNT_ID varchar (30),
@JF_CategoryNumber varchar (15),
@CARD_NUM int,
@HQ_JF_AMOUNT int,
@State varchar (16),
@USE_DESC varchar (400),

@StatusCode int output-Status code: 0: Failure 1: Success 2: Cannot repeat fetch
As
BEGIN
---SET NOCOUNT on added to prevent extra result sets from
--interfering with SELECT statements.
SET NOCOUNT on;

Declare
@repetitionsCycle float=0,--cycle (days)
@repetitionsCycle_second int=0,--Period (sec)
@repetitionsFrequency int=0--the maximum number of times allowed in a cycle
@realFrequency int=0--The actual cycle

@USE_DATE datetime = GETDATE ();
--whether to get points repeatedly
Select Top (1) @repetitionsCycle =repetitionscycle, @repetitionsFrequency =repetitionsfrequency from Gm_jf_action_ The RULES where an_categorynumber= @JF_CategoryNumber;

if (@repetitionsCycle <1)
BEGIN
Set @repetitionsCycle_second = (@repetitionsCycle-1) *24*60*60;
Select @realFrequency =count (1) from Gm_jf_detail where account_id= @ACCOUNT_ID and jf_categorynumber= @JF_ Categorynumber and Use_date <= @USE_DATE and use_date >= CONVERT (varchar), DATEADD (second,-@repetitionsCycle_ Second, @USE_DATE), 120)
End
ELSE
BEGIN
Select @realFrequency =count (1) from Gm_jf_detail where account_id= @ACCOUNT_ID and jf_categorynumber= @JF_ Categorynumber and Use_date <= @USE_DATE and use_date >= CONVERT (varchar), DATEADD (day,-(@repetitionsCycle-1), @USE_DATE), 120)
End

if (@realFrequency >= @repetitionsFrequency)--The actual period is greater than the number of cycles
Begin
Set @StatusCode = 2;
return 2;
End

declare @count int = 0; --Number of data bars
Declare @temp_table table-variables
(
account_id varchar (30),
Jf_amount Decimal (16,2),
Ttl_jf_amount Decimal (16,2),
Last_update_time datetime,
[Version] int
);

BEGIN Tran;
--Insert Details
INSERT INTO Gm_jf_detail
(ACCOUNT_ID,JF_CATEGORYNUMBER,CARD_NUM,HQ_JF_AMOUNT,[STATE],USE_DESC)
Values
(@ACCOUNT_ID, @JF_CategoryNumber, @CARD_NUM, @HQ_JF_AMOUNT, @State, @USE_DESC)

--Populating table variables
Insert INTO @temp_table Select Account_id,jf_amount,ttl_jf_amount,last_update_time,[version] from GM_JF where Account_ Id= @ACCOUNT_ID
Select @count = count (1) from @temp_table;
--Judge and update total points (0: Add others: Modify)
IF (@count =0)
Begin
Insert into GM_JF (Account_id,jf_amount,ttl_jf_amount)
Values
(@ACCOUNT_ID, @HQ_JF_AMOUNT, @HQ_JF_AMOUNT)
End
Else
Begin
declare @JF_AMOUNT int,--Total integral
@TTL_JF_AMOUNT int,--Available points
@Version int; --Version number

Select @JF_AMOUNT =jf_amount, @TTL_JF_AMOUNT =ttl_jf_amount, @Version =[version] from @temp_table where account_id=@ account_id;

Update GM_JF Set jf_amount= (@JF_AMOUNT + @HQ_JF_AMOUNT), ttl_jf_amount= (@TTL_JF_AMOUNT + @HQ_JF_AMOUNT), Last_update_ Time=getdate (), [version]= (@Version + 1) where account_id= @ACCOUNT_ID
End

Commit Tran;
Set @StatusCode = 1;

IF (@ @ERROR <>0)
BEGIN
Set @StatusCode = 0;
ROLLBACK Tran;
End
End