"Just for fun - Nothing to do" GM gear coupon :P

[Blacksheep25]

- _ADD_GM_GEAR Stored procedure located at:
[SRO_VT_SHARD].[dbo].[_ADD_GM_GEAR]

Spoiler:

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 Egy A and B 11D gears as default. (@GearSuffix settings, CHANGE IT)
* - Custom Devil Spirit item code. (@DSSuffix settings, CHANGE IT)
* - Custom title level. (@HwanLevel settings, CHANGE IT)
* - Custom GM avatar suffix. (@uniSuffix settings, CHANGE IT)
* - Maximum level parameter is taken from "_RefLevel" table
*
* 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.
*
* 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 */
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- @@@ BEGIN: SETTINGS

-- CHANGE THIS! -- CHANGE THIS! -- CHANGE THIS! (if needed)

-- (Represent ITEM_CH_BOW_11_SET_B_RARE), change as you need it
DECLARE @GearSuffix VARCHAR(16) = 'SET_B_RARE'

-- (Represent ITEM_ETC_AVATAR_W_GM_UNIFORM), change as you need it
DECLARE @uniSuffix VARCHAR(16) = 'GM_UNIFORM'

-- (Represent ITEM_MALL_AVATAR_W_NASRUN_S_BLUE), change as you need it
DECLARE @DSSuffix VARCHAR(16) = 'NASRUN_S_BLUE'

-- Represent custom title, change as you need it
DECLARE @HwanLevel TINYINT = 9

-- Change these if you want
DECLARE @Strength INT = 10000
DECLARE @Intellect INT = 10000
DECLARE @RemainGold INT = 1000000000
DECLARE @RemainSkillPoint INT = 15000000
DECLARE @InventorySize INT = 93

-- Gift Silk
DECLARE @GiftSilk INT = 1000

-- @@@ END: SETTINGS
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

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 CONDITION 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 BETWEEN 1907 AND 1919) -- Chinesse Male
BEGIN
SET @CharRace = 'CH'
SET @CharGender = 'M'
END
IF (@RefCharID BETWEEN 1920 AND 1932) -- Chinesse Female
BEGIN
SET @CharRace = 'CH'
SET @CharGender = 'W'
END
IF (@RefCharID BETWEEN 14875 AND 14887) -- European Male
BEGIN
SET @CharRace = 'EU'
SET @CharGender = 'M'
END
IF (@RefCharID BETWEEN 14888 AND 14900) -- European Female
BEGIN
SET @CharRace = 'EU'
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

-- GM Uniform + Devil Spirit
DECLARE @CodeNameUniform VARCHAR(256)
DECLARE @CodeNameDS VARCHAR(256)
DECLARE @RefUniformID INT
DECLARE @RefDSID INT
DECLARE @RefUniformLinkID INT
DECLARE @RefDSLinkID 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 [Service] = 1 AND 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_' + @GearSuffix
SET @CodeNameMail = 'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_BA_' + @GearSuffix
SET @CodeNameShoulder = 'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_SA_' + @GearSuffix
SET @CodeNameGauntlet = 'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_AA_' + @GearSuffix
SET @CodeNamePants = 'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_LA_' + @GearSuffix
SET @CodeNameBoots = 'ITEM_' + @CharRace + '_' + @CharGender + '_' + @EQClass + '_' + @EQDegree + '_FA_' + @GearSuffix

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

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

-- GM Uniform + Devil Spirit
SET @CodeNameUniform = 'ITEM_ETC_AVATAR_' + @CharGender + '_' + @uniSuffix
SET @CodeNameDS = 'ITEM_MALL_AVATAR_' + @CharGender + '_' + @DSSuffix

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

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

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

