vSRO Çözüldü Any idea on how to fix this?

Using this to add blues to job suits results in an overlap error being thrown by shard/game server
Appreciate the help in advance


Iş kostýumlaryna gök reňk goşmak üçin muny ulanmak şard/oýun serweri tarapyndan gabat gelmek ýalňyşlygynyň döremegine getirýär.

Kömek üçin öňünden minnetdarlyk bildirýäris.

USE [SRO_VT_SHARD];
GO
SET NOCOUNT ON;

BEGIN TRY
----------------------------------------------------------------
-- 0) REQUIRED: Param1..Param12 must be BIGINT (packed values exceed INT)
----------------------------------------------------------------
DECLARE @Cols TABLE (rn INT IDENTITY(1,1), ColName SYSNAME);
INSERT INTO @Cols (ColName)
VALUES ('Param1'),('Param2'),('Param3'),('Param4'),('Param5'),('Param6'),
('Param7'),('Param8'),('Param9'),('Param10'),('Param11'),('Param12');

DECLARE @i INT = 1, @n INT = (SELECT COUNT(*) FROM @Cols);
DECLARE @col SYSNAME, @type SYSNAME, @isNullable BIT, @SQL NVARCHAR(4000);

WHILE @i <= @n
BEGIN
SELECT @col = ColName FROM @Cols WHERE rn = @i;

SELECT
@type = t.name,
@isNullable = c.is_nullable
FROM sys.columns c
JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('dbo._RefObjItem')
AND c.name = @col;

IF @type IS NULL
RAISERROR('dbo._RefObjItem column missing (expected Param1..Param12).', 16, 1);

IF @type <> 'bigint'
BEGIN
SET @SQL =
N'ALTER TABLE dbo._RefObjItem ALTER COLUMN ' + QUOTENAME(@col) +
N' BIGINT ' + CASE WHEN @isNullable = 1 THEN N'NULL' ELSE N'NOT NULL' END + N';';
EXEC sp_executesql @SQL;
END

SET @i = @i + 1;
END

----------------------------------------------------------------
-- 1) Data patch
----------------------------------------------------------------
BEGIN TRAN;

-- Targets
DECLARE @Targets TABLE (CodeName128 VARCHAR(129) PRIMARY KEY);
INSERT INTO @Targets (CodeName128)
VALUES
('ITEM_CH_M_TRADE_HUNTER_03'),
('ITEM_CH_F_TRADE_HUNTER_03'),
('ITEM_CH_M_TRADE_TRADER_03'),
('ITEM_CH_F_TRADE_TRADER_03'),
('ITEM_CH_M_TRADE_THIEF_03'),
('ITEM_CH_F_TRADE_THIEF_03');

-- Job map (confirmed from your dump: Hunter=4, Trader=2, Thief=3)
DECLARE @JobMap TABLE (ID INT PRIMARY KEY, JobType INT NOT NULL);

INSERT INTO @JobMap (ID, JobType)
SELECT
c.ID,
CASE
WHEN c.CodeName128 LIKE '%HUNTER%' THEN 4
WHEN c.CodeName128 LIKE '%TRADER%' THEN 2
WHEN c.CodeName128 LIKE '%THIEF%' THEN 3
ELSE 0
END
FROM dbo._RefObjCommon c
JOIN @Targets t ON t.CodeName128 = c.CodeName128;

IF EXISTS (SELECT 1 FROM @JobMap WHERE JobType = 0)
RAISERROR('JobType mapping failed for one or more targets (unexpected CodeName).', 16, 1);

-- Packed values (600 HP / 600 MP)
DECLARE @HPParamID INT = 148,
@MPParamID INT = 160,
@HP BIGINT = 2576980377748, -- 600*2^32 + 148
@MP BIGINT = 2576980377760, -- 600*2^32 + 160
@MASK BIGINT = 4294967295; -- 0xFFFFFFFF

----------------------------------------------------------------
-- 1A) Apply 600HP/600MP into dbo._RefObjItem.Param1..Param12
-- Empty slots in your DB: -1 or 0
-- ParamID extraction: only for Val > 0; keep as BIGINT to avoid overflow
----------------------------------------------------------------
DECLARE @Pick TABLE (ID INT PRIMARY KEY, HPSlot TINYINT NULL, MPSlot TINYINT NULL);

