Example of learning how to obtain and consume points in a stored procedure

This article describes how to use the stored procedure to obtain and consume points. For more information, see section 1. GM_JF Customer Account integral table 2. GM_JF_DETAIL customer account credit consumption record 3. GM_JF_ACTION_RULES Integral Action rule table 4. GM_JF_GOODS_RULES points commodity rule table-

This article describes how to use the stored procedure to obtain and consume points. For more information, see section 1. GM_JF Customer Account integral table 2. GM_JF_DETAIL customer account credit consumption record 3. GM_JF_ACTION _ RULES Integral Action rule table 4. GM_JF_GOODS _ RULES points commodity rule table-

This article describes how to use the stored procedure to obtain and consume points.

1. GM_JF Customer Account integral table

2. GM_JF_DETAIL credit consumption record of the Customer Account

3. GM_JF_ACTION _ RULES Integral Action rule table

4. GM_JF_GOODS _ RULES points commodity rule table

-- ====================== Test ============================= ======================================

/*

Declare @ StatusCode int = 1;

Exec sp_GM_JF_AddScore 'admin', 'an _ jf_00000000', 0, '', @ StatusCode output

Print @ StatusCode

*/

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

/*

* Determine if points are repeatedly obtained (points cannot be repeatedly obtained when personal data is improved for the first time and passwords are changed for the first time)

* The result is a comparison between the number of data entries in the query cycle of GM_JF_DETAIL (Details table) and the number of recurrence times in the GM_JF_ACTION_RULES (action rule table ).

* If the value is greater than or equal to the number of recurrence cycles, the credit is obtained repeatedly.

* Next

* 1. Data in the Details table is stored into the database.

* 2. If the total points table has no corresponding customer's total points, a new one will be inserted. If yes, the data will be read and updated.

*

*/

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: Failed 1: Successful 2: Repeated retrieval is not allowed

AS

BEGIN

-- Set nocount on added to prevent extra result sets from

-- Interfering with SELECT statements.

Set nocount on;

Declare

@ RepetitionsCycle float = 0, -- cycle (day)

@ RepetitionsCycle_second int = 0, -- cycle (seconds)

@ RepetitionsFrequency int = 0, -- maximum number of times allowed in a cycle

@ RealFrequency int = 0, -- actual cycle

@ USE_DATE datetime = GETDATE ();

-- Whether to repeatedly obtain points

Select top (1) @ repetitionsCycle = RepetitionsCycle, @ repetitionsFrequency = RepetitionsFrequency from GM_JF_ACTION_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 (19 ), 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 (10 ), DATEADD (DAY,-(@ repetitionsCycle-1), @ USE_DATE), 120)

END

If (@ realFrequency >=@ repetitionsFrequency) -- the actual cycle is greater than the number of cycles

Begin

Set @ StatusCode = 2;

Return 2;

End

Declare @ count int = 0; -- number of data entries

Declare @ temp_table 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)

-- Fill in 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;

-- Determine and update the 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 points

@ 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 + @ amount), TTL_JF_AMOUNT = (@ TTL_JF_AMOUNT + @ 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

,