-- GM Uniform + Devil Spirit
SELECT @RefUniformID = ID, @RefUniformLinkID = link FROM [_RefObjCommon] WHERE [Service] = 1 AND CodeName128 = @CodeNameUniform
SELECT @RefDSID = ID, @RefDSLinkID = link FROM [_RefObjCommon] WHERE [Service] = 1 AND CodeName128 = @CodeNameDS

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 OR
@RefUniformID IS NULL OR @RefUniformID = 0 OR
@RefDSID IS NULL OR @RefDSID = 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 OR
@RefUniformLinkID IS NULL OR @RefUniformLinkID = 0 OR
@RefDSLinkID IS NULL OR @RefDSLinkID = 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))
-- 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_MOVE_SPEED_UP_50', 50, 0
EXEC [_ADD_ITEM_EXTERN] @CharName, 'ITEM_MALL_REVERSE_RETURN_SCROLL', 50, 0
EXEC [_ADD_ITEM_EXTERN] @CharName, 'ITEM_MALL_RETURN_SCROLL_HIGH_SPEED', 50, 0
EXEC [_ADD_ITEM_EXTERN] @CharName, 'ITEM_COS_P_GGLIDER_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

DECLARE @UniformItemID64 BIGINT
DECLARE @DSItemID64 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

SET @UniformItemID64 = 0
SET @DSItemID64 = 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

-- GM uniform
PRINT '- GM uniform added to inventory.'
EXEC @UniformItemID64 = [_ADD_ITEM_EXTERN] @CharName, @CodeNameUniform, 1, 0
IF (@UniformItemID64 <= 0)
BEGIN
PRINT 'Problem when executing [_ADD_ITEM_EXTERN], canceling sequence and rolling back data...'
ROLLBACK TRANSACTION
RETURN -19
END

-- Devil Spirit
PRINT '- Devil Spirit added to inventory.'
EXEC @DSItemID64 = [_ADD_ITEM_EXTERN] @CharName, @CodeNameDS, 1, 10
IF (@DSItemID64 <= 0)
BEGIN
PRINT 'Problem when executing [_ADD_ITEM_EXTERN], canceling sequence and rolling back data...'
ROLLBACK TRANSACTION
RETURN -20
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
PRINT '- Adding extra blue attributes to Chest: HP recovery/MP recovery 210%'
UPDATE _Items SET MagParamNum = 5, MagParam5 = 901943132463 WHERE ID64 = @MailItemID64

-- 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
PRINT '- Adding extra blue attributes to Earring: CombustionProbability 50% Reduce, SleepProbability 50% Reduce'
UPDATE _Items SET MagParamNum = 9, MagParam8 = 214748365115, MagParam9 = 214748365139 WHERE ID64 = @EarringItemID64
PRINT '- Adding extra blue attributes to Necklace: StunProbability 50% Reduce'
UPDATE _Items SET MagParamNum = 8, MagParam8 = 47244640547 WHERE ID64 = @NecklaceItemID64
PRINT '- Adding extra blue attributes to Rings: DiseaseProbability 25% Reduce, FearProbability 25% Reduce'
UPDATE _Items SET MagParamNum = 9, MagParam8 = 107374182751, MagParam9 = 107374182727 WHERE 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
PRINT '- Adding extra blue attributes to Weapon: Critical 3'
UPDATE _Items SET MagParamNum = 6, MagParam6 = 12884902155 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

-- Devil Spirit
PRINT 'Updating Devil Spirit attributes to +10 FB (10% Damage increase to unique)'
UPDATE _Items SET OptLevel = 10, MagParamNum = 1, MagParam1 = 42949673313 WHERE ID64 = @DSItemID64

-- Set other attributes
PRINT 'Adding Stat, Gold, SP, Inventory size...'
UPDATE [_Char] SET Strength = @Strength, Intellect = @Intellect, RemainGold = @RemainGold, RemainSkillPoint = @RemainSkillPoint, InventorySize = @InventorySize WHERE CharID = @CharID