;WITH Unpvt AS (
SELECT
i.ID,
v.Slot,
CAST(v.Val AS BIGINT) AS ValBI,
CASE WHEN CAST(v.Val AS BIGINT) > 0 THEN (CAST(v.Val AS BIGINT) & @MASK) ELSE NULL END AS ParamID_BI
FROM dbo._RefObjItem i
JOIN @JobMap jm ON jm.ID = i.ID
CROSS APPLY (VALUES
(1, i.Param1), (2, i.Param2), (3, i.Param3), (4, i.Param4),
(5, i.Param5), (6, i.Param6), (7, i.Param7), (8, i.Param8),
(9, i.Param9), (10, i.Param10), (11, i.Param11), (12, i.Param12)
) v(Slot, Val)
),
Slots AS (
SELECT
ID,
MAX(CASE WHEN ParamID_BI = @HPParamID THEN Slot ELSE 0 END) AS ExistingHPSlot,
MAX(CASE WHEN ParamID_BI = @MPParamID THEN Slot ELSE 0 END) AS ExistingMPSlot
FROM Unpvt
GROUP BY ID
),
EmptySlots AS (
SELECT
ID,
Slot,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Slot) AS rn
FROM Unpvt
WHERE ValBI IN (-1, 0)
)
INSERT INTO @Pick (ID, HPSlot, MPSlot)
SELECT
s.ID,
COALESCE(NULLIF(s.ExistingHPSlot, 0), e1.Slot) AS HPSlot,
COALESCE(NULLIF(s.ExistingMPSlot, 0), e2.Slot) AS MPSlot
FROM Slots s
OUTER APPLY (
SELECT TOP (1) es.Slot
FROM EmptySlots es
WHERE s.ExistingHPSlot = 0 AND es.ID = s.ID
ORDER BY es.Slot
) e1
OUTER APPLY (
SELECT TOP (1) es.Slot
FROM EmptySlots es
WHERE s.ExistingMPSlot = 0 AND es.ID = s.ID
AND es.Slot <> COALESCE(NULLIF(s.ExistingHPSlot, 0), e1.Slot)
ORDER BY es.Slot
) e2;

IF EXISTS (SELECT 1 FROM @Pick WHERE HPSlot IS NULL OR MPSlot IS NULL)
RAISERROR('Not enough empty Param slots (-1/0) to apply BOTH HP and MP for at least one target item.', 16, 1);

UPDATE i
SET
Param1 = CASE WHEN p.HPSlot = 1 THEN @HP WHEN p.MPSlot = 1 THEN @MP ELSE i.Param1 END,
Param2 = CASE WHEN p.HPSlot = 2 THEN @HP WHEN p.MPSlot = 2 THEN @MP ELSE i.Param2 END,
Param3 = CASE WHEN p.HPSlot = 3 THEN @HP WHEN p.MPSlot = 3 THEN @MP ELSE i.Param3 END,
Param4 = CASE WHEN p.HPSlot = 4 THEN @HP WHEN p.MPSlot = 4 THEN @MP ELSE i.Param4 END,
Param5 = CASE WHEN p.HPSlot = 5 THEN @HP WHEN p.MPSlot = 5 THEN @MP ELSE i.Param5 END,
Param6 = CASE WHEN p.HPSlot = 6 THEN @HP WHEN p.MPSlot = 6 THEN @MP ELSE i.Param6 END,
Param7 = CASE WHEN p.HPSlot = 7 THEN @HP WHEN p.MPSlot = 7 THEN @MP ELSE i.Param7 END,
Param8 = CASE WHEN p.HPSlot = 8 THEN @HP WHEN p.MPSlot = 8 THEN @MP ELSE i.Param8 END,
Param9 = CASE WHEN p.HPSlot = 9 THEN @HP WHEN p.MPSlot = 9 THEN @MP ELSE i.Param9 END,
Param10 = CASE WHEN p.HPSlot = 10 THEN @HP WHEN p.MPSlot = 10 THEN @MP ELSE i.Param10 END,
Param11 = CASE WHEN p.HPSlot = 11 THEN @HP WHEN p.MPSlot = 11 THEN @MP ELSE i.Param11 END,
Param12 = CASE WHEN p.HPSlot = 12 THEN @HP WHEN p.MPSlot = 12 THEN @MP ELSE i.Param12 END
FROM dbo._RefObjItem i
JOIN @Pick p ON p.ID = i.ID;

