[Stored Procedure] Add custom gear to GM character

[Witchy Moo]

Hi, I'd like to share something, hope could be useful a little bit

This is to add custom equipment to your GM, it will auto detect character existence, GM status, race and gender.

I use v188, didn't test it in v193 (please read the SP notes)

--- Updates made, if you've applied this SP before, just ALTER the whole thing

New Stored Procedure:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_ADD_GM_GEAR]    Script Date: 11/02/2013 23:44:41 ******/
/*
 * Purpose: Add custom equipment to GM character
 * Auto check: Will auto detect character existence, GM status, race and gender.
 * How to use:
 * -
 * USE [SRO_VT_SHARD]
 * EXEC [_ADD_GM_GEAR] '<character_name>', '<gear_type>', <equipment_degree>, <plus>
 * -
 * Parameters:
 *   <character_name>: duh?
 *   <gear_type>: HEAVY, LIGHT, or CLOTHES
 *   <equipment_degree>: duh?
 *   <plus>: duh?
 *
 * Example: EXEC [_ADD_GM_GEAR] 'Mulan', 'CLOTHES', 11, 12
 * Will give: 'Mulan' (11 Degree, +12 100% FB) Weapon/Shield, Garment, Accessories, 1B Gold, 10M SP, and 109 Inventory slots
 *
 * Notes:
 * - Feel free to change anything ;)
 * - GM levels determined by "sec_primary" group in TB_User table in [SRO_VT_ACCOUNT] database.
 *   I use default GM levels of 1-6 and 10. (GM level check section)
 * - I put SOSUN item suffix as default. (@itemSuffix settings, change it if you want)
 *
 * Important stuff:
 * - Dependecies SP (make sure they are exist): _FN_ADD_INITIAL_EQUIP, _ADD_ITEM_EXTERN, _IsExistingCharName, _STRG_DEL_ITEM_NoTX
 * - This will put new equipment to equipment slots, even if they're empty.
 * - The character MUST wear a weapon (no need others, just weapon) (to trigger detection)
 * - Shield will automatically added if the character is using 1H weapon. (No shield for 2H)
 *
 * Updates:
 * - Remove weapon type execution parameter
 * - Added detection feature for weapon and shield
 * - Added spam messages to SQL output window ;)
 *
 * -- Witchy (Mulan)
 *
 */
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

-- Check if SP exist, if not, auto create the SP
IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = '_ADD_GM_GEAR')
    EXEC('CREATE PROCEDURE [SRO_VT_SHARD].[dbo].[_ADD_GM_GEAR] AS BEGIN SET NOCOUNT ON; END')
GO

ALTER PROCEDURE [dbo].[_ADD_GM_GEAR]
    @CharName AS VARCHAR(64),
    @EQClass AS VARCHAR(16),
    @EQDegree AS VARCHAR(2),
    @EQPlus AS tinyINT