-- Set maximum level and mastery
PRINT 'Setting maximum level and skill masteries...'
DECLARE @CharLevel TINYINT = (SELECT TOP 1 Lvl FROM [_RefLevel] ORDER BY Lvl DESC)
DECLARE @ExpOffset BIGINT = (SELECT Exp_C FROM [SRO_VT_SHARD].[dbo].[_RefLevel] WHERE Lvl = @CharLevel)
UPDATE [_Char] SET RemainStatPoint = 0, CurLevel = @CharLevel, MaxLevel = @CharLevel, ExpOffset = @ExpOffset WHERE CharID = @CharID
UPDATE [_CharSkillMastery] SET [Level] = @CharLevel WHERE CharID = @CharID

-- Adding title
PRINT 'Adding GM title...'
UPDATE [_Char] SET HwanLevel = @HwanLevel WHERE CharID = @CharID

-- _TimedJob
PRINT 'Cleaning _TimedJob and _TimedJobForPet records...'
DELETE FROM [_TimedJob] WHERE CharID = @CharID
DELETE FROM [_TimedJobForPet] WHERE CharID = @CharID

PRINT 'Adding Gift Silk...'
UPDATE [SRO_VT_ACCOUNT].[dbo].[SK_Silk] SET [silk_gift] = @GiftSilk WHERE JID = @UserJID

COMMIT TRANSACTION
PRINT 'Sequence complete.'

RETURN 1
-- EOF

GM Check removed, should beable to have everyone use it now.

[Witchy Moo]

- _ADD_GM_GEAR Stored procedure located at:
[SRO_VT_SHARD].[dbo].[_ADD_GM_GEAR]
GM Check removed, should beable to have everyone use it now.

Thank you ;), and I added video and GM check removal remark in the first post.

[P0kemonMast]

im having trouble adding the SP . i keep getting this when i execute the SP

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 558
Invalid object name 'dbo._ADD_GM_GEAR'.

[Witchy Moo]

im having trouble adding the SP . i keep getting this when i execute the SP

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 558
Invalid object name 'dbo._ADD_GM_GEAR'.

Seems that the SQL user (whatever user you're using to login to the database), doesn't have enough privileges to create new SP using database name as prefix. (not "sa" maybe?) :)

Try login using "sa" account, that should fix it.
good luck

[P0kemonMast]

i am loged in as "sa" tho o.O

[Witchy Moo]

i am loged in as "sa" tho o.O

Well, you can, however, create procedure manually before executing the SP (since problem you got related to the SQL not the SP) :P

Do this:

USE [SRO_VT_SHARD]
GO
CREATE PROCEDURE [_ADD_GM_GEAR] AS BEGIN SET NOCOUNT ON; END

Then re-run the SP :)

[P0kemonMast]

Thanks , now that worked :P . Hopefully the rest works now too

[Witchy Moo]

Thanks , now that worked :P . Hopefully the rest works now too

Awesome, let us know how it's going :)

[P0kemonMast]

Well another issue , it seems that when i add this into the _AddLogItem sp

IF (@Operation = 41) -- scroll is used
BEGIN
IF (@ItemRefID BETWEEN 46027 AND 46029) -- GM coupon
BEGIN
DECLARE @CharName VARCHAR(64) = (SELECT CharName16 from [SRO_VT_SHARD].[dbo].[_Char] WHERE CharID = @CharID)
DECLARE @GearType VARCHAR(16) =
CASE
WHEN @ItemRefID = 46027 THEN 'HEAVY'
WHEN @ItemRefID = 46028 THEN 'LIGHT'
WHEN @ItemRefID = 46029 THEN 'CLOTHES'
END
-- 11 = Degree, 16 = Da PLUS
EXEC [SRO_VT_SHARD].[dbo].[_ADD_GM_GEAR] @CharName, @GearType, 11, 16
END
END

the following 'EXEC [SRO_VT_SHARD].[dbo].[_ADD_GM_GEAR]' , comes out underlined. And saying that procedure is not found. When it was created already.

[Witchy Moo]

