레드문 케릭터포인터 변경 프로시져

CREATE PROCEDURE RMS_BONUSPOINT_USE
    @GameID VARCHAR(16),
    @BonusPoint    INT,
    @Bonus2Point    INT,
    @SBonusPoint    INT,
    @AbilityType    INT,
    @TypePoint    INT,
    @Bonus    INT    OUTPUT,
    @Bonus2    INT    OUTPUT,
    @SBonus    INT    OUTPUT,
    @STotalBonus    INT    OUTPUT,
    @AbilityPoint    INT    OUTPUT
AS

SET NOCOUNT ON

DECLARE @Error INT, @RowCount INT, @ReturnValue INT
DECLARE @TotalUsePoint INT
DECLARE @MBonus INT, @MBonus2 INT, @MSBonus INT, @MTotal INT
DECLARE @CurBonus INT, @CurBonus2 INT, @CurSBonus INT
SELECT @Error = 0, @RowCount = 1, @ReturnValue = -1

IF @BonusPoint < 0 OR @Bonus2Point < 0 OR @SBonusPoint < 0 OR (@BonusPoint= 0 AND @Bonus2Point = 0 AND @SBonusPoint = 0)
BEGIN
    RETURN @ReturnValue
END

SET @TotalUsePoint = @BonusPoint + @Bonus2Point + @SBonusPoint

/* 아래의 10의 숫자를 바꾸시면 올리는 포인트를 조정할 수 있습니다.
예를 들어, 5로 바꾸면, 5/50으로 설정됩니다.
아래 세개의 숫자를 동시에 다 바꿔주셔야 합니다. */
SET @MBonus = @BonusPoint*5
SET @MBonus2 = @Bonus2Point*5
SET @MSBonus = @SBonusPoint*5
SET @MTotal = @MBonus + @MBonus2 + @MSBonus

SELECT @CurBonus=Bonus, @CurBonus2=Bonus2, @CurSBonus=SBonus FROM tblGameID1 WHERE GameID = @GameID

BEGIN TRANSACTION

IF (@CurBonus >= @MBonus) AND (@CurBonus2 >= @MBonus2) AND (@CurSBonus >= @MSBonus)
BEGIN
    SET @TotalUsePoint= @MTotal

    IF @BonusPoint > 0
    BEGIN
        UPDATE tblGameID1 SET Bonus = Bonus - @MBonus WHERE GameID = @GameID AND Bonus >= @MBonus
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
    END

    IF @Error = 0 AND @RowCount > 0 AND @Bonus2Point > 0
    BEGIN
        UPDATE tblGameID1 SET Bonus2 = Bonus2 - @MBonus2 WHERE GameID = @GameID AND Bonus2 >= @MBonus2
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
    END   

    IF @Error = 0 AND @RowCount > 0 AND @SBonusPoint > 0
    BEGIN
        UPDATE tblGameID1 SET SBonus = SBonus - @MSBonus WHERE GameID = @GameID AND SBonus >= @MSBonus
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
    END
END

ELSE
BEGIN
    IF @BonusPoint > 0
    BEGIN
        UPDATE tblGameID1 SET Bonus = Bonus - @BonusPoint WHERE GameID = @GameID AND Bonus >= @BonusPoint
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
    END

    IF @Error = 0 AND @RowCount > 0 AND @Bonus2Point > 0
    BEGIN
        UPDATE tblGameID1 SET Bonus2 = Bonus2 - @Bonus2Point WHERE GameID = @GameID AND Bonus2 >= @Bonus2Point
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
    END   

    IF @Error = 0 AND @RowCount > 0 AND @SBonusPoint > 0
    BEGIN
        UPDATE tblGameID1 SET SBonus = SBonus - @SBonusPoint WHERE GameID = @GameID AND SBonus >= @SBonusPoint
        SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
    END
END

    IF @Error = 0 AND @RowCount > 0 AND @TypePoint > 0
    BEGIN
        IF @AbilityType = 1
        BEGIN
            UPDATE tblGameID1 SET Strength = (Strength + @TypePoint * @TotalUsePoint ) WHERE GameID = @GameID
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
        END
        ELSE IF @AbilityType = 2
        BEGIN
            UPDATE tblGameID1 SET Spirit = (Spirit + @TypePoint * @TotalUsePoint ) WHERE GameID = @GameID
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
        END
        ELSE IF @AbilityType = 3
        BEGIN
            UPDATE tblGameID1 SET Dexterity = (Dexterity + @TypePoint * @TotalUsePoint ) WHERE GameID = @GameID
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
        END
        ELSE IF @AbilityType = 4
        BEGIN
            UPDATE tblGameID1 SET Power = (Power + @TypePoint * @TotalUsePoint ) WHERE GameID = @GameID
            SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
        END
       
        IF @Error = 0 AND @RowCount > 0
        BEGIN
            SELECT @Bonus = Bonus, @Bonus2 = Bonus2, @SBonus = SBonus, @STotalBonus = STotalBonus FROM tblGameID1 WHERE GameID = @GameID
            IF @AbilityType = 1
                SELECT @AbilityPoint = Strength FROM tblGameID1 WHERE GameID = @GameID
            ELSE IF @AbilityType = 2
                SELECT @AbilityPoint = Spirit FROM tblGameID1 WHERE GameID = @GameID
            ELSE IF @AbilityType = 3
                SELECT @AbilityPoint = Dexterity FROM tblGameID1 WHERE GameID = @GameID
            ELSE IF @AbilityType = 4
                SELECT @AbilityPoint = Power FROM tblGameID1 WHERE GameID = @GameID
            SET @ReturnValue = @TotalUsePoint
            COMMIT TRANSACTION
        END
        ELSE
        BEGIN
            ROLLBACK TRANSACTION
        END
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION
    END


RETURN @ReturnValue
GO