----------------------------------------------------------------
-- 1B) Job requirement = JobLv 3 in dbo._RefObjCommon
-- If job type already exists in slot2/3/4 => set its ReqLevel to 3
-- Else insert into first empty slot among #2..#4
----------------------------------------------------------------
-- Update existing job slot value (if already present)
UPDATE c
SET
ReqLevel2 = CASE WHEN c.ReqLevelType2 = jm.JobType THEN 3 ELSE c.ReqLevel2 END,
ReqLevel3 = CASE WHEN c.ReqLevelType3 = jm.JobType THEN 3 ELSE c.ReqLevel3 END,
ReqLevel4 = CASE WHEN c.ReqLevelType4 = jm.JobType THEN 3 ELSE c.ReqLevel4 END
FROM dbo._RefObjCommon c
JOIN @JobMap jm ON jm.ID = c.ID;

-- Insert into first empty slot (2 then 3 then 4) if not present anywhere
UPDATE c
SET ReqLevelType2 = jm.JobType, ReqLevel2 = 3
FROM dbo._RefObjCommon c
JOIN @JobMap jm ON jm.ID = c.ID
WHERE c.ReqLevelType2 = -1 AND c.ReqLevel2 = 0
AND c.ReqLevelType2 <> jm.JobType AND c.ReqLevelType3 <> jm.JobType AND c.ReqLevelType4 <> jm.JobType;

UPDATE c
SET ReqLevelType3 = jm.JobType, ReqLevel3 = 3
FROM dbo._RefObjCommon c
JOIN @JobMap jm ON jm.ID = c.ID
WHERE c.ReqLevelType3 = -1 AND c.ReqLevel3 = 0
AND c.ReqLevelType2 <> jm.JobType AND c.ReqLevelType3 <> jm.JobType AND c.ReqLevelType4 <> jm.JobType;

UPDATE c
SET ReqLevelType4 = jm.JobType, ReqLevel4 = 3
FROM dbo._RefObjCommon c
JOIN @JobMap jm ON jm.ID = c.ID
WHERE c.ReqLevelType4 = -1 AND c.ReqLevel4 = 0
AND c.ReqLevelType2 <> jm.JobType AND c.ReqLevelType3 <> jm.JobType AND c.ReqLevelType4 <> jm.JobType;

-- Final safety check (NO CTE here — prevents your IF syntax error)
IF EXISTS (
SELECT 1
FROM dbo._RefObjCommon c
JOIN @JobMap jm ON jm.ID = c.ID
WHERE c.ReqLevelType2 <> jm.JobType
AND c.ReqLevelType3 <> jm.JobType
AND c.ReqLevelType4 <> jm.JobType
)
RAISERROR('Could not apply JobLv=3 requirement to at least one target (no empty slots 2/3/4 and no existing job slot).', 16, 1);

----------------------------------------------------------------
-- Verification
----------------------------------------------------------------
SELECT
c.CodeName128,
c.ID,
c.ReqLevelType1, c.ReqLevel1,
c.ReqLevelType2, c.ReqLevel2,
c.ReqLevelType3, c.ReqLevel3,
c.ReqLevelType4, c.ReqLevel4
FROM dbo._RefObjCommon c
JOIN @Targets t ON t.CodeName128 = c.CodeName128
ORDER BY c.CodeName128;

SELECT
c.CodeName128,
i.ID,
i.Param1, i.Param2, i.Param3, i.Param4, i.Param5, i.Param6,
i.Param7, i.Param8, i.Param9, i.Param10, i.Param11, i.Param12
FROM dbo._RefObjItem i
JOIN dbo._RefObjCommon c ON c.ID = i.ID
JOIN @Targets t ON t.CodeName128 = c.CodeName128
ORDER BY c.CodeName128;

COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @Line INT = ERROR_LINE();
RAISERROR('Patch failed at line %d: %s', 16, 1, @Line, @Err);
END CATCH;
GO