[RELEASE] Upgrade System

/**
version : 1
author : syloxx
created date : 2015-03-23
description : upgrade your items.
return value :
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 in upgrade table.
**/

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

DECLARE @ReturnValue int
    , @ItemID bigint
    , @BeforeRefID int
    , @BeforeOptLevel tinyint
    , @UpgradeRefID int
    , @UpgradeOptLevel tinyint;

IF XACT_STATE() = -1
BEGIN
    SET @ReturnValue = -1;
    GOTO ErrorHandler;
END

BEGIN TRY
    /**_# Get ItemID of item in slot 13.*/
    SELECT @ItemID = ItemID
    FROM dbo._Inventory WITH (NOLOCK)
    WHERE CharID = @CharID
    AND Slot = 13;

    /**_# Check if slot 13 is empty.*/
    IF @ItemID IS NULL OR @ItemID = 0
    BEGIN
        SET @ReturnValue = -2;
        GOTO ErrorHandler;
    END

    /**_# Get RefID and OptLevel of item in slot 13.*/
    SELECT @BeforeRefID = RefItemID
        , @BeforeOptLevel = OptLevel
    FROM dbo._Items WITH (NOLOCK)
    WHERE ID64 = @ItemID;

    /**_# Get all required informations to upgrade the item.*/
    SELECT TOP 1 @UpgradeRefID = UpgradeRefID
        , @UpgradeOptLevel = UpgradeOptLevel
    FROM _RefUpgradeConfig WITH (NOLOCK)
    WHERE Service = 1
    AND BeforeRefID = @BeforeRefID
    AND BeforeOptLevel <= @BeforeOptLevel
    ORDER BY BeforeOptLevel DESC;

    /**_# Check if item in slot 13 exsits in upgrade table.*/
    IF @UpgradeRefID IS NULL OR @UpgradeRefID = 0
    BEGIN
        SET @ReturnValue = -3;
        GOTO ErrorHandler;
    END

    BEGIN TRANSACTION;

    /**_# Upgrade your item.*/
    UPDATE _Items
    SET RefItemID = @UpgradeRefID
        , OptLevel = @UpgradeOptLevel
    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(16);

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

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

RETURN @ReturnValue;

CREATE TABLE _RefUpgradeConfig
(
  Service bit NOT NULL
, BeforeRefID int NOT NULL
, BeforeOptLevel tinyint NOT NULL
, UpgradeRefID int NOT NULL
, UpgradeOptLevel tinyint NOT NULL
);