Register for your free account! | Forgot your password?

You last visited: Today at 04:06

  • 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,132
Received Thanks: 608
[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 03/23/2015, 12:32   #2
 
elite*gold: 0
Join Date: Mar 2010
Posts: 627
Received Thanks: 61
useful thanks for hard work :-)


@$$ is offline  
Old 03/23/2015, 13:27   #3
 
elite*gold: 0
Join Date: Jan 2008
Posts: 79
Received Thanks: 8
what does it do ?
raptter is offline  
Old 03/23/2015, 13:33   #4
 
elite*gold: 1830
Join Date: Oct 2014
Posts: 398
Received Thanks: 328
Good job Syloxx =)


Crayon* is offline  
Old 03/23/2015, 13:48   #5
 
elite*gold: 6
Join Date: Oct 2013
Posts: 1,132
Received Thanks: 608
Quote:
Originally Posted by raptter View Post
what does it do ?
its a faster version of the joymax honorranking query
Syloxx is offline  
Old 03/23/2015, 15:01   #6

 
elite*gold: 2
Join Date: Jan 2015
Posts: 1,261
Received Thanks: 776
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 03/23/2015, 15:08   #7
 
elite*gold: 41
Join Date: Oct 2012
Posts: 2,216
Received Thanks: 1,187
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 03/23/2015, 15:39   #8

 
elite*gold: 2
Join Date: Jan 2015
Posts: 1,261
Received Thanks: 776
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 03/23/2015, 15:41   #9
 
elite*gold: 41
Join Date: Oct 2012
Posts: 2,216
Received Thanks: 1,187
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 03/23/2015, 16:37   #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 03/23/2015, 21:24   #11
 
elite*gold: 0
Join Date: Oct 2012
Posts: 137
Received Thanks: 109
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 03/23/2015, 21:45   #12
 
elite*gold: 6
Join Date: Oct 2013
Posts: 1,132
Received Thanks: 608
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 03/23/2015, 22:53   #13
 
elite*gold: 0
Join Date: Mar 2012
Posts: 589
Received Thanks: 18
the problem in the game?

Examples of such lag
ILowe is offline  
Old 03/24/2015, 02:22   #14
 
elite*gold: 375
Join Date: Mar 2015
Posts: 75
Received Thanks: 26
not bad
*--* is offline  
Old 03/25/2015, 20:58   #15
 
elite*gold: 6
Join Date: Oct 2013
Posts: 1,132
Received Thanks: 608
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



« Adv c & d & s | 7/24 Botting on VPS Server! Need help.. »

Similar Threads
[RELEASE] Professional Procedure (_AddNewCOS)
01/08/2015 - SRO PServer Guides & Releases - 6 Replies
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 procedure _AddNewCOS. This release is for advertising myself since many people ask "how do you code" before they wanna hire me so in future i simply can forward them to this release For a better formatting download the attach. USE SRO_VT_SHARD SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[Release] Silk Scroll Procedure
11/24/2014 - SRO PServer Guides & Releases - 3 Replies
Here it is :) Add this to LOG_DB. _AddLogChar
[Release] Procedure for events
05/17/2012 - SRO PServer Guides & Releases - 4 Replies
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 solve this, I did an small procedure and a table which can be re-utilized for any kind of event. With this procedure you can give event rewards to your players with an ease, you only have to code an small php page or just take the char names by hand and insert them into a table in your database, then modify and execute the procedure. The rewards...



All times are GMT +1. The time now is 04:06.


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

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