Register for your free account! | Forgot your password?

You last visited: Today at 09:28

  • Please register to post and access all features, it's quick, easy and FREE!

 

[RELEASE] Improved HonorRank procedure

Reply
 
Old   #1
 
elite*gold: 6
Join Date: Oct 2013
Posts: 1,110
Received Thanks: 584
[RELEASE] Improved HonorRank procedure

Code:
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



Syloxx is offline  
Thanks
15 Users
Old   #2
 
elite*gold: 0
Join Date: Mar 2010
Posts: 620
Received Thanks: 59
useful thanks for hard work :-)


@$$ is offline  
Old   #3
 
elite*gold: 0
Join Date: Jan 2008
Posts: 76
Received Thanks: 7
what does it do ?
raptter is offline  
Old   #4
 
elite*gold: 2330
Join Date: Oct 2014
Posts: 363
Received Thanks: 275
Good job Syloxx =)


Crayon* is offline  
Old   #5
 
elite*gold: 6
Join Date: Oct 2013
Posts: 1,110
Received Thanks: 584
Quote:
Originally Posted by raptter View Post
what does it do ?
its a faster version of the joymax honorranking query
Syloxx is offline  
Old   #6

 
elite*gold: 0
Join Date: Jan 2015
Posts: 1,234
Received Thanks: 730
Quote:
Originally Posted by raptter View Post
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
Craine- is offline  
Old   #7
 
elite*gold: 41
Join Date: Oct 2012
Posts: 2,216
Received Thanks: 1,186
Quote:
Originally Posted by Leonius* View Post
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
Crue* is offline  
Thanks
1 User
Old   #8

 
elite*gold: 0
Join Date: Jan 2015
Posts: 1,234
Received Thanks: 730
Quote:
Originally Posted by Crue* View Post
i think he already did and answer him
Yea, he already answered him, i just explained it a bit more.
Craine- is offline  
Old   #9
 
elite*gold: 41
Join Date: Oct 2012
Posts: 2,216
Received Thanks: 1,186
Quote:
Originally Posted by Leonius* View Post
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 ****
Crue* is offline  
Thanks
3 Users
Old   #10
 
elite*gold: 0
Join Date: Mar 2015
Posts: 95
Received Thanks: 58
Quote:
Originally Posted by Leonius* View Post
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.
DeaDeployment is offline  
Thanks
2 Users
Old   #11
 
elite*gold: 0
Join Date: Oct 2012
Posts: 137
Received Thanks: 108
Quote:
Originally Posted by Syloxx View Post
Code:
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
geomangy is offline  
Old   #12
 
elite*gold: 6
Join Date: Oct 2013
Posts: 1,110
Received Thanks: 584
Quote:
Originally Posted by geomangy View Post
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)
Syloxx is offline  
Old   #13
 
elite*gold: 0
Join Date: Mar 2012
Posts: 573
Received Thanks: 17
the problem in the game?

Examples of such lag
ILowe is offline  
Old   #14
 
elite*gold: 375
Join Date: Mar 2015
Posts: 75
Received Thanks: 26
not bad
*--* is offline  
Old   #15
 
elite*gold: 6
Join Date: Oct 2013
Posts: 1,110
Received Thanks: 584
just ask me why finally people take care about performance...

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


Syloxx is offline  
Reply



« Previous Thread | Next Thread »

Similar Threads
[RELEASE] Professional Procedure (_AddNewCOS)
Today i wanna release a template how i write my queries i usually sell, since i wont release something important for free i simply rewrote the...
6 Replies - SRO PServer Guides & Releases
[Release] Silk Scroll Procedure
Here it is :) Add this to LOG_DB. _AddLogChar
3 Replies - SRO PServer Guides & Releases
[Release] Procedure for events
So, I did an event on my server but faced a huge problem, there was SO many players registered on it and add the rewards to them by hand sucks. For...
4 Replies - SRO PServer Guides & Releases



All times are GMT +1. The time now is 09:28.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.

Support | Contact Us | FAQ | Advertising | Privacy Policy | Abuse
Copyright ©2017 elitepvpers All Rights Reserved.