ALTER PROCEDURE BLSP_Native_SaveAbnormalStatus
@PlayerDB_Id BIGINT,
@jsonArrayStr NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
set dateformat ymd
Declare @AbnormalStatus table (
DB_ID BIGINT
, AbnormalStatus_CId INT
, Player_DB_Id BIGINT
, Caster_Player_DB_Id BIGINT
, Level INT
, Stack_Count INT
, State_Date DATETIME
, Remain_Duration_Sec BIGINT
, Total_Duration_Sec BIGINT
, Valid_Data INT
, DB_ID_TYPE VARCHAR(10)
, PRIMARY KEY (DB_ID)
)
-- DB_ID ������ ���� CHID ȹ��
declare @CHID BIGINT
select @CHID = CHID * power(CAST(2 AS BIGINT), CAST(48 AS BIGINT)) FROM DBWorldServerId;
-- �ű� INSERT �� �ʿ��� ������
INSERT INTO @AbnormalStatus
SELECT @CHID + NEXT VALUE FOR DBAbnormalStatus_Seq as DB_ID
, AbnormalStatus_CId , Player_DB_Id , Caster_Player_DB_Id, Level , Stack_Count , State_Date , Remain_Duration_Sec ,Total_Duration_Sec , Valid_Data
, 'NEW' DB_ID_TYPE
FROM OpenJson(@jsonArrayStr)
WITH (
DB_ID BIGINT '$.DB_ID',
AbnormalStatus_CId INT '$.AbnormalStatus_CId',
Player_DB_Id BIGINT '$.Player_DB_Id',
Caster_Player_DB_Id BIGINT '$.Caster_Player_DB_Id',
Level INT '$.Level',
Stack_Count INT '$.Stack_Count',
State_Date DATETIME '$.State_Date',
Remain_Duration_Sec BIGINT '$.Remain_Duration_Sec',
Total_Duration_Sec BIGINT '$.Total_Duration_Sec',
Valid_Data INT '$.Valid_Data'
)
WHERE DB_ID = 0
-- UPDATE �� �ʿ�������
INSERT INTO @AbnormalStatus
SELECT DB_ID , AbnormalStatus_CId , Player_DB_Id , Caster_Player_DB_Id, Level , Stack_Count , State_Date , Remain_Duration_Sec ,Total_Duration_Sec , Valid_Data
, 'OLD' DB_ID_TYPE
FROM OpenJson(@jsonArrayStr)
WITH (
DB_ID BIGINT '$.DB_ID',
AbnormalStatus_CId INT '$.AbnormalStatus_CId',
Player_DB_Id BIGINT '$.Player_DB_Id',
Caster_Player_DB_Id BIGINT '$.Caster_Player_DB_Id',
Level INT '$.Level',
Stack_Count INT '$.Stack_Count',
State_Date DATETIME '$.State_Date',
Remain_Duration_Sec BIGINT '$.Remain_Duration_Sec',
Total_Duration_Sec BIGINT '$.Total_Duration_Sec',
Valid_Data INT '$.Valid_Data'
)
WHERE DB_ID > 0
--select * from @AbnormalStatus
-- DB_ID �� ������ UPDATE / ������ INSERT
MERGE DBAbnormalStatus AS A
USING @AbnormalStatus AS B
ON A.DB_ID = B.DB_ID
WHEN MATCHED THEN
UPDATE
SET A.AbnormalStatus_CId = B.AbnormalStatus_CId
, A.Caster_Player_DB_Id = B.Caster_Player_DB_Id
, A.Level = B.Level
, A.Player_DB_Id = B.Player_DB_Id
, A.Remain_Duration_Sec = B.Remain_Duration_Sec
, A.Stack_Count = B.Stack_Count
, A.State_Date = B.State_Date
, A.Total_Duration_Sec = B.Total_Duration_Sec
, A.updated_at = GETDATE()
, A.Valid_Data = B.Valid_Data
WHEN NOT MATCHED THEN
INSERT ( AbnormalStatus_CId, Caster_Player_DB_Id, DB_ID, Level, Player_DB_Id, Remain_Duration_Sec, Stack_Count, State_Date, Total_Duration_Sec, Valid_Data )
VALUES ( B.AbnormalStatus_CId, B.Caster_Player_DB_Id, B.DB_ID, B.Level, B.Player_DB_Id, B.Remain_Duration_Sec, B.Stack_Count, B.State_Date, B.Total_Duration_Sec, B.Valid_Data );
-- ������ Ȯ��
select DB_ID_TYPE, DB_ID, Valid_Data
FROM @AbnormalStatus
RETURN 0
END
