[RELEASE] Improved HonorRank procedure

[Syloxx]

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TRAINING_CAMP_UPDATEHONORRANK]    Script Date: 23.03.2015 10:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/**
version : 1
author : syloxx
created date : 2015-28-02
description : Update academy ranking and award honor buffs.
return value :
1 = There is no error.
-1 = The transaction is in an uncommittable state. Rolling back transaction.
-2 = No camp reached the necessary requirements. 
**/

ALTER PROCEDURE [dbo].[_TRAINING_CAMP_UPDATEHONORRANK]
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @intReturnValue int
    , @intCampID int;

DECLARE @tblRanking table (ID int IDENTITY(1, 1) NOT NULL, CampID int NOT NULL, [Rank] tinyint NULL);

/**_# Rollback and return if inside an uncommittable transaction.*/
IF XACT_STATE() = -1
BEGIN
    SET @intReturnValue = -1;
    GOTO ErrorHandler;
END

BEGIN TRY
    BEGIN TRANSACTION;

    /**_# Insert candidates into variable table.*/
    INSERT @tblRanking (CampID)
    SELECT TOP 50 ID
    FROM dbo._TrainingCamp WITH (NOLOCK)
    WHERE EvaluationPoint >= 10
    ORDER BY EvaluationPoint DESC, LatestEvaluationDate ASC;

    IF @@ROWCOUNT = 0
    BEGIN
        SET @intReturnValue = -2;
        GOTO ErrorHandler;
    END

    /**_# Set the rank of every camp back to default.*/
    UPDATE _TrainingCamp
    SET Rank = 5
    WHERE Rank <> 5;

    /**_# Distribute the new ranks.*/
    UPDATE @tblRanking
    SET [Rank] = CASE
                   WHEN ID BETWEEN 1 AND 5
                    THEN 1
                   WHEN ID BETWEEN 6 AND 15
                    THEN 2
                   WHEN ID BETWEEN 16 AND 30
                    THEN 3
                   ELSE 4
               END;
    
    /**_# Update Table: [dbo].[_TrainingCamp]*/
    UPDATE trc
    SET [Rank] = rnk.[Rank]
    FROM _TrainingCamp trc, @tblRanking rnk
    WHERE trc.ID = rnk.CampID;

    /**_# Update Table: [dbo].[_TrainingCampHonorRank]*/
    UPDATE tcr
    SET CampID = rnk.CampID
        , [Rank] = rnk.[Rank]
    FROM _TrainingCampHonorRank tcr, @tblRanking rnk
    WHERE tcr.Ranking = rnk.ID;

    /**_# Log last execute date.*/
    IF EXISTS (
        SELECT *
        FROM _TrainingCampHonorRankUpdateDate
    )
        UPDATE _TrainingCampHonorRankUpdateDate
        SET LastUpdateDate = GETDATE();
    ELSE
        INSERT _TrainingCampHonorRankUpdateDate (LastUpdateDate)
        VALUES (GETDATE());

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    GOTO ErrorHandler;
END CATCH;

RETURN 1;

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

RETURN @intReturnValue

[@$$]

useful thanks for hard work :-)

[raptter]

what does it do ?

[Crayon*]

Good job Syloxx =)

[Syloxx]

Originally Posted by raptter

what does it do ?

its a faster version of the joymax honorranking query

[Aaron*]

Originally Posted by raptter

what does it do ?

It simply updates honor ranking, it is a bit faster than the original one, it does not really matter how fast is it as long as they both work xD

[Crue*]

Originally Posted by Leonius*

It simply updates honor ranking, it is a bit faster than the original one, it does not really matter how fast is it as long as they both work xD

i think he already did and answer him

[Aaron*]

Originally Posted by Crue*

i think he already did and answer him

Yea, he already answered him, i just explained it a bit more.

[Crue*]

Originally Posted by Leonius*

Yea, he already answered him, i just explained it a bit more.

