User Tag List

Page 1 of 3 1 2 3 LastLast
Results 1 to 7 of 20

Thread: SQL routine to create level stats

  1. #1
    Glorious leifus's Avatar

    Join Date
    Dec 2014
    Location
    Norway
    Posts
    14
    XP
    5,001,390
    Level
    500
    Thanks
    0
    Thanked 7 Times in 3 Posts

    SQL routine to create level stats

    Hello, today i want to realease routin i made a while ago to create level stats.

    This easily editable routin you can use to create level stats for your server and here is a example for "pet_levelstats". So this adds 175 levels from the current max level, so if the max level is 83 it will add level stats up to 258(83+175 =258), but you can change that by editing the 175 number.

    The percentage you can change where it says @percent = 1.10 and you can change how you want(1.05 = 5%, 1.75 = 75%) and so on.

    Table can be changed by editing @tableh = pet_levelstats

    Code:
    BEGIN
    
    SET
    @percent = 1.10, /* increase 10% from last level */
    @tableh = pet_levelstats; /* Table you are going to use */
    
    DECLARE v1 INT DEFAULT 175; /* How many times you want it to run*/
     
     WHILE v1 > 0 
     DO
      
      INSERT INTO @tableh (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
      SELECT 
        `creature_entry` 
       ,`level` + 1 `level` 
       ,CAST(`hp`  * @percent AS UNSIGNED) `hp`
       ,CAST(`mana`  * @percent AS UNSIGNED) `mana`
       ,CAST(`armor`  * @percent AS UNSIGNED) `armor`
       ,CAST(`str`  * @percent AS UNSIGNED) `str`
       ,CAST(`agi`  * @percent AS UNSIGNED) `agi`
       ,CAST(`sta`  * @percent AS UNSIGNED) `sta`
       ,CAST(`inte`  * @percent AS UNSIGNED) `inte`
       ,CAST(`spi`  * @percent AS UNSIGNED) `spi`
      FROM @tableh
      WHERE `level` = (SELECT MAX(`level`) FROM @tableh);
    
      SET v1 = v1 - 1;
     END WHILE;
     
    END
    When it comes to changing the table, you have to change the table colums so they are in tact with the table you change i to. As an example if you want to change to "player_levelstats" you have to change the Insert command to this
    Code:
    - INSERT INTO @tableh (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
    + INSERT INTO @tableh (`race`, `class`, `level`, `str`, `agi`, `sta`, `inte`, `spi`)
    and the select command to this
    Code:
    - SELECT 
    -    `creature_entry` 
    -   ,`level` + 1 `level` 
    -   ,CAST(`hp`  * @percent AS UNSIGNED) `hp`
    -   ,CAST(`mana`  * @percent AS UNSIGNED) `mana`
    -   ,CAST(`armor`  * @percent AS UNSIGNED) `armor`
    -   ,CAST(`str`  * @percent AS UNSIGNED) `str`
    -   ,CAST(`agi`  * @percent AS UNSIGNED) `agi`
    -   ,CAST(`sta`  * @percent AS UNSIGNED) `sta`
    -   ,CAST(`inte`  * @percent AS UNSIGNED) `inte`
    -   ,CAST(`spi`  * @percent AS UNSIGNED) `spi`
    -  FROM @tableh
    -  WHERE `level` = (SELECT MAX(`level`) FROM @tableh);
    
    + SELECT 
    +    `race`
    +   ,`class`
    +   ,`level` + 1 `level` 
    +   ,CAST(`str`  * @percent AS UNSIGNED) `str`
    +   ,CAST(`agi`  * @percent AS UNSIGNED) `agi`
    +   ,CAST(`sta`  * @percent AS UNSIGNED) `sta`
    +   ,CAST(`inte`  * @percent AS UNSIGNED) `inte`
    +   ,CAST(`spi`  * @percent AS UNSIGNED) `spi`
    +  FROM @tableh
    +  WHERE `level` = (SELECT MAX(`level`) FROM @tableh);
    and make sure they are in the right order. You can also use this to create xp_for_level and other things you need to scale if you are creating a hige level server.

    This could probebly done easyer and better, but i did it this way and if you see something i could have possible done better, please let me know :)

    Hope you like it :)

  2. The Following 4 Users Say Thank You to leifus For This Useful Post:


  3. #2
    Banned
    Join Date
    Apr 2013
    Posts
    237
    Thanks
    34
    Thanked 82 Times in 38 Posts
    This will come in handy for some people, thank you!

  4. #3
    Enthusiast
    Join Date
    Oct 2015
    Posts
    98
    XP
    1,391
    Level
    10
    Thanks
    19
    Thanked 10 Times in 10 Posts
    Thanks! Added to my SQL utilities list ^^ It was very usefull

    Regards

  5. The Following User Says Thank You to jonmii For This Useful Post:


  6. #4
    Original Poster
    Glorious leifus's Avatar

    Join Date
    Dec 2014
    Location
    Norway
    Posts
    14
    XP
    5,001,390
    Level
    500
    Thanks
    0
    Thanked 7 Times in 3 Posts
    Quote Originally Posted by Portals View Post
    This will come in handy for some people, thank you!
    Quote Originally Posted by jonmii View Post
    Thanks! Added to my SQL utilities list ^^ It was very usefull

    Regards
    Thanks guys, glad you like it :)

  7. #5
    Enthusiast
    Join Date
    Mar 2014
    Posts
    17
    XP
    1,878
    Level
    12
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Not sure why this wont work, gives me error online 3

  8. #6
    Founder <span style='color: #FF4500'>Tommy</span>'s Avatar
    Join Date
    Apr 2013
    Posts
    7,391
    XP
    3,449,104
    Level
    378
    Thanks
    889
    Thanked 1,856 Times in 1,119 Posts
    Quote Originally Posted by revowow View Post
    Not sure why this wont work, gives me error online 3
    Can you screenshot the error?

  9. #7
    Enthusiast Marko's Avatar
    Join Date
    Feb 2015
    Location
    Serbia
    Posts
    55
    XP
    1,619
    Level
    11
    Thanks
    9
    Thanked 2 Times in 2 Posts
    SET
    @percent = 1.10,
    @tableh = pet_levelstats;
    /* SQL Error (1054): Unknown column 'pet_levelstats' in 'field list' */
    DECLARE v1 INT DEFAULT 171;
    /* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE v1 INT DEFAULT 171' at line 1 */
    WHILE v1 > 0
    DO

    INSERT INTO @tableh (`pet_levelstats`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)
    SELECT
    `creature_entry`
    ,`level` + 1 `level`
    ,CAST(`hp` * @percent AS UNSIGNED) `hp`
    ,CAST(`mana` * @percent AS UNSIGNED) `mana`
    ,CAST(`armor` * @percent AS UNSIGNED) `armor`
    ,CAST(`str` * @percent AS UNSIGNED) `str`
    ,CAST(`agi` * @percent AS UNSIGNED) `agi`
    ,CAST(`sta` * @percent AS UNSIGNED) `sta`
    ,CAST(`inte` * @percent AS UNSIGNED) `inte`
    ,CAST(`spi` * @percent AS UNSIGNED) `spi`
    FROM @tableh
    WHERE `level` = (SELECT MAX(`level`) FROM @tableh);
    /* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@tableh (`pet_levelstats`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, ' at line 4 */
    SET v1 = v1 - 1;
    /* SQL Error (1193): Unknown system variable 'v1' */
    /* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 5 queries: 0.000 sec. */
    tables and rows in my db have same names as in this query but it does not work :(

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •