[Release] Queries – Unique / Mob Add/Delete with One Click - TopS4A

[NESTA3!!]

👋 Welcome everyone on epvp!
Today I'm bringing you a powerful SQL query — with one click, you can delete all your unique monsters, and with another click, spawn them again instantly at any position!

Add or delete any unique monster instantly with no hassle.

• ✔ Deletes the unique from: Tab_RefTactics, Tab_RefHive, and Tab_RefNest
• ✔ Clean and correct deletion order
• ✔ Displays full info (Nest ID, Hive ID, Tactics ID)
• ✔ Error message if codename doesn't exist

• ✔ Spawns the unique at any character’s current position
• ✔ Automatically fetches (X, Y, Z) coordinates
• ✔ Generates new IDs for Nest, Hive, and Tactics
• ✔ Full detailed spawn report

---

• ✔ No manual ID handling required
• ✔ Built-in character name validation
• ✔ Perfect for events, global uniques, and scheduled spawns

➕ Add Unique

USE SRO_VT_SHARD
GO

-- Script Author: Ahmed Nesta
-- Purpose: Insert a monster at the character's current location using its codename
-- Features: Dynamic ID generation, character position fetching, and safe multi-table insertion
-- Script Name: Insert_Mob_By_CodeName_With_Char_Position.sql

-- Configuration variables
DECLARE @UNIQUE VARCHAR(129) = 'MOB_JUPITER_YUNO'
DECLARE @CHARNAME VARCHAR(64) = 'nesta'
DECLARE @mindealy INT = 60        -- Minimum delay in seconds before the monster respawns
DECLARE @maxdelay INT = 120       -- Maximum delay in seconds before the monster respawns
DECLARE @INITRADIUS INT = 100     -- Initial spawn radius: how far from the center the monster can spawn
DECLARE @RADIUS INT = 500         -- Movement radius: how far the monster can roam from the spawn point

-- Validate character existence
IF NOT EXISTS (SELECT 1 FROM _Char WHERE CharName16 = @CHARNAME)
BEGIN
    RAISERROR('Character %s does not exist in _Char.', 16, 1, @CHARNAME)
    RETURN
END

-- Generate new unique IDs
DECLARE @MAXNEST INT = (SELECT ISNULL(MAX(dwNestID), 0) + 1 FROM Tab_RefNest)
DECLARE @MAXHIVE INT = (SELECT ISNULL(MAX(dwHiveID), 0) + 1 FROM Tab_RefHive)
DECLARE @MAXTACTICS INT = (SELECT ISNULL(MAX(dwTacticsID), 0) + 1 FROM Tab_RefTactics)

-- Get mob ID by codename
DECLARE @MOBID INT = (SELECT ID FROM _RefObjCommon WHERE CodeName128 = @UNIQUE)

-- Insert into Tab_RefTactics
SET IDENTITY_INSERT Tab_RefTactics ON
INSERT INTO Tab_RefTactics (
    dwTacticsID, dwObjID, btAIQoS, nMaxStamina, btMaxStaminaVariance, nSightRange,
    btAggressType, AggressData, btChangeTarget, btHelpRequestTo, btHelpResponseTo,
    btBattleStyle, BattleStyleData, btDiversionBasis, DiversionBasisData1, DiversionBasisData2,
    DiversionBasisData3, DiversionBasisData4, DiversionBasisData5, DiversionBasisData6,
    DiversionBasisData7, DiversionBasisData8, btDiversionKeepBasis, DiversionKeepBasisData1,
    DiversionKeepBasisData2, DiversionKeepBasisData3, DiversionKeepBasisData4,
    DiversionKeepBasisData5, DiversionKeepBasisData6, DiversionKeepBasisData7,
    DiversionKeepBasisData8, btKeepDistance, KeepDistanceData, btTraceType, btTraceBoundary,
    TraceData, btHomingType, HomingData, btAggressTypeOnHoming, btFleeType,
    dwChampionTacticsID, AdditionOptionFlag, szDescString128
) VALUES (
    @MAXTACTICS, @MOBID, 0, 500, 50, 200, 0, 0, 2, 2, 2, 0, 0, 5,
    0, 0, 0, 0, 0, 30, 0, 0, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0,
    1, 255, 0, 0, 100, 2, 0, 0, 112, 0, @UNIQUE
)
SET IDENTITY_INSERT Tab_RefTactics OFF

-- Insert into Tab_RefHive
SET IDENTITY_INSERT Tab_RefHive ON
INSERT INTO Tab_RefHive (
    dwHiveID, btKeepMonsterCountType, dwOverwriteMaxTotalCount, fMonsterCountPerPC,
    dwSpawnSpeedIncreaseRate, dwMaxIncreaseRate, btFlag, GameWorldID,
    HatchObjType, szDescString128
) VALUES (
    @MAXHIVE, 0, 1, 0, 0, 0, 0, 1, 1, @UNIQUE
)
SET IDENTITY_INSERT Tab_RefHive OFF

