SQL routine to create level stats

leifus · Apr 20, 2016

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:

[COLOR="#FF0000"]- INSERT INTO @tableh (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, `inte`, `spi`)[/COLOR]
[COLOR="#008000"]+ INSERT INTO @tableh (`race`, `class`, `level`, `str`, `agi`, `sta`, `inte`, `spi`)[/COLOR]

and the select command to this

Code:

[COLOR="#FF0000"]- 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);[/COLOR]

[COLOR="#008000"]+ 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);[/COLOR]

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

Portals · Apr 20, 2016

This will come in handy for some people, thank you!

jonmii · Apr 21, 2016

Thanks! Added to my SQL utilities list ^^ It was very usefull

Regards

leifus · Apr 22, 2016

Portals said:
This will come in handy for some people, thank you!

jonmii said:
Thanks! Added to my SQL utilities list ^^ It was very usefull

Regards

Thanks guys, glad you like it

revowow · Aug 15, 2016

Not sure why this wont work, gives me error online 3

Tommy · Aug 16, 2016

revowow said:
Not sure why this wont work, gives me error online 3

Can you screenshot the error?

Marko · Nov 20, 2016

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

MrPixelMC · Nov 20, 2016

Marko said:
tables and rows in my db have same names as in this query but it does not work

What error does it give?

slp13at420 · Nov 20, 2016

Code:

[COLOR="#808080"]
/* SQL Error (1193): Unknown system variable 'v1' */
[/COLOR]

is the error he is getting.
By simply adding a `@` infront of the variable `v1` (i.e.`@v1`) I got it to stop throwing that error.

Marko · Nov 21, 2016

errors

slp13at420 said:
Code:

[COLOR="#808080"] /* SQL Error (1193): Unknown system variable 'v1' */ [/COLOR]

is the error he is getting.
By simply adding a `@` infront of the variable `v1` (i.e.`@v1`) I got it to stop throwing that error.

Have put @ in front of every v1 now, like this:

Code:

SET
@percent = 1.20,
@tableh = pet_levelstats; 
DECLARE @v1 INT DEFAULT 10; 
 
 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;

but again errors:

Code:

SET
@percent = 1.20,
@tableh = pet_levelstats;
/* SQL Error (1054): Unknown column 'pet_levelstats' in 'field list' */
DECLARE @v1 INT DEFAULT 10;
/* 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 10' at line 1 */
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);
/* 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 (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`, `sta`, ' at line 4 */
SET @v1 = @v1 - 1;
END WHILE;
/* 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 'END WHILE' at line 1 */
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 4 of 5 queries: 0.000 sec. */

:foreveralone:

Rochet2 · Nov 21, 2016

You cannot use @varname for table names. You must explicitly set the table name.
So replace all @tableh with pet_levelstats

That probably fixes all the errors.. hmm
read more here: http://stackoverflow.com/questions/2838490/table-name-as-variable

Marko · Nov 21, 2016

Rochet2 said:
You cannot use @varname for table names. You must explicitly set the table name.
So replace all @tableh with pet_levelstats

That probably fixes all the errors.. hmm
read more here: http://stackoverflow.com/questions/2838490/table-name-as-variable

Like this?

Code:

SET
@percent = 1.20,
@tableh = pet_levelstats; 
DECLARE @v1 INT DEFAULT 10; 
 
WHILE @v1 > 0 
DO
  
INSERT INTO @pet_levelstats (`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 @pet_levelstats
WHERE `level` = (SELECT MAX(`level`) FROM @pet_levelstats);

SET @v1 = @v1 - 1;
END WHILE;

but still does not work..

kay:

Code:

SET
@percent = 1.20,
@tableh = pet_levelstats;
/* SQL Error (1054): Unknown column 'pet_levelstats' in 'field list' */
DECLARE @v1 INT DEFAULT 10;
/* 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 10' at line 1 */
WHILE @v1 > 0 
DO
  
INSERT INTO @pet_levelstats (`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 @pet_levelstats
WHERE `level` = (SELECT MAX(`level`) FROM @pet_levelstats);
/* 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 '@pet_levelstats (`creature_entry`, `level`, `hp`, `mana`, `armor`, `str`, `agi`,' at line 4 */
SET @v1 = @v1 - 1;
END WHILE;
/* 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 'END WHILE' at line 1 */
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 4 of 5 queries: 0.000 sec. */

slp13at420 · Nov 21, 2016

yea I am getting the same error.

Code:

[COLOR="#808080"]
 SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END WHILE' at line 1[/COLOR]

that one tho I dunno how to fix lol ...

Marko · Nov 21, 2016

waiting for fix

its too damn tiresome manually making all level, pet, player, xp stats for a fun server.. for a jadecore 5.4.8 that means 165 more levels to 255. for each pet am manually adding one by one lvl stats.. takes too long. not to mention some end at lvl 85 and continue after next few pets pet so i have to search manually to duplicate that row and edit properly each stat..

:smash:

Rochet2 · Nov 21, 2016

I said to replace @tableh with pet_levelstats, not with @pet_levelstats.
Also while you are at it, remove this:

@tableh = pet_levelstats;

from the SET statement at the top

slp13at420 · Nov 21, 2016

30 seconds with the search window for `pet_levelstats` produced this thread:
http://emudevs.com/showthread.php/2890-toolz-2-do-a-255-realm

but that was in the donor section that no longer exists.
here are the links to those tools:

https://github.com/BlackWolfsDen/Toolz/tree/master/CreatureStatGen
https://github.com/BlackWolfsDen/Toolz/tree/master/Pet_LevelStats
https://github.com/BlackWolfsDen/Toolz/tree/master/PlayerStatsGenerator

I went ahead and moved the thread to here since it seems it can be helpful still

:
http://emudevs.com/showthread.php/2890-toolz-2-do-a-255-realm

slp13at420 · Nov 21, 2016

here is what I did to just troubleshoot:

Code:

[COLOR="#808080"]
-- Template released by Leifus of EmuDevs.com
-- \o/ Tnx Leifus
-- http://emudevs.com/showthread.php/5604-SQL-routine-to-create-level-stats

[COLOR="#DAA520"]BEGIN[/COLOR]

[COLOR="#DAA520"]SET[/COLOR]

-- @percent = 1.10, /* increase 10% from last level */
-- @tableh = pet_levelstats; /* Table you are going to use */

[COLOR="#DAA520"]DECLARE @v1 INT DEFAULT 105;[/COLOR] /* How many times you want it to run*/
 
 [COLOR="#DAA520"]WHILE (@v1 > 0) DO[/COLOR]
  
 /* 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);
*/
  [COLOR="#DAA520"]SET @v1 = @v1 - 1;[/COLOR]
  
 [COLOR="#DAA520"]END WHILE;[/COLOR]
 
[COLOR="#DAA520"]END;[/COLOR]
[/COLOR]

yet I get an error with `END WHILE;` even tho I only have 7 active lines of code there lol it looks right when I researched sql `WHILE` loops.

Rochet2 · Nov 21, 2016

Here is the full code you need, it should work on mysql 5.6

Code:

DROP PROCEDURE IF EXISTS create_level_stats;

delimiter //

CREATE PROCEDURE create_level_stats()
BEGIN

DECLARE v1 INT DEFAULT 10; /* How many times you want it to run*/

SET
@percent = 1.10; /* increase 10% from last level */
 
 WHILE v1 > 0 
 DO
  
  INSERT INTO pet_levelstats (`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 pet_levelstats
  WHERE `level` = (SELECT MAX(`level`) FROM pet_levelstats);

  SET v1 = v1 - 1;
 END WHILE;
 
END

//
delimiter ;

call create_level_stats();

DROP PROCEDURE IF EXISTS create_level_stats;

slp13at420 · Nov 21, 2016

aaah that's what I was missing lol create/destry procedure. the examples I found didn't all have that in them so I didn't concern my self with it ...

and yea that works proper now .

Marko · Nov 21, 2016

player_levelstats

Thank you so much guys

I could post sql of a teleporter for jadecore 5.4.8 i managed to adjust from wowsource.. but i doubt anyone here would need that.
OH and yeah important warning for anyone wanting to use this epic sql, remember to change all your datatype of table pet_levelstats (those SMALLINT) to MEDIUMINT, otherwise it will not be able to support those high custom stats

edit:
here is a working query for player_levelstats..

Code:

DROP PROCEDURE IF EXISTS create_level_stats;

delimiter //

CREATE PROCEDURE create_level_stats()
BEGIN

DECLARE v1 INT DEFAULT 10; 

SET
@percent = 1.20;
 
 WHILE v1 > 0 
 DO
  
  INSERT INTO player_levelstats (`race`, `class`, `level`, `str`, `agi`, `sta`, `inte`, `spi`)
  SELECT 
    `race` 
   ,`class` + 1 `level`
   ,`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 player_levelstats
  WHERE `level` = (SELECT MAX(`level`) FROM player_levelstats);

  SET v1 = v1 - 1;
 END WHILE;
 
END

//
delimiter ;

call create_level_stats();

DROP PROCEDURE IF EXISTS create_level_stats;

Ofc i was not satisfied since at lvl 100 some class/races had just 413stamina so i manually edited all level 100 staminas to have at least 714, since some other class/races had like 1600 @ lvl 100

:OhIMember: dont forget to to change all your stats datatype to MEDIUMINT

SQL routine to create level stats

Glorious

Banned

Enthusiast

Glorious

Enthusiast

Founder

Enthusiast

Member

Mad Scientist

Enthusiast

Moderator / Eluna Dev

Enthusiast

Mad Scientist

Enthusiast

Moderator / Eluna Dev

Mad Scientist

Mad Scientist

Moderator / Eluna Dev

Mad Scientist

Enthusiast