[RELEASE] ItemSwitch System

[Syloxx]

Hey Elitepvpers,

someone asked me to create a Devil Spirit gender switch and i decided to create a generally ItemSwitch query out of it

maybe its usefull for some of you guys.

TABLE:

CREATE TABLE dbo._RefItemSwitch
(
      [Service] INT NOT NULL
    , RefItemID1 INT NOT NULL
    , RefItemID2 INT NOT NULL
    , RefScroll VARCHAR(129) NOT NULL
);

PROCEDURE:

/**
version : 1
author : syloxx
created date : 2015-06-07
description : change gender / switch items
return :
0 = There is no error.
-1 = The transaction is in an uncommittable state. Rolling back transaction.
-2 = Slot 13 is emptry.
-3 = Item in slot 13 is not allowed to switch.
-4 = Unknown error.
**/

CREATE PROCEDURE dbo._ItemSwitcher
      @CharID int
    , @Scroll varchar(129)
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @ReturnValue int
    , @ItemID bigint
    , @OldRefItemID int
    , @NewRefItemID1 int
    , @NewRefItemID2 int
    , @NewRefItemID int

IF XACT_STATE() = -1
BEGIN
    SET @ReturnValue = -1;
    GOTO ErrorHandler;
END
BEGIN TRY
    /**_# Get ItemID of item in slot 13.*/
    SELECT @ItemID = INV.ItemID
        , @OldRefItemID = I.RefItemID
    FROM dbo._Inventory INV WITH (NOLOCK)
        JOIN _Items I WITH (NOLOCK) ON INV.ItemID = I.ID64
    WHERE INV.CharID = @CharID
    AND INV.Slot = 13;
    
    /**_# Check if slot 13 is empty.*/
    IF @OldRefItemID IS NULL OR @OldRefItemID = 0
    BEGIN
        SET @ReturnValue = -2;
        GOTO ErrorHandler;
    END
    
    /**_# Get all possible ItemIDs.*/
    SELECT TOP 1 @NewRefItemID1 = RefItemID1
        , @NewRefItemID2 = RefItemID2
    FROM _RefItemSwitch WITH (NOLOCK)
    WHERE Service = 1
    AND (RefItemID1 = @OldRefItemID OR RefItemID2 = @OldRefItemID)
    AND RefScroll = RefScroll
    
    /**_# Check if item is allowed to switch.*/
    IF @NewRefItemID1 IS NULL OR @NewRefItemID2 IS NULL OR @NewRefItemID1 = 0 OR @NewRefItemID2 = 0
    BEGIN
        SET @ReturnValue = -3;
        GOTO ErrorHandler;
    END
    
    /**_# Set the new ItemID.*/
    SET @NewRefItemID = CASE
                            WHEN @NewRefItemID1 = @OldRefItemID THEN @NewRefItemID2
                            WHEN @NewRefItemID2 = @OldRefItemID THEN @NewRefItemID1
                            ELSE @OldRefItemID
                        END
    
    /**_# Debug for an unknown error.*/
    IF @NewRefItemID = @OldRefItemID
    BEGIN
        SET @ReturnValue = -4;
        GOTO ErrorHandler;
    END
    
    BEGIN TRANSACTION;
    
    /**_# Finally switch the Item.*/
    UPDATE _Items
    SET RefItemID = @NewRefItemID
    WHERE ID64 = @ItemID
    
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
        GOTO ErrorHandler;
END CATCH;

RETURN 0;

ErrorHandler:
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;

DECLARE @CharName varchar(17);

SELECT @CharName = CharName16
FROM _Char WITH (NOLOCK)
WHERE CharID = @CharID;

EXEC _ADD_ITEM_EXTERN @CharName, @Scroll, 1, 0;

RETURN @ReturnValue;

TUTORIAL:

1) fill table _RefItemSwitch with Service (0 = disabled, 1 = enabled), RefItemID1 (male for example), RefItemID2 (female for example), RefScroll (codename of the scroll).
2) create a new scroll i wont explain how to do it
3) paste the function to trigger this procedure in _AddLogChar

[Justin1337*]

Cool, thanks Syloxx. =)

[R3D*]

It is good to see something useful that may help the beginners in this community! Good Job!

[Emperix]

Thanks alot

[Justin1337*]

Originally Posted by Acs'

It is good to see something useful that may help the beginners in this community! Good Job!

Originally Posted by Emperix

Thanks alot

07/06/2015, 08:00 glad to see i am not the only one going blind. XD

[Worshiper]

Thanks For it its good idea

[R3D*]

Originally Posted by Justin1337*

07/06/2015, 08:00 glad to see i am not the only one going blind. XD

I already realized that when this guy @Emperix has done a replay. xD

[Emperix]

Originally Posted by Justin1337*

07/06/2015, 08:00 glad to see i am not the only one going blind. XD

lol it had no comments so i thought its new lol