[RELEASE - Trinity] Stored Procedure for Rotchet2's DressNPC

[Valkryst]

Hey,

I've just been studying for one of my exams tomorrow by making a bunch of SQL queries, views, and stored procedures. I expect that those who may use the procedure already have knowledge of SQL/MySQL and know how to use stored procedures. If you have no idea how to use it, but you're using Rotchet2's DressNPCs patch and would like to use the procedure anyway, then just ask what you need and I'll see if I can answer your questions.

The Stored Procedure:

Code: [Select]

DELIMITER $$

CREATE PROCEDURE createNPCModel(
entry INTEGER,
race INTEGER,
gender INTEGER,
skin INTEGER,
face INTEGER,
hair INTEGER,
hairColor INTEGER,
facialHair INTEGER,
head INTEGER,
shoulders INTEGER,
body INTEGER,
chest INTEGER,
waist INTEGER,
legs INTEGER,
feet INTEGER,
wrists INTEGER,
hands INTEGER,
back INTEGER,
tabard INTEGER,
mainHand INTEGER,
offHand INTEGER,
ranged INTEGER
)

BEGIN
INSERT INTO `creature_template_outfits` (`entry`, `race`, `gender`, `skin`, `face`, `hair`, `haircolor`, `facialhair`, `head`, `shoulders`, `body`, `chest`, `waist`, `legs`, `feet`, `wrists`, `hands`, `back`, `tabard`)
VALUES (entry, race, gender, skin, face, hair, hairColor, facialHair, head, shoulders, body, chest, waist, legs, feet, wrists, hands, back, tabard);
INSERT INTO `creature_equip_template` (`entry`, `id`, `itemEntry1`, `itemEntry2`, `itemEntry3`)
VALUES (entry, 1, mainHand, offHand, ranged);

SELECT
creature_template_outfits.entry,
creature_template_outfits.race AS raceID,
creature_template_outfits.gender AS genderID,
creature_template_outfits.skin AS skinID,
creature_template_outfits.face AS faceID,
creature_template_outfits.hair AS hairID,
creature_template_outfits.haircolor AS hairColorID,
creature_template_outfits.facialhair AS facialFeatureID,
creature_template_outfits.head,
creature_template_outfits.shoulders,
creature_template_outfits.body,
creature_template_outfits.chest,
creature_template_outfits.waist,
creature_template_outfits.legs,
creature_template_outfits.feet,
creature_template_outfits.wrists,
creature_template_outfits.hands,
creature_template_outfits.back,
creature_template_outfits.tabard,
creature_equip_template.itemEntry1 AS mainHand,
creature_equip_template.itemEntry2 AS offHand,
creature_equip_template.itemEntry3 AS ranged
FROM creature_template_outfits JOIN creature_equip_template ON creature_template_outfits.entry = creature_equip_template.entry WHERE creature_template_outfits.entry = entry;
END$$

DELIMITER ;

Here's a query to use the stored procedure.

Code: [Select]

/*
You can use either the displayID of the item or the spawnID of the item.
The majority of good looking items will not have a spawnID, so just use
the displayID in almost every case.
When using a displayID, you need to make the displayID negative.
When using a spawnID, you do not make the spawnID negative.

To use this query, just type in the numbers and execute it into the world datbase.
*/

SET
@NPC_ENTRY_ID := putNumberHere,
@NPC_RACE_ID := putNumberHere,
@GENDER := putNumberHere,  -- 0 = male  1 = female
@SKIN_ID := putNumberHere,
@FACE_ID := putNumberHere,
@HAIR_COLOR_ID := putNumberHere, -- Applies to head and facial hair.
@HAIR_ID := putNumberHere,
@FACIAL_FEATURE_ID := putNumberHere, -- Beards, earrings, etc...
@HEAD := putNumberHere,
@SHOULDERS := putNumberHere,
@BODY := putNumberHere, -- This is  for shirts.
@CHEST := putNumberHere,
@WAIST := putNumberHere,
@LEGS := putNumberHere,
@FEET := putNumberHere,
@WRISTS := putNumberHere,
@HANDS := putNumberHere,
@BACK := putNumberHere,
@TABARD := putNumberHere,
@MAIN_HAND := putNumberHere,
@OFF_HAND := putNumberHere,
@RANGED := putNumberHere;


CALL createNPCModel(
@NPC_ENTRY_ID,
@NPC_RACE_ID,
@GENDER,
@SKIN_ID,
@FACE_ID,
@HAIR_ID,
@HAIR_COLOR_ID,
@FACIAL_FEATURE_ID,
@HEAD,
@SHOULDERS,
@BODY,
@CHEST,
@WAIST,
@LEGS,
@FEET,
@WRISTS,
@HANDS,
@BACK,
@TABARD,
@MAIN_HAND,
@OFF_HAND,
@RANGED
);
-----------------------------------------------------------------------------------------------------------------------------------

If you want to stay up-to-date with my latest tutorials or if you just want an easier way to view all of my tutorials and releases in one place then take a look at my blog.

-----------------------------------------------------------------------------------------------------------------------------------

11/May/2014

• Optimized the SQL query.

23/May/2014

• Updates the bonus query.