Well another issue , it seems that when i add this into the _AddLogItem sp
the following 'EXEC [SRO_VT_SHARD].[dbo].[_ADD_GM_GEAR]' , comes out underlined. And saying that procedure is not found. When it was created already.

Did you test by running the _ADD_GM_GEAR SP manually from SQL window?

EXEC [SRO_VT_SHARD].[dbo].[_ADD_GM_GEAR] 'yourcharname', 'LIGHT', 11, 16

Verify also if your database name is "SRO_VT_SHARD", and your current SQL user is mapped to "dbo" role membership. I think there are some who use different database name and different SQL user.

[P0kemonMast]

nope im getting this

Msg 8115, Level 16, State 2, Procedure _ADD_GM_GEAR, Line 29
Arithmetic overflow error converting expression to data type int.
Validating Character...
Validating Account ID...
Validating GM Level...
Validating race and gender...
Race code is: CH, gender code is: W.
Validating equipped weapon...
Checking if character is using shield...
Setting up item codenames...
Getting item reference ID and link...
Invalid item reference ID, check item codename (degree parameter or item suffix setting). Sequence canceled!
Msg 50000, Level 1, State 16

[CoderWaxy]

Not Work i Tested it

y+1 pokemon i get this error too

[Witchy Moo]

nope im getting this

"Invalid item reference ID, check item codename (degree parameter or item suffix setting). Sequence canceled!"

There, it's not SP error, but one of items there doesn't exist in your DB or Media, seems you didn't read the first post about "SETTINGS":

Stuff that NEEDED TO BE CHANGED in that NEW SP ("SETTINGS" section):

    -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    -- @@@ BEGIN: SETTINGS
    
    -- CHANGE THIS! -- CHANGE THIS! -- CHANGE THIS! (if needed)

    -- (Represent ITEM_CH_BOW_11_SET_B_RARE), change as you need it
    DECLARE @GearSuffix VARCHAR(16) = 'SET_B_RARE'

    -- (Represent ITEM_ETC_AVATAR_W_GM_UNIFORM), change as you need it
    DECLARE @uniSuffix VARCHAR(16) = 'GM_UNIFORM'

    -- (Represent ITEM_MALL_AVATAR_W_NASRUN_S_BLUE), change as you need it
    DECLARE @DSSuffix VARCHAR(16) = 'NASRUN_S_BLUE'

    -- Represent custom title, change as you need it
    DECLARE @HwanLevel TINYINT = 9

    -- Change these if you want
    DECLARE @Strength INT = 10000
    DECLARE @Intellect INT = 10000
    DECLARE @RemainGold INT = 1000000000
    DECLARE @RemainSkillPoint INT = 15000000
    DECLARE @InventorySize INT = 93

    -- Gift Silk
    DECLARE @GiftSilk INT = 1000

    -- @@@ END: SETTINGS
    -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

BE SURE you change those settings to match your DB or Media, I think i put enough understandable comments about it.

Let me break it down, for example, if you set:

DECLARE @GearSuffix VARCHAR(16) = 'C_RARE'
Means it will represent: "ITEM_<race>_<item>_<degree>_C_RARE" (For example: ITEM_CH_BOW_11_C_RARE) <-- is this item exist?

Goes the same for other settings. Verify if your item exist.

Not Work i Tested it

y+1 pokemon i get this error too

Yep, it won't work for you (same invalid item reference code error) if you only copy-paste it without verifying if the ITEM EXIST in your db or Media :)

Weapon/gear/accessory, Devil Spirit, GM avatar represented by each codes must exist in your DB or Media

[P0kemonMast]

Oh wow thanks. Found the problem , never knew i did not have the "NASRUN_S_BLUE" or any of the 'S' versions.

[Witchy Moo]

Oh wow thanks. Found the problem , never knew i did not have the "NASRUN_S_BLUE" or any of the 'S' versions.

Excellent, nice work. sorry about that, since I have custom devil spirit code in my db :P