[HowTo] Easilly add starting items for all chars with MySQL Function

This is intended for people that are not quite familiar with MySQL or don't know how to easilly commit a 'larger' insert query without too much trouble.

What will you need?

- This example uses the MaNGOS Emulator+DB
- A MySQL browser of some sort (I use Navicat)
- Basic MySQL knowledge


What is a function and why should I use it?

A function is basically a sperate 'script' that you can call, while giving 1 or more parameters in some cases.
Why use it? Because using a function beats having to write an insert query for 64 rows of data (as what happens in this example).


The Situation

The table we are inserting data into is world.playercreateinfo_item.
It contains additional items given to certain characters on char creation.
In this particular situation (which this function is designed for) we would want to give one specific item to all new characters created (For example a potion that summons a mount)

This table will require 1 seperate row per race, per class. With 11 races and 11 classes, this will come down to 121 rows of data for 1 single item you want to give to everyone.

So here's the code used to create the function:

Code:

---

CREATE PROCEDURE `global_item`(IN `v_id` int)
BEGIN
                                                        DECLARE v_race INT Default 1;
                                                        DECLARE v_class INT Default 1;
              SET v_race = 1;
              SET v_class = 1;
                                        WHILE v_race <= 11 DO
              WHILE v_class  <= 11 DO
                          INSERT INTO playercreateinfo_item values (v_race, v_class, v_id, 1);
                          SET  v_class = v_class + 1;
              END WHILE;
                                        SET v_race = v_race + 1;
                                        SET v_class = 1;
                                        END WHILE;
END

---

Function Breakdown

Ok so what happens here?
The first line represents the creation of a function, where 'IN `v_id` int' means you require the user to send a parameter for the variable "v_id" upon executing the function.

Then between the BEGIN and END tags is the function itself.
In this particular function you work with 3 variables:
- v_race
- v_class
- v_id

Race id's go from 1 - 11 as well ass class IDs.
v_id represents the itemid you want to give to the players

In this function I decided to use 2 WHILE LOOPS.
Basically it's a loop within a loop.
Upon executing the function, the race id and class id are set to 1.

When the query starts it will fall into the v_race-loop, which on his turn starts the v_class loop.
It will first check if the race id is equal or lower than 11 and if it is, it will continue to the class-loop.
The class-loop will check if the classid is equal or lower than 11 and if it is it will commit an INSERT query using the current variables, inserting your entered itemid to raceid 1 and classid 1.
After that is done, it will increase the class-variable (v_class) by one.
Then it hits the END WHILE, which sends your progress back to the beginning of the class loop.
It will continue to insert data for each class, until you've reached a classid greater than 11.
When that happens it will exit the class-loop and increase the raceid by one and reset the classid to 1.
Then it will hit the END WHILE for the race-loop, which resets it back to the beginning of the race loop, but this time with raceid 2 and classid 1.

This will repeat the 2 loops again, until your raceid has become greater than 11.
When this happens your function will hit the END statement, which ends the function.

Now when you execute this function it will prompt you to enter a value for your v_id variable. When you've done that it will execute the function quickly.

In my case the entire function took 0.016ms to finish.

---

I created this function specifically to easilly add a global custom item for new chars of EVERY race and class.
I'm pretty sure a lot of you guys know how things like this work already, but hopefully some of you learners will find this useful and will learn more about MySQL functions and how to use them.