Stored Procedure for Rochet2’s DressNPCs Patch

by Valkryst

I expect that those who use the procedure to have knowledge of SQL/MySQL and know how to use stored procedures. The query can be used by anyone. If you have no idea how to use it, but you're using Rochet2'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:

    
        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` (`CreatureID`, `ID`, `ItemID1`, `ItemID2`, `ItemID3`)
        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.ItemID1 AS mainHand,
        creature_equip_template.ItemID2 AS offHand,
        creature_equip_template.ItemID3 AS ranged
        FROM creature_template_outfits JOIN creature_equip_template ON creature_template_outfits.entry = creature_equip_template.CreatureID WHERE creature_template_outfits.entry = entry;
        END$$

        DELIMITER ;
    

Here's a query to use the stored procedure.

    
        /*
        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
        );