nope you just added this word 'updates' only and that's mean you didn't explain any ****

[DeaDeployment]

Originally Posted by Leonius*

It simply updates honor ranking, it is a bit faster than the original one, it does not really matter how fast is it as long as they both work xD

illuminati Confirmed.

[geomangy]

Originally Posted by Syloxx

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TRAINING_CAMP_UPDATEHONORRANK]    Script Date: 23.03.2015 10:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/**
version : 1
author : syloxx
created date : 2015-28-02
description : Update Job ranking and award honor buffs.
return value :
1 = There is no error.
-1 = The transaction is in an uncommittable state. Rolling back transaction.
-2 = No camp reached the necessary requirements. 
**/

ALTER PROCEDURE [dbo].[_TRAINING_CAMP_UPDATEHONORRANK]
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @intReturnValue int
    , @intCampID int;

DECLARE @tblRanking table (ID int IDENTITY(1, 1) NOT NULL, CampID int NOT NULL, [Rank] tinyint NULL);

/**_# Rollback and return if inside an uncommittable transaction.*/
IF XACT_STATE() = -1
BEGIN
    SET @intReturnValue = -1;
    GOTO ErrorHandler;
END

BEGIN TRY
    BEGIN TRANSACTION;

    /**_# Insert candidates into variable table.*/
    INSERT @tblRanking (CampID)
    SELECT TOP 50 ID
    FROM dbo._TrainingCamp WITH (NOLOCK)
    WHERE EvaluationPoint >= 10
    ORDER BY EvaluationPoint DESC, LatestEvaluationDate ASC;

    IF @@ROWCOUNT = 0
    BEGIN
        SET @intReturnValue = -2;
        GOTO ErrorHandler;
    END

    /**_# Set the rank of every camp back to default.*/
    UPDATE _TrainingCamp
    SET Rank = 5
    WHERE Rank <> 5;

    /**_# Distribute the new ranks.*/
    UPDATE @tblRanking
    SET [Rank] = CASE
                   WHEN ID BETWEEN 1 AND 5
                    THEN 1
                   WHEN ID BETWEEN 6 AND 15
                    THEN 2
                   WHEN ID BETWEEN 16 AND 30
                    THEN 3
                   ELSE 4
               END;
    
    /**_# Update Table: [dbo].[_TrainingCamp]*/
    UPDATE trc
    SET [Rank] = rnk.[Rank]
    FROM _TrainingCamp trc, @tblRanking rnk
    WHERE trc.ID = rnk.CampID;

    /**_# Update Table: [dbo].[_TrainingCampHonorRank]*/
    UPDATE tcr
    SET CampID = rnk.CampID
        , [Rank] = rnk.[Rank]
    FROM _TrainingCampHonorRank tcr, @tblRanking rnk
    WHERE tcr.Ranking = rnk.ID;

    /**_# Log last execute date.*/
    IF EXISTS (
        SELECT *
        FROM _TrainingCampHonorRankUpdateDate
    )
        UPDATE _TrainingCampHonorRankUpdateDate
        SET LastUpdateDate = GETDATE();
    ELSE
        INSERT _TrainingCampHonorRankUpdateDate (LastUpdateDate)
        VALUES (GETDATE());

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    GOTO ErrorHandler;
END CATCH;

RETURN 1;

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

RETURN @intReturnValue

I Think You Just Try To Waste Your Free Time
You Are Professional Coder And U Can Do Better Than That
Try To Code Any New Idea

[Syloxx]

Originally Posted by geomangy

I Think You Just Try To Waste Your Free Time
You Are Professional Coder And U Can Do Better Than That
Try To Code Any New Idea

actually i made it on request

a friend asked me 2 do it because his server was laggy (this procedure issues)

[ILowe]

the problem in the game?

Examples of such lag

[*--*]

not bad

[Syloxx]

just ask me why finally people take care about performance...

all the time it was like "who cares about performance my query works too"