Hello,
I am trying to edit a procedure to add new points to existing points without resetting it.
I have 2 tables. First table is saving the points dynamically. Then the points in first table are saving to second table. But I want the points to get increased in the second tables.
Example: first table has 5 points. Second tables showing 5 points. (fine)
Now first table is truncated, and then it has 1 point. I want the second table to show 6 points.
In my case it will reset the 5 points and show 1 point which I don't want like that.
I am TSQL beginner and I couldn't find a solution. Hope you guys help me.
USE [RANK_User]
GO
/****** Object: StoredProcedure [dbo].[_BARank] Script Date: 4/13/2020 3:43:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**
version : 1
author : xxx
created date : 2020-13-04
description : battle arena rank manager
**/
ALTER PROCEDURE [dbo].[_BARank]
AS
DECLARE @Counter INT = 1
DECLARE @RankTable TABLE (ID INT IDENTITY(1,1), CharName16 VARCHAR(64), Points INT)
INSERT @RankTable
SELECT Data2, COUNT()
FROM RANK_User.dbo._BridgeCommands
WHERE Executor = '_IncreaseKillCountBA' and Data3 = 'BattleArena'
GROUP BY Data2
ORDER BY COUNT() DESC
WHILE @Counter <= (SELECT COUNT(*) FROM @RankTable)
BEGIN
DECLARE @CharName16 VARCHAR(64),
@Points INT,
@CharID INT,
@Guild VARCHAR(64)
/**_# INITIALIZE */
SET @CharName16 = NULL
SET @CharID = NULL
SET @Guild = NULL
SELECT @CharName16 = CharName16,
@Points = Points
FROM @RankTable
WHERE ID = @Counter
SELECT @CharID = CharID
FROM KSY_VT_SHARD.dbo._Char
WHERE CharName16 = @CharName16
SELECT @Guild = G.Name
FROM KSY_VT_SHARD.dbo._GuildMember GM
JOIN KSY_VT_SHARD.dbo._Guild G
ON GM.GuildID = G.ID
WHERE GM.CharName = @CharName16
IF @Guild IS NULL
BEGIN
SET @Guild = ''
END
UPDATE RANK_User.dbo._DynamicRank_Custom1
SET CharName16 = @CharName16, --POSIBLE CHAR RENAME
GuildName = @Guild, --POSIBLE GUILD LEAVE
Point = @Points,
LastUpdated = GETDATE()
WHERE CharID = @CharID
IF @@ROWCOUNT = 0
BEGIN
INSERT RANK_User.dbo._DynamicRank_Custom1 (CharID, CharName16, GuildName, Point, LastUpdated)
VALUES (@CharID, @CharName16, @Guild, @Points, GETDATE())
END
/**_# FOR LOOP COUNTER*/
SET @Counter += 1
END