-- Get character position
DECLARE @REGION INT = (SELECT LatestRegion FROM _Char WHERE CharName16 = @CHARNAME)
DECLARE @POSX INT = (SELECT POSX FROM _Char WHERE CharName16 = @CHARNAME)
DECLARE @POSY INT = (SELECT POSY FROM _Char WHERE CharName16 = @CHARNAME)
DECLARE @POSZ INT = (SELECT POSZ FROM _Char WHERE CharName16 = @CHARNAME)

-- Insert into Tab_RefNest
SET IDENTITY_INSERT Tab_RefNest ON
INSERT INTO Tab_RefNest (
    dwNestID, dwHiveID, dwTacticsID, nRegionDBID,
    fLocalPosX, fLocalPosY, fLocalPosZ, wInitialDir,
    nRadius, nGenerateRadius, nChampionGenPercentage,
    dwDelayTimeMin, dwDelayTimeMax, dwMaxTotalCount,
    btFlag, btRespawn, btType
) VALUES (
    @MAXNEST, @MAXHIVE, @MAXTACTICS, @REGION,
    @POSX, @POSY, @POSZ, 0,
    @RADIUS, @INITRADIUS, 0,
    @mindealy, @maxdelay, 1,
    0, 1, 0
)
SET IDENTITY_INSERT Tab_RefNest OFF

-- Show all recent rows added to Tab_RefNest
PRINT 'Inserted rows in Tab_RefNest:'
SELECT 
    dwNestID, dwHiveID, dwTacticsID, nRegionDBID, fLocalPosX, fLocalPosY, fLocalPosZ, 
    nRadius, nGenerateRadius, dwDelayTimeMin, dwDelayTimeMax, dwMaxTotalCount, btFlag, btRespawn, btType
FROM Tab_RefNest
WHERE dwNestID >= @MAXNEST

-- Show character info below inserted rows
PRINT 'Character info:'
SELECT 
    CharName16 AS CharacterName,
    LatestRegion AS Region,
    POSX AS PositionX,
    POSY AS PositionY,
    POSZ AS PositionZ
FROM _Char
WHERE CharName16 = @CHARNAME

-- Confirm success
PRINT 'All records inserted and displayed successfully.'

➖ Delete Unique

USE SRO_VT_SHARD
GO

-- ========================================
-- Script by: Ahmed Nesta
-- Features:
-- 1. Deletes all entries for a specific mob from Tab_RefTactics, Tab_RefHive, and Tab_RefNest.
-- 2. Automatically handles deletion in the correct order to maintain referential integrity.
-- 3. Outputs details of each deleted entry for transparency and logging.
-- ========================================

-- Configuration: set your unique mob codename
DECLARE @UniqueCode VARCHAR(129) = 'MOB_JUPITER_YUNO'

-- Loop variables
DECLARE @TacticsID INT, @HiveID INT, @NestID INT

-- Cursor-style loop to delete all matching entries
WHILE EXISTS (
    SELECT 1
    FROM Tab_RefTactics T
    JOIN Tab_RefHive H ON H.szDescString128 = T.szDescString128
    JOIN Tab_RefNest N ON N.dwTacticsID = T.dwTacticsID AND N.dwHiveID = H.dwHiveID
    WHERE T.szDescString128 = @UniqueCode
)
BEGIN
    -- Fetch top record matching the codename
    SELECT TOP 1
        @TacticsID = T.dwTacticsID,
        @HiveID = H.dwHiveID,
        @NestID = N.dwNestID
    FROM Tab_RefTactics T
    JOIN Tab_RefHive H ON H.szDescString128 = T.szDescString128
    JOIN Tab_RefNest N ON N.dwTacticsID = T.dwTacticsID AND N.dwHiveID = H.dwHiveID
    WHERE T.szDescString128 = @UniqueCode

    -- Delete in order: Nest -> Hive -> Tactics
    DELETE FROM Tab_RefNest WHERE dwNestID = @NestID
    DELETE FROM Tab_RefHive WHERE dwHiveID = @HiveID
    DELETE FROM Tab_RefTactics WHERE dwTacticsID = @TacticsID

    -- Output deleted info
    PRINT 'Deleted Record Set:'
    PRINT '- Nest ID: ' + CAST(@NestID AS VARCHAR)
    PRINT '- Hive ID: ' + CAST(@HiveID AS VARCHAR)
    PRINT '- Tactics ID: ' + CAST(@TacticsID AS VARCHAR)
END

PRINT 'All matching mobs with codename "' + @UniqueCode + '" were deleted.'

-- ========================================
-- Script Created by Ahmed Nesta
-- Silkroad SQL Development
-- ========================================

I know what I've shared is simple, but it's a powerful and highly useful tool for both beginners and developers alike.
Wishing you all the best and continued success.

[johnnydung]

thanks

[Nightmare**]

keep

[Shuma20]

Thanks for your efforts