AS
    /* Update 2013/11/05 */
    -- Item set prefix
    -- CHANGE THIS! -- CHANGE THIS! -- CHANGE THIS! (if needed)
    DECLARE @itemSuffix VARCHAR(16) = 'C_RARE'

    SET NOCOUNT ON

    -- Check if character exist
    DECLARE    @isCharExist tinyINT

    PRINT 'Validating Character...'
    EXEC @isCharExist = [_IsExistingCharName] @CharNameToCheck = @CharName
    IF (@isCharExist = 0)
    BEGIN
        RAISERROR('Character: %s does not exist. Sequence canceled!', 1, 16, @CharName)
        RETURN -1
    END

    -- Check character and account related information (In relation with SRO_VT_ACCOUNT)
    DECLARE @CharID INT
    DECLARE @RefCharID INT
    DECLARE @UserJID INT
    DECLARE @GMLevel INT
    DECLARE @AccountName VARCHAR(32)

    -- Check if character assigned to Account ID
    PRINT 'Validating Account ID...'
    SELECT @CharID = CharID, @RefCharID = RefObjID FROM [_Char] WHERE CharName16 = @CharName
    SELECT @UserJID = UserJID FROM [_User] WHERE CharID = @CharID
    IF (@UserJID IS NULL OR @UserJID = 0)
    BEGIN
        RAISERROR('Account ID does not exist, or character: %s is not assigned to any user accounts. Sequence canceled!', 1, 16, @CharName)
        RETURN -2
    END

    -- Check GM levels (CHANGE THIS if needed)
    PRINT 'Validating GM Level...'
    SELECT @GMLevel = sec_primary, @AccountName = StrUserID FROM [SRO_VT_ACCOUNT].[dbo].[TB_User] WHERE JID = @UserJID
    IF (@GMLevel IS NULL OR (@GMLevel > 6 AND @GMLevel < 10))
    BEGIN
        RAISERROR('Account ID associated with this char: %s, does not have GM prvileges. Sequence canceled!', 1, 16, @AccountName)
        RETURN -3
    END

    -- Check character gender and race
    DECLARE @CharGender VARCHAR(1)
    DECLARE @CharRace VARCHAR(2)

    PRINT 'Validating race and gender...'
    IF (@RefCharID <= 14887 AND @RefCharID >= 14875) -- European Male
    BEGIN
        SET @CharRace = 'EU'
        SET @CharGender = 'M'
    END
    IF (@RefCharID <= 14900 AND @RefCharID >= 14888) -- European Female
    BEGIN
        SET @CharRace = 'EU'
        SET @CharGender = 'W'
    END
    IF (@RefCharID <= 1919 AND @RefCharID >= 1907) -- Chinesse Male
    BEGIN
        SET @CharRace = 'CH'
        SET @CharGender = 'M'
    END
    IF (@RefCharID <= 1932 AND @RefCharID >= 1920) -- Chinesse Female
    BEGIN
        SET @CharRace = 'CH'
        SET @CharGender = 'W'
    END
    PRINT 'Race code is: ' + @CharRace + ', gender code is: ' + @CharGender + '.'

    -- Setting up equipment
    -- Gears
    DECLARE @CodeNameHelm        VARCHAR(256)
    DECLARE @CodeNameMail        VARCHAR(256)
    DECLARE @CodeNameShoulder    VARCHAR(256)
    DECLARE @CodeNameGauntlet    VARCHAR(256)
    DECLARE @CodeNamePants        VARCHAR(256)
    DECLARE @CodeNameBoots        VARCHAR(256)
    DECLARE @RefHelmID            INT
    DECLARE @RefMailID            INT
    DECLARE @RefShoulderID        INT
    DECLARE @RefGauntletID        INT
    DECLARE @RefPantsID            INT
    DECLARE @RefBootsID            INT
    DECLARE @RefHelmLinkID        INT
    DECLARE @RefMailLinkID        INT
    DECLARE @RefShoulderLinkID    INT
    DECLARE @RefGauntletLinkID    INT
    DECLARE @RefPantsLinkID        INT
    DECLARE @RefBootsLinkID        INT

    -- Accessories
    DECLARE @CodeNameEarring    VARCHAR(256)
    DECLARE @CodeNameNecklace    VARCHAR(256)
    DECLARE @CodeNameRing        VARCHAR(256)
    DECLARE @RefEarringID        INT
    DECLARE @RefNecklaceID        INT
    DECLARE @RefRingID            INT
    DECLARE @RefEarringLinkID    INT
    DECLARE @RefNecklaceLinkID    INT
    DECLARE @RefRingLinkID        INT

    -- Weapon and Shield
    DECLARE @CodeNameWeapon        VARCHAR(256)
    DECLARE @CodeNameShield        VARCHAR(256)
    DECLARE @RefWeaponID        INT
    DECLARE @RefShieldID        INT
    DECLARE @RefWeaponLinkID    INT
    DECLARE @RefShieldLinkID    INT

    /* Update 2013/11/05 */
    -- Check for current equipped weapon/shield
    PRINT 'Validating equipped weapon...'
    DECLARE @OldWeaponID INT = (SELECT ItemID FROM [_Inventory] WHERE CharID=(SELECT CharID FROM [_Char] WHERE CharID = @CharID) AND Slot ='6')
    IF (@OldWeaponID IS NULL OR @OldWeaponID = 0)
    BEGIN
        RAISERROR('Character: %s is not wearing any weapon. Sequence canceled!', 10, 1, @CharName)
        RETURN -4
    END
    DECLARE @RefOldWeaponID INT = (SELECT RefItemID FROM [_Items] WHERE ID64 = @OldWeaponID)
    DECLARE @WPClass VARCHAR(16)
    SELECT @WPClass = 
        CASE
            WHEN TypeID4 = 2 THEN 'SWORD'
            WHEN TypeID4 = 3 THEN 'BLADE'
            WHEN TypeID4 = 4 THEN 'SPEAR'
            WHEN TypeID4 = 5 THEN 'TBLADE'
            WHEN TypeID4 = 6 THEN 'BOW'
            WHEN TypeID4 = 7 THEN 'SWORD'
            WHEN TypeID4 = 8 THEN 'TSWORD'
            WHEN TypeID4 = 9 THEN 'AXE'
            WHEN TypeID4 = 10 THEN 'DARKSTAFF'
            WHEN TypeID4 = 11 THEN 'TSTAFF'
            WHEN TypeID4 = 12 THEN 'CROSSBOW'
            WHEN TypeID4 = 13 THEN 'DAGGER'
            WHEN TypeID4 = 14 THEN 'HARP'
            WHEN TypeID4 = 15 THEN 'STAFF'
        END
    FROM [_RefObjCommon] WHERE ID = @RefOldWeaponID ORDER BY TypeID4 ASC

    /* Update 2013/11/05 */
    -- Set no shield by default, if the weapon used is 1H, then add shield automatically.
    PRINT 'Checking if character is using shield...'
    DECLARE @useShield TINYINT = 0
    IF (@WPClass = 'SWORD' OR @WPClass = 'BLADE' OR @WPClass = 'STAFF')
    BEGIN
        PRINT '- 1H weapon is used, new shield will be added.'
        SET @useShield = 1
    END    

    /* Update 2013/11/05 */
    -- Item codes (trailing codes are from itemSuffix settings above)
    PRINT 'Setting up item codenames...'
    -- Gears
    SET @CodeNameHelm        = 'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_HA_' + @itemSuffix
    SET @CodeNameMail        = 'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_BA_' + @itemSuffix
    SET @CodeNameShoulder    = 'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_SA_' + @itemSuffix
    SET @CodeNameGauntlet    = 'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_AA_' + @itemSuffix
    SET @CodeNamePants        = 'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_LA_' + @itemSuffix
    SET @CodeNameBoots        = 'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_FA_' + @itemSuffix

    -- Accessories
    SET @CodeNameEarring    = 'ITEM_' + @CharRace + '_EARRING_' + @EQDegree + '_' + @itemSuffix
    SET @CodeNameNecklace    = 'ITEM_' + @CharRace + '_NECKLACE_' + @EQDegree + '_' + @itemSuffix
    SET @CodeNameRing        = 'ITEM_' + @CharRace + '_RING_' + @EQDegree + '_' + @itemSuffix

    -- Weapon and Shield
    SET @CodeNameWeapon        = 'ITEM_' + @CharRace + '_' + @WPClass + '_' + @EQDegree + '_' + @itemSuffix
    SET @CodeNameShield        = 'ITEM_' + @CharRace + '_SHIELD_' + @EQDegree + '_' + @itemSuffix

    -- Get Reference ID and Link ID
    PRINT 'Getting item reference ID and link...'
    -- Gears
    SELECT @RefHelmID = ID, @RefHelmLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameHelm
    SELECT @RefMailID = ID, @RefMailLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameMail
    SELECT @RefShoulderID = ID, @RefShoulderLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameShoulder
    SELECT @RefGauntletID = ID, @RefGauntletLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameGauntlet
    SELECT @RefPantsID = ID, @RefPantsLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNamePants
    SELECT @RefBootsID = ID, @RefBootsLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameBoots

    -- Accessories
    SELECT @RefEarringID = ID, @RefEarringLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameEarring
    SELECT @RefNecklaceID = ID, @RefNecklaceLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameNecklace
    SELECT @RefRingID = ID, @RefRingLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameRing

    -- Weapon and Shield
    SELECT @RefWeaponID = ID, @RefWeaponLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameWeapon
    SELECT @RefShieldID = ID, @RefShieldLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameShield

    IF (
        @RefHelmID IS NULL OR @RefHelmID = 0 OR 
        @RefMailID IS NULL OR @RefMailID = 0 OR 
        @RefShoulderID IS NULL OR @RefShoulderID = 0 OR 
        @RefGauntletID IS NULL OR @RefGauntletID = 0 OR 
        @RefPantsID IS NULL OR @RefPantsID = 0 OR 
        @RefBootsID IS NULL OR @RefBootsID = 0 OR 
        @RefEarringID IS NULL OR @RefEarringID = 0 OR 
        @RefNecklaceID IS NULL OR @RefNecklaceID = 0 OR 
        @RefRingID IS NULL OR @RefRingID = 0 OR
        @RefWeaponID IS NULL OR @RefWeaponID = 0 OR
        @RefShieldID IS NULL OR @RefShieldID = 0
    )
    BEGIN
        RAISERROR('Invalid item reference ID, check item codename (degree parameter or item suffix setting). Sequence canceled!', 1, 16)
        RETURN -5
    END
    IF (
        @RefHelmLinkID IS NULL OR @RefHelmLinkID = 0 OR 
        @RefMailLinkID IS NULL OR @RefMailLinkID = 0 OR 
        @RefShoulderLinkID IS NULL OR @RefShoulderLinkID = 0 OR 
        @RefGauntletLinkID IS NULL OR @RefGauntletLinkID = 0 OR 
        @RefPantsLinkID IS NULL OR @RefPantsLinkID = 0 OR 
        @RefBootsLinkID IS NULL OR @RefBootsLinkID = 0 OR 
        @RefEarringLinkID IS NULL OR @RefEarringLinkID = 0 OR 
        @RefNecklaceLinkID IS NULL OR @RefNecklaceLinkID = 0 OR 
        @RefRingLinkID IS NULL OR @RefRingLinkID = 0 OR 
        @RefWeaponLinkID IS NULL OR @RefWeaponLinkID = 0 OR 
        @RefShieldLinkID IS NULL OR @RefShieldLinkID = 0
    )
    BEGIN
        RAISERROR('Invalid link reference ID, check item codename (degree parameter or item suffix setting). Sequence canceled!', 1, 16)
        RETURN -6
    END

    -- Get durability for 'Data'
    PRINT 'Getting items durability information...'
    DECLARE @DuraHelm INT = (SELECT Dur_L from [_RefObjItem] WHERE ID = @RefHelmLinkID)
    DECLARE @DuraMail INT = (SELECT Dur_L from [_RefObjItem] WHERE ID = @RefMailLinkID)
    DECLARE @DuraShoulder INT = (SELECT Dur_L from [_RefObjItem] WHERE ID = @RefShoulderLinkID)
    DECLARE @DuraGauntlet INT = (SELECT Dur_L from [_RefObjItem] WHERE ID = @RefGauntletLinkID)
    DECLARE @DuraPants INT = (SELECT Dur_L from [_RefObjItem] WHERE ID = @RefPantsLinkID)
    DECLARE @DuraBoots INT = (SELECT Dur_L from [_RefObjItem] WHERE ID = @RefBootsLinkID)
    DECLARE @DuraWeapon INT = (SELECT Dur_L from [_RefObjItem] WHERE ID = @RefWeaponLinkID)
    DECLARE @DuraShield INT = (SELECT Dur_L from [_RefObjItem] WHERE ID = @RefShieldLinkID)

    /* Update 2013/11/05 */
    -- Clear inventory (Just equipped items) ;)
    PRINT 'Removing equipped items...'
    DECLARE @TSlots INT
    DECLARE @CharSlot INT
    SET @CharSlot = 0
    SELECT @TSlots = COUNT(Slot) from _Inventory WHERE CharID = @CharID
    WHILE @CharSlot <= @TSlots
    BEGIN
        -- IF (@CharSlot < 6 OR (@CharSlot > 7 AND @CharSlot < 13))
        -- BEGIN
        IF (@CharSlot < 13)
        BEGIN
            EXEC [_STRG_DEL_ITEM_NoTX] 1, @CharID, @CharSlot
        END
        SET @CharSlot = @CharSlot + 1
    END

    -- Sequence begin
    BEGIN TRANSACTION
        PRINT 'Adding new items...'

        -- Add other stuff (make sure they\'re exist in database and Media)
        EXEC [_ADD_ITEM_EXTERN] @CharName, 'ITEM_MALL_REVERSE_RETURN_SCROLL', 10, 0
        EXEC [_ADD_ITEM_EXTERN] @CharName, 'ITEM_MALL_RETURN_SCROLL_HIGH_SPEED', 10, 0
        EXEC [_ADD_ITEM_EXTERN] @CharName, 'ITEM_MALL_CHAR_SKIN_CHANGE_SCROLL', 1, 0
        PRINT '- Cool stuff added to inventory.'

        -- Add new equipment to equipment slots
        DECLARE @HelmItemID64 BIGINT
        DECLARE @MailItemID64 BIGINT
        DECLARE @ShoulderItemID64 BIGINT
        DECLARE @GauntletItemID64 BIGINT
        DECLARE @PantsItemID64 BIGINT
        DECLARE @BootsItemID64 BIGINT

        DECLARE @EarringItemID64 BIGINT
        DECLARE @NecklaceItemID64 BIGINT
        DECLARE @RingLItemID64 BIGINT
        DECLARE @RingRItemID64 BIGINT

        DECLARE @WeaponItemID64 BIGINT
        DECLARE @ShieldItemID64 BIGINT

        SET @HelmItemID64 = 0
        SET @MailItemID64 = 0
        SET @ShoulderItemID64 = 0
        SET @GauntletItemID64 = 0
        SET @PantsItemID64 = 0
        SET @BootsItemID64 = 0

        SET @EarringItemID64 = 0
        SET @NecklaceItemID64 = 0
        SET @RingLItemID64 = 0
        SET @RingRItemID64 = 0

        SET @WeaponItemID64 = 0
        SET @ShieldItemID64 = 0

        -- Helm
        EXEC @HelmItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 0, @RefHelmID, @DuraHelm
        IF (@HelmItemID64 <= 0)
        BEGIN
            PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
            ROLLBACK TRANSACTION
            RETURN -7
        END
        PRINT '- Helm equipped.'

        -- Chest
        EXEC @MailItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 1, @RefMailID, @DuraMail
        IF (@MailItemID64 <= 0)
        BEGIN
            PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
            ROLLBACK TRANSACTION
            RETURN -8
        END
        PRINT '- Chest equipped.'

        -- Shoulder
        EXEC @ShoulderItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 2, @RefShoulderID, @DuraShoulder
        IF (@ShoulderItemID64 <= 0)
        BEGIN
            PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
            ROLLBACK TRANSACTION
            RETURN -9
        END
        PRINT '- Shoulder equipped.'

        -- Gauntlet
        EXEC @GauntletItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 3, @RefGauntletID, @DuraGauntlet
        IF (@GauntletItemID64 <= 0)
        BEGIN
            PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
            ROLLBACK TRANSACTION
            RETURN -10
        END
        PRINT '- Gauntlet equipped.'

        -- Pants
        EXEC @PantsItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 4, @RefPantsID, @DuraPants
        IF (@PantsItemID64 <= 0)
        BEGIN
            PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
            ROLLBACK TRANSACTION
            RETURN -11
        END
        PRINT '- Pants equipped.'

        -- Boots
        EXEC @BootsItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 5, @RefBootsID, @DuraBoots
        IF (@BootsItemID64 <= 0)
        BEGIN
            PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
            ROLLBACK TRANSACTION
            RETURN -12
        END
        PRINT '- Boots equipped.'

        -- Earring
        EXEC @EarringItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 9, @RefEarringID, 0
        IF (@EarringItemID64 <= 0)
        BEGIN
            PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
            ROLLBACK TRANSACTION
            RETURN -13
        END
        PRINT '- Earring equipped.'

        -- Necklace
        EXEC @NecklaceItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 10, @RefNecklaceID, 0
        IF (@NecklaceItemID64 <= 0)
        BEGIN
            PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
            ROLLBACK TRANSACTION
            RETURN -14
        END
        PRINT '- Necklace equipped.'

        -- Left Ring
        EXEC @RingLItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 11, @RefRingID, 0
        IF (@RingLItemID64 <= 0)
        BEGIN
            PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
            ROLLBACK TRANSACTION
            RETURN -15
        END
        PRINT '- Left Ring equipped.'

        -- Right Ring
        EXEC @RingRItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 12, @RefRingID, 0
        IF (@RingRItemID64 <= 0)
        BEGIN
            PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
            ROLLBACK TRANSACTION
            RETURN -16
        END
        PRINT '- Right Ring equipped.'

        -- Weapon
        EXEC @WeaponItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 6, @RefWeaponID, @DuraWeapon
        IF (@WeaponItemID64 <= 0)
        BEGIN
            PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
            ROLLBACK TRANSACTION
            RETURN -17
        END
        PRINT '- Weapon equipped.'

        -- Shield
        IF (@useShield = 1)
        BEGIN
            EXEC @ShieldItemID64 = [_FN_ADD_INITIAL_EQUIP] @CharID, 7, @RefShieldID, @DuraShield
            IF (@ShieldItemID64 <= 0)
            BEGIN
                PRINT 'Problem when executing [_FN_ADD_INITIAL_EQUIP], canceling sequence and rolling back data...'
                ROLLBACK TRANSACTION
                RETURN -18
            END
            PRINT '- Shield equipped.'
        END

        -- Make 100% and FB
        -- Gears
        PRINT 'Updating gear attributes to +' + CAST(@EQPlus AS VARCHAR(2)) + ' FB (Str/Int 7, Parry 60%, Durability 200%)'
        UPDATE _Items SET OptLevel = @EQPlus, Variance = 34359738336, MagParamNum = 4, MagParam1 = 30064771144, MagParam2 = 30064771150, MagParam3 = 858993459290, MagParam4 = 257698037898, MagParam5 = NULL, MagParam6 = NULL, MagParam7 = NULL, MagParam8 = NULL, MagParam9 = NULL, MagParam10 = NULL, MagParam11 = NULL, MagParam12 = NULL WHERE ID64 = @HelmItemID64 OR ID64 = @MailItemID64 OR ID64 = @ShoulderItemID64 OR ID64 = @GauntletItemID64 OR ID64 = @PantsItemID64 OR ID64 = @BootsItemID64
        -- Accessories
        PRINT 'Updating accessory attributes to +' + CAST(@EQPlus AS VARCHAR(2)) + ' FB (Str/Int 7, Freeze 20%, Electric Shock 20%, Burn 20%, Poison 20%, Zombie 20%)'
        UPDATE _Items SET OptLevel = @EQPlus, Variance = 1073741823, MagParamNum = 7, MagParam1 = 30064771144, MagParam2 = 30064771150, MagParam3 = 85899346100, MagParam4 = 85899346094, MagParam5 = 85899346088, MagParam6 = 85899346106, MagParam7 = 85899346112, MagParam8 = NULL, MagParam9 = NULL, MagParam10 = NULL, MagParam11 = NULL, MagParam12 = NULL WHERE ID64 = @EarringItemID64 OR ID64 = @NecklaceItemID64 OR ID64 = @RingLItemID64 OR ID64 = @RingRItemID64
        -- Weapon
        PRINT 'Updating weapon attributes to +' + CAST(@EQPlus AS VARCHAR(2)) + ' FB (Str/Int 7, Block 100, Attack Rate 60%, Durability 200%)'
        UPDATE _Items SET OptLevel = @EQPlus, Variance = 34359738336, MagParamNum = 5, MagParam1 = 30064771144, MagParam2 = 30064771150, MagParam3 = 858993459290, MagParam4 = 429496729714, MagParam5 = 257698037862, MagParam6 = NULL, MagParam7 = NULL, MagParam8 = NULL, MagParam9 = NULL, MagParam10 = NULL, MagParam11 = NULL, MagParam12 = NULL WHERE ID64 = @WeaponItemID64
        -- Shield
        IF (@ShieldItemID64 IS NOT NULL OR @ShieldItemID64 > 0)
        BEGIN
            PRINT 'Updating shield attributes to +' + CAST(@EQPlus AS VARCHAR(2)) + ' FB (Str/Int 7, Critical 100, Durability 200%)'
            UPDATE _Items SET OptLevel = @EQPlus, Variance = 34359738336, MagParamNum = 4, MagParam1 = 30064771144, MagParam2 = 30064771150, MagParam3 = 858993459290, MagParam4 = 429496729726, MagParam5 = NULL, MagParam6 = NULL, MagParam7 = NULL, MagParam8 = NULL, MagParam9 = NULL, MagParam10 = NULL, MagParam11 = NULL, MagParam12 = NULL WHERE ID64 = @ShieldItemID64
        END

        -- Set other attributes
        PRINT 'Adding Gold, SP, Inventory size...'
        UPDATE _Char SET RemainGold = 1000000000, RemainSkillPoint = 10000000, InventorySize = 109 WHERE _Char.CharID = @CharID

    COMMIT TRANSACTION
    PRINT 'Sequence complete.'

RETURN 1
-- EOF

How to use?

EXEC [SRO_VT_SHARD].[dbo].[_ADD_GM_GEAR] '<character_name>', '<gear_type>', <equipment_degree>, <plus>

Usage example:

EXEC [SRO_VT_SHARD].[_ADD_GM_GEAR] 'Mulan', 'CLOTHES', 11, 12

Will give: 'Mulan' (11 Degree, +12 100% FB) Weapon/Shield, Garment, Accessories, 1B Gold, 10M SP, and 109 Inventory slots.

Parameters:

• <character_name>: duh?
• <equipment_type>: HEAVY, LIGHT, or CLOTHES
• <equipment_degree>: duh?
• <plus>: duh?

Notes:

• Feel free to change anything
• GM levels determined by "sec_primary" group in TB_User table in [SRO_VT_ACCOUNT] database. I use default GM levels of 1-6 and 10. (GM level check section)
• I put SOSUN item suffix as default. (@itemSuffix settings, change it if you want)

Important stuff:

• Dependecies SP (make sure they are exist): _FN_ADD_INITIAL_EQUIP, _ADD_ITEM_EXTERN, _IsExistingCharName, _STRG_DEL_ITEM_NoTX
• This will put new equipment to equipment slots, even if they're empty.
• The character MUST wear a weapon (no need others, just weapon) (to trigger detection)
• Shield will automatically added if the character is using 1H weapon. (No shield for 2H)

Updates:

• Remove weapon type execution parameter
• Added detection feature for weapon and shield
• Added spam messages to SQL output window

Results:

Before:

Spoiler

After:

Spoiler

Weapon sample:

Spoiler

Accessory sample:

Spoiler

SQL spam:

Spoiler

cheers

 

[karemsame]

Good Work!

but why this

Invalid item reference ID, check item codename.
Msg 50000, Level 1, State 16

 

[SupremeSRO]

Great , keep sharing tt1:

 

[Witchy Moo]

-- Stored Procedure updated (check first post)

Good Work!

but why this

Take a look at @itemSuffix setting, put 'C_RARE' for SOSUN or set to whatever you want

 

[karemsame]

Your item code name doesn't exist, like i said, i put Egy stuff, for example:

'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_HA_SET_B_RARE'

means if the race is chinese, male, and you put "HEAVY" and "11" in parameter it will become:

ITEM_CH_M_HEAVY_11_HA_SET_B_RARE

(Egy A helm 11D) <-- is this exist in your database?

change the item suffix to whatever you want, for example:

change the '_HA_SET_B_RARE' to '_HA_C_RARE'

Then the item will become:

ITEM_CH_M_HEAVY_11_HA_C_RARE

(SOSUN 11D)

As for as I know there are no "_SET_B_RARE" for 12D and above.

Thank you!

 

[Witchy Moo]

-- Stored Procedure updated (check first post)

Thank you!

have fun

--
As a companion to that SP, I made a little something (which I forgot to include in that SP , sorry ), It is to add "GM Uniform" and "Devil Spirit (S) +10 FB 100%"

You can execute it directly from query window or you can patch it to that GM SP thingy if you want:

Just set @CharName and @CharGender and run it.

USE [SRO_VT_SHARD]
GO

/*
 * Simple T-SQL query to to add GM uniform and Devil Spirit (S) +10 FB 100%
 *
 * Notes:
 * MAKE SURE these items exists in your Media and database:
 * - ITEM_ETC_AVATAR_W_GM_UNIFORM
 * - ITEM_ETC_AVATAR_M_GM_UNIFORM
 * - ITEM_MALL_AVATAR_M_NASRUN_UNIQUE
 * - ITEM_MALL_AVATAR_W_NASRUN_UNIQUE
 *
 */

DECLARE @CharName VARCHAR(64)
DECLARE @CharGender VARCHAR(1)

SET @CharName = 'Witchy'    -- Set character name
SET @CharGender = 'W'    -- Set character gender , 'M' or 'W'

-- Variables declaration
DECLARE @CodeNameUniform    VARCHAR(256)
DECLARE @CodeNameDS            VARCHAR(256)
DECLARE @RefUniformID        INT
DECLARE @RefDSID            INT
DECLARE @RefUniformLinkID    INT
DECLARE @RefDSLinkID        INT

-- Set codenames
SET @CodeNameUniform = 'ITEM_ETC_AVATAR_' + @CharGender + '_GM_UNIFORM'
SET @CodeNameDS = 'ITEM_MALL_AVATAR_' + @CharGender + '_NASRUN_UNIQUE'

-- Find reference links
SELECT @RefUniformID = ID, @RefUniformLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameUniform
SELECT @RefDSID    = ID, @RefDSLinkID = link FROM [_RefObjCommon] WHERE CodeName128 = @CodeNameDS

IF (@RefUniformID IS NULL OR @RefUniformID = 0 OR @RefDSID IS NULL OR @RefDSID = 0)
BEGIN
    RAISERROR('Invalid item reference ID, check item codename.', 1, 16)
    RETURN
END
IF (@RefUniformLinkID IS NULL OR @RefUniformLinkID = 0 OR @RefDSLinkID IS NULL OR @RefDSLinkID = 0)
BEGIN
    RAISERROR('Invalid link reference ID, check item codename.', 1, 16)
    RETURN
END

-- GM Uniform + Devil Spirit - Start adding to inventory
BEGIN TRANSACTION
    DECLARE @UniformItemID64 BIGINT
    DECLARE @DSItemID64 BIGINT

    SET @UniformItemID64 = 0
    SET @DSItemID64 = 0

    -- GM uniform
    EXEC @UniformItemID64 = [_ADD_ITEM_EXTERN] @CharName, @CodeNameUniform, 1, 0
    IF (@UniformItemID64 <= 0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN
    END

    -- Devil Spirit
    EXEC @DSItemID64 = [_ADD_ITEM_EXTERN] @CharName, @CodeNameDS, 1, 10
    IF (@DSItemID64 <= 0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN
    END

    -- Make +10 and FB 100% for Devil Spirit
    UPDATE _Items SET OptLevel = 10, MagParamNum = 1, MagParam1 = 42949673313 WHERE ID64 = @DSItemID64

COMMIT TRANSACTION
RETURN

Result:


have fun

 

[Witchy Moo]

-- Stored Procedure updated (check first post).

Additional notes, in case someone needs it, about white and blue attributes value (Variance and MagParam):

Variance (white state)

• 34359738336 = Gear, Weapons, Shields all attributes 100%, expect durability
• 1073741823 = Accessories all attributes 100%

MagParam (blue state)

• 30064771144 = Str 7
• 30064771150 = Int 7
• 858993459290 = Durability 200% (Gears, Weapons, Shields)
• 429496729714 = Blocking Rate 100 (Weapons)
• 257698037862 = Attack Rate 60% (Weapons)
• 429496729726 = Critical Block 100 (Shields)
• 257698037898 = Parry Rate 60% (Gears)
• 85899346100 = Freeze 20% (Accessories)
• 85899346094 = Electric Shock 20%(Accessories)
• 85899346088 = Burn 20%(Accessories)
• 85899346106 = Poison 20%(Accessories)
• 85899346112 = Zombie 20%(Accessories)

Result:


cheers

 

[OutOfMemory]

Great work, i did that one before but yours in prof way.

 

[Witchy Moo]

Great work, i did that one before but yours in prof way.

Thank you for your feedback

-- Stored Procedure updated (check first post)

 

[ahmetagcan]

I get this error

 

[OutOfMemory]

I get this error

View attachment 139617

Just read the error -.-

Your codename of item is invalid.

What i have notice:

1- You have to make gm level limited because some members will not notice the gm level so you can do it like that.

IF (@GMLevel IS NULL OR @GMLevel !=1 OR @GMLevel !=2)

2- Invalid item reference ID, check item codename -- Anyone will use this procedure will get same error.

You have to re-check it again i just commented it and it works.

3- Shield and weapon are clean - You can edit them later.

I really did not have time to read the full code but looks fine.

 

[Witchy Moo]

-- Stored Procedure updated (check first post)

I get this error

View attachment 139617

To solve this problem take a look at @itemSuffix settings, set to something that's in your Media, for example: 'C_RARE' for SOSUN, etc etc..

good luck

Just read the error -.-

Your codename of item is invalid.

What i have notice:

1- You have to make gm level limited because some members will not notice the gm level so you can do it like that.

2- Invalid item reference ID, check item codename -- Anyone will use this procedure will get same error.

You have to re-check it again i just commented it and it works.

3- Shield and weapon are clean - You can edit them later.

I really did not have time to read the full code but looks fine.

Thank you

Btw point number 3, updated to FB and 100% state in the SP. about GM level, I use default GM levels in vsro database, based on "sec_primary" value in [SRO_VT_ACCOUNT].[TB_User] table, which are 1 to 6, and 10, here:

IF (@GMLevel IS NULL OR (@GMLevel > 6 AND @GMLevel < 10))

cheers

 

[Witchy Moo]

-- Stored Procedure updated (check first post)

 

[MissinGDeaTh]

Msg 166, Level 15, State 1, Line 1
'CREATE/ALTER PROCEDURE' does not allow specifying the database name as a prefix to the object name.
Msg 208, Level 16, State 6, Procedure _ADD_GM_GEAR, Line 2
Invalid object name 'dbo._ADD_GM_GEAR'.

??

 

[zaabner]

For me everything runs well, except making devil +10 and full blues shows: (0 row(s) affected) and devil is not fb, any suggestions?