Admin: Created page with "{| align="right" | TOC |} Category: Guides for SkyFire ==What is a MySQL Stored Procedure== * A procedure in MySQL is a '''set of queries''' that will be run once pr..."

2013-01-21T21:53:27Z

Created page with "{| align="right" | __TOC__ |} Category: Guides for SkyFire ==What is a MySQL Stored Procedure== * A procedure in MySQL is a '''set of queries''' that will be run once pr..."

New page

{| align="right"
| __TOC__
|}
[[Category: Guides for SkyFire]]

==What is a MySQL Stored Procedure==

* A procedure in MySQL is a '''set of queries''' that will be run once procedures is called.

* Procedures are objects '''stored inside the database''' and once created, they’re always there for use.

* Procedures and functions in MySQL are called '''stored routines''', because they’re meant to ease often repeated actions.

* See TDB documentation on how to use procedures: [http://docs.google.com/present/view?id=dc98z2zz_58dgdphpgd Stored Procedures for Users]

* See TDB project documentation: [http://docs.google.com/present/view?id=dc98z2zz_5pgwcbpdw Stored Procedures]

* See more information at: [http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html MySQL Reference Manual]

==MySQL Stores Procedures on Trinity==
Since updatepack 27 we’re implementing stored MySQL procedures to be used for fix submissions.
MySQL stored procedures are expected to be used in most of submited fixes on TDB forums once the project's fully established.

===Error-Handling Procedure===
Error handlers are used for data validation inside of other procedures.
They consist of regular MySQL queries and logical operations such as IF or CASE.
They validate data by either proving its existence in the database or making sure data is within proper ranges and types, etc. If this fails – the procedure raises an error, failing the procedure call.
As a standard, these procedures have names starting with '''''sp_tdb_Check*'''''.

====`sp_CheckNpcEntry`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT || Entry of the npc to check
|}

<var>Description:</var> Error handling for TDB procedure: check if npc of provided ID exists in database

<var>Dependencies:</var> none

<var>Tables Affected:</var> [[Creature_template_tc2|creature_template]]

<pre>CALL `sp_CheckNpcEntry`(257); -- will error out if invalid npc entry (creature_template.entry = 257)</pre>

====`sp_CheckTriggerId`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| trigger_id || IN INT(10) || ID to check against the db for quest objective
|}

<var>Description:</var> Check if provided creature ID is requirement of any quest into database (quest_template)

<var>Dependencies:</var> none

<var>Tables Affected:</var> [[Quest_template_tc2|quest_template]]

<pre>CALL `sp_CheckTriggerId`(257); -- make sure trigger (creature_template.entry = 257) is requirement for a quest</pre>.

====`sp_CheckQuestEntry`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| quest_entry || IN INT(10) || Entry of quest from quest_template
|}

<var>Description:</var> Check if provided quest exists in database (quest_template)

<var>Dependencies:</var> none

<var>Tables Affected:</var> [[Quest_template_tc2|quest_template]]

<pre>CALL `sp_CheckQuestEntry`(9876); -- check if quest with ID 9876 exists in database</pre>.

====`sp_CheckNPCOrGO`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_or_go_entry || IN INT(10) || Entry of creature or gameobject from *_template
|-
| entry_type || IN VARCHAR(10) || must be 'GO' or 'NPC'
|}

<var>Description:</var> Check if npc or gameobject with provided entry exists in database (creature_template or gameobject_template)

<var>Dependencies:</var> none

<var>Tables Affected:</var> [[Gameobject_template_tc2|gameobject_template]], [[Creature_template_tc2|creature_template]]

<pre>CALL `sp_CheckNPCOrGO`(257,'NPC'); -- check if NPC with entry = 257 exists in database</pre>.

====`sp_CheckGobjEntry`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| gameobject_entry || IN INT(10) || Entry of the npc to check
|}

<var>Description:</var> Check if provided gameobject exists in database (table gameobject_template)

<var>Dependencies:</var> none

<var>Tables Affected:</var> [[Gameobject_template_tc2|gameobject_template]], [[Creature_template_tc2|creature_template]]

<pre>CALL `sp_CheckGobjEntry`(175124); -- check if Rookery Egg exists in database, error if not</pre>.

===Utility Procedure===
Utility procedures are typically only used within other, more complex procedures. They tend to help with small pieces of a larger solution. Using utility procedures is usually more complicated than just a basic procedure because there is often cleanup needed after using them (or other special circumstances requiring expert knowledge of the procedure). Use these with care.

====`sp_GetEntryList`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| input || IN TEXT || A comma-delimited list of entries to be split and inserted individually into a temporary table
|}

<var>Description:</var> Utility procedure to split a comma-delimited list into a temporary table to be used outside of the procedure. Drop up the temporary table after using it. USE WITH CARE.

<var>Dependencies:</var> none

<var>Tables Affected:</var> [[#`tdb_entry_list`|tdb_entry_list]]

<pre>CALL `sp_GetEntryList`('1,2,3,4,5,6'); -- creates a temp table with 6 rows containing the values 1-6 respectively
SELECT * FROM `tdb_entry_list`; -- use this table as a source for other actions
DROP TEMPORARY TABLE `tdb_entry_list`; -- always drop the table when finished!</pre>

====`sp_ReGuid`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| new_base_guid || IN INT(10) || All creature guids will be re-numbered with the first guid being this number
|}

<var>Description:</var> Will re-number all existing guids in creature table starting with a provided value

<var>Dependencies:</var> `sp_ReGuidAlterTables`

<var>Tables Affected:</var> Any with creature.guid value in use

<pre>CALL `sp_ReGuid`(1000); -- will renumber all existing guids in creature table starting with 1000 as initial</pre>

===Basic Procedure===
Basic procedures are simple, concise, and only accommodate one piece of functionality. These differ from [[#Utility Procedure | Utility Procedures]] in that they can be used by themselves safely and easily without knowledge of any other procedures (outside of [[#Error-Handling Procedure | Error-Handling Procedures]]). They do one thing, and only one thing.

====`sp_TriggerSettings`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT || Entry of the npc for whom template is updated
|}

<var>Description:</var> Sets NPC as a trigger (disable movements, ignore aggro, and disable targetting)

<var>Dependencies:</var> none

<var>Tables Affected:</var> [[Creature_tc2 | creature]], [[Creature_template_tc2 | creature_template]]

<pre>CALL `sp_tdb_TriggerSettings`(257); -- sets npc having `creature_template`.`entry` = 257 to act as trigger</pre>

====`sp_SpellScriptTarget`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| spell_ID || IN INT || ID of spell we want to set target for
|-
| target_type || IN VARCHAR(10) || Choose from ('GO','NPC','DEAD_NPC','MINION')
|-
| spell_target_entry || IN INT(11) || ID of creature or gameobject
|}

<var>Description:</var> Sets target requirement for spellcast

<var>Dependencies:</var> none

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]], [[Spell_script_target | spell_script_target]]

<pre>CALL `sp_SpellScriptTarget`(4444,'NPC',257); -- allows spell 4444 to be cast only on living creature with `creature_template`.`entry` = 257</pre>

====`sp_KillQuestgiver`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry`
|}

<var>Description:</var> Update creature to appear death but still react to eAI / give or take quests

<var>Dependencies:</var> [[#`sp_CheckNpcEntry`|sp_CheckNpcEntry]]

<var>Tables Affected:</var> [[Creature_addon_tc2 | creature_template_addon]], [[Creature_tc2 | creature]]

<pre>CALL `TDB_sp_KillQuestgiver`(257); -- Makes creature with entry 257 appear dead but still albe to give / take quests or react to spellhits</pre>

====`sp_MakeAttackable`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry` who should be made attackable
|}

<var>Description:</var> Update npc to be attackable

<var>Dependencies:</var> [[#`sp_CheckNpcEntry`|sp_CheckNpcEntry]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]]

<pre>CALL `sp_MakeAttackable`(257); -- enables attacking for NPC with ID 257 (creature_template.entry)</pre>

====`sp_IgnoreAggro`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry` who should be made to ignore aggro
|}

<var>Description:</var> Update npc to be ignore aggro

<var>Dependencies:</var> [[#`sp_CheckNpcEntry`|sp_CheckNpcEntry]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]]

<pre>CALL `sp_IgnoreAggro`(257); -- makes NPC with ID 257 ignore aggro</pre>

====`sp_MakeLootable`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry` who should be made lootable
|}

<var>Description:</var> Update npc to become lootable

<var>Dependencies:</var> [[#`sp_CheckNpcEntry`|sp_CheckNpcEntry]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]]

<pre>CALL `sp_MakeLootable`(257); -- makes NPC with ID 257 lootable</pre>

====`sp_SetFaction`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry` whose faction is to be changed
|-
| faction_A || IN INT(10) || Alliance faction to set
|-
| faction_H || IN INT(10) || Horde faction to set
|}

<var>Description:</var> Update npc's faction for both alliance and horde

<var>Dependencies:</var> [[#`sp_CheckNpcEntry`|sp_CheckNpcEntry]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]]

<pre> CALL `sp_SetFaction`(257,7,7); -- sets faction to 7 for NPC with ID 257 (Kobold Worker)</pre>

====`sp_SetSelectable`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry` who should be made selectable
|}

<var>Description:</var> Update npc to become selectable

<var>Dependencies:</var> [[#`sp_CheckNpcEntry`|sp_CheckNpcEntry]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]]

<pre>CALL `sp_SetSelectable`(257); -- makes NPC with ID 257 selectable</pre>

====`sp_SetQuestlevel`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| quest_entry || IN INT(10) || ID of a quest from quest_template
|-
| quest_level || IN INT(10) || New MinLevel value for the selected quest
|}

<var>Description:</var> Update quest to provided level. Only for seasonal quests where levels are not correct from WDB.

<var>Dependencies:</var> [[#`sp_CheckQuestEntry`|sp_CheckQuestEntry]]

<var>Tables Affected:</var> [[Quest_template_tc2 | quest_template]]

<pre> CALL `sp_SetQuestlevel`(11335,30) - sets MinLevel of quest ID 11335 (Call to Arms: Arathi Basin) to 30</pre>

====`sp_SetNotSelectable`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry` who should be not selectable
|}

<var>Description:</var> Update npc to become not selectable

<var>Dependencies:</var> [[#`sp_CheckNpcEntry`|sp_CheckNpcEntry]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]]

<pre>CALL `sp_SetNotSelectable`(257); -- makes NPC with ID 257 not selectable</pre>

====`sp_MakeNotLootable`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry` who should not be lootable
|}

<var>Description:</var> Update npc to become not lootable

<var>Dependencies:</var> [[#`sp_CheckNpcEntry`|sp_CheckNpcEntry]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]]

<pre>CALL `sp_MakeNotLootable`(257); -- makes creature of ID 257 (Kobold Worker) not lootable</pre>

====`sp_NotIgnoreAggro`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry` who should aggro normally
|}

<var>Description:</var> Update npc to aggro normally

<var>Dependencies:</var> [[#`sp_CheckNpcEntry`|sp_CheckNpcEntry]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]]

<pre>CALL `sp_NotIgnoreAggro`(257); -- makes creature of ID 257 (Kobold Worker) stop ignoring aggro</pre>

====`sp_MakeNotAttackable`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry` who should be unattackable
|}

<var>Description:</var> Update npc to become unattackable

<var>Dependencies:</var> [[#`sp_CheckNpcEntry`|sp_CheckNpcEntry]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]]

<pre>CALL `sp_MakeNotAttackable`(257); -- disables attacking of creature with ID 257 (creature_template.entry - Kobold Worker)</pre>

====`sp_GetLootIdForChest`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| go_id || IN MEDIUMINT(6) || ID of the gameobject whose loot id is to be gathered
|-
| go_loot_id || OUT INT(10) || variable to store the retrieved value in
|}

<var>Description:</var> Get the loot ID for a specified gameobject (data1 field). Must be a chest (type=3).

<var>Dependencies:</var> [[#`sp_CheckGobjEntry`|sp_CheckGobjEntry]]

<var>Tables Affected:</var> [[Gameobject_template_tc2 | gameobject_template]]

<pre>CALL `sp_GetLootIdForChest`(194200,@Test); -- get the loot id for g194200 (Rare Cache of Winter) and put it in a variable
SELECT * FROM `gameobject_loot_template` WHERE `entry`=@Test; -- use the variable containing the lootid for a fix</pre>

====`sp_get_ref_id`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| ref_type || IN VARCHAR(10) || Must be one of the following: 'SKIN', 'ITEM', 'FISH', 'MILL', 'RAID_GOBJ', 'MINE', 'PROSPECT', 'WORLD', 'RAID_CRE', 'DUNGEON' or 'PICKPOCKET'
|-
| reference || OUT MEDIUMINT(5) || variable to store the calculated reference id
|}

<var>Description:</var> Get a generated loot reference id based on the type of loot its to be used for

<var>Dependencies:</var> none

<var>Tables Affected:</var> [[Loot_template_tc2 | reference_loot_template]]

<pre>CALL `sp_get_ref_id`('RAID_CRE',@Test); -- store the next available reference ID in the range for raid creatures in a variable
SELECT @Test; -- use the variable however we like in a fix</pre>

====`sp_SetLootId`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || Entry of the npc whose lootid you would like to set
|-
| loot_id || IN MEDIUMINT(5) || NULLABLE. If provided, set the specified NPC's lootid to this value. If NULL, NPC uses its own entry.
|}

<var>Description:</var> Sets the loot id of a specified NPC

<var>Dependencies:</var> [[#`sp_CheckNpcEntry`|sp_CheckNpcEntry]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]]

<pre>CALL `sp_SetLootId`(10184,NULL); -- sets the lootid of c10184 (Onyxia) to its own entry number</pre>

===Average Procedure===
Average level procedures are significantly more complex than [[#Basic Procedure | Basic Procedures]]. They involve multiple tables, multiple procedure dependencies, and frequently perform more than one action. They do, however, still exist for often-used functionality. These types of procedures tend to mask complex behavior using only a few parameters.

====`sp_SetLootIdByList`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| entry_list || IN TEXT || Entry of the npc whose lootid you would like to set
|-
| loot_id || IN INT(10) || NULLABLE. If provided, set the specified NPC's lootid to this value. If NULL, NPC uses its own entry.
|}

<var>Description:</var> Sets the loot id of a list of NPCs to either their own entry numbers or a specified value

<var>Dependencies:</var> [[#`sp_GetEntryList` | sp_GetEntryList]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]], [[#`tdb_entry_list` | tdb_entry_list]]

<pre>CALL `sp_SetLootIdByList` ('1,2,3,4,5','99999'); -- sets the loot id of 5 specified npcs to 99999</pre>

====`sp_QuestRelations`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_or_go_entry || IN INT(10) || ID of NPC from `creature_template`.`entry` OR GameObject from `gameobject_template`
|-
| switch_give_take || IN VARCHAR(10) || Must be either 'QGIVER', 'QTAKER', 'BOTH' - defines if NPC / GO will only give quest / take quest / do both
|-
| quest_entry || IN INT(10) || Entry of quest from quest_template
|-
| entry_type || IN VARCHAR(10) || Either 'NPC' or 'GO' based on the type of entry given
|}

<var>Description:</var> Sets the loot id of a list of NPCs to either their own entry numbers or a specified value

<var>Dependencies:</var> [[#`sp_CheckNPCOrGO` | sp_CheckNPCOrGO]], [[#`sp_CheckQuestEntry` | sp_CheckQuestEntry]]

<var>Tables Affected:</var> [[Creature_questrelation_tc2 | creature_questionrelation]], [[Creature_involvedrelation_tc2 | creature_involvedrelation]], [[Gameobject_questrelation_tc2 | gameobject_questionrelation]], [[Gameobject_involvedrelation_tc2 | gameobject_involvedrelation]]

<pre>CALL `sp_SetLootIdByList`('1,2,3,4,5','99999'); -- sets the loot id of 5 specified npcs to 99999</pre>

===Advanced Procedure===
Advanced procedures are used rarely. They involved many tables, procedures, and have many dependencies and clean up measures required. They are to be used with great care as they will often take a significant amount of time to execute as well. Not for beginners, use any of these with care.

''No procedures accepted.''

===Event AI Procedures===
These procedures are specific to ScriptDev2-based content, namely the Event AI tables. All eAI procedures relate to the same tables, which are described in the [[Event_AI | Event AI Guide]].

====`sp_eai_selectID`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| creature_entry || IN INT(10) || Creature entry to check against
|-
| event_id || OUT INT(10) || Best EventAI ID to associate with the specified creature
|}

<var>Description:</var> Check if eAI exists and get best id for new entries. To be used inside other eAI procs only.

<var>Degree:</var> [[#Utility Procedure | Utility]]

<var>Dependencies:</var> none

<var>Tables Affected:</var> [[Creature_ai_scripts_tc2 | creature_ai_scripts]]

<pre>CALL `sp_eai_selectID`(8956,@eventID); -- gets an appropriate eAI event id for c8956 (Angerclaw Bear) and places it in the @eventID variable</pre>

====`sp_eai_KillCreditOnSpellhit`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry`
|-
| spell_id || IN INT(10) || ID of spell we want to set target for
|-
| trigger_id || IN INT(10) || ID of trigger NPC that needs to be killed for quest objective
|-
| despawn_time || IN INT(10) || time (milliseconds) before NPC despawns
|}

<var>Description:</var> Perform eAI script for NPC to give credit on spellhit

<var>Degree:</var> [[#Average Procedure | Average]]

<var>Dependencies:</var> [[#`sp_CheckNpcEntry` | sp_CheckNpcEntry]], [[#`sp_CheckTriggerId` | sp_CheckTriggerId]], [[#`sp_eai_selectID` | sp_eai_selectID]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]], [[Creature_ai_scripts_tc2 | creature_ai_scripts]]

<pre>-- Creature of ID 257 when hit with spell of ID 4444 will give credit for killing NPC of ID 1235 and will then despawn after 10 seconds
CALL `sp_tdb_eai_KillCreditOnSpellhit`(257,4444,1235,10000); </pre>

====`sp_eai_CastSpellOnSpawn`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry`
|-
| spell_id || IN INT(6) || ID of spell we want to set target for
|}

<var>Description:</var> Perform eAI script for NPC to cast spell on self upon spawn

<var>Degree:</var> [[#Average Procedure | Average]]

<var>Dependencies:</var> [[#`sp_CheckNpcEntry` | sp_CheckNpcEntry]], [[#`sp_eai_selectID` | sp_eai_selectID]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]], [[Creature_ai_scripts_tc2 | creature_ai_scripts]]

<pre>CALL `sp_eai_CastSpellOnSpawn`(257,4444); -- Creature of ID 257 (Kobold Worker) will cast spell of ID 4444 on self when spawned</pre>

====`sp_eai_KillCreditOnDeath`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry`
|-
| trigger_id || IN INT(10) || ID of trigger NPC that needs to be killed for quest objective
|}

<var>Description:</var> Create eAI script for NPC to give credit on death

<var>Degree:</var> [[#Average Procedure | Average]]

<var>Dependencies:</var> [[#`sp_CheckNpcEntry` | sp_CheckNpcEntry]], [[#`sp_eai_selectID` | sp_eai_selectID]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]], [[Creature_ai_scripts_tc2 | creature_ai_scripts]]

<pre>CALL `sp_eai_KillCreditOnDeath`(46,257); -- NPC of ID 46 (Murloc Forager) when killed will give credit for killing NPC with ID 257 (Kobold Worker)</pre>

====`sp_eAI_SpawnOnSpellhit`====
<var>Parameters:</var>
{| border=1 cellpadding=3 cellspacing=0
! Name !! Type !! Description
|-
| npc_entry || IN INT(10) || ID of NPC from `creature_template`.`entry`
|-
| spell_id || IN INT(10) || ID of spell we want to set target for
|-
| spawn_id || IN INT(10) || NPC that will be spawned at current location of the provided npc_entry
|-
| despawn_time || IN INT(10) || time (milliseconds) before summoned NPC despawns
|}

<var>Description:</var> Create eAI script for NPC to give credit on death

<var>Degree:</var> [[#Average Procedure | Average]]

<var>Dependencies:</var> [[#`sp_CheckNpcEntry` | sp_CheckNpcEntry]], [[#`sp_eai_selectID` | sp_eai_selectID]]

<var>Tables Affected:</var> [[Creature_template_tc2 | creature_template]], [[Creature_ai_scripts_tc2 | creature_ai_scripts]]

<pre>-- NPC of id 1234 will summon NPC of id 1235 upon being cast spell 4444. NPC 1235 will despawn after 100s
CALL `sp_eAI_SpawnOnSpellhit`(1234,4444,1235,100000);</pre>

==Temporary Tables==
Temporary tables are a way to store complex data that needs to be used in more than one place but should not be stored permanently. For TDB procedures, we will use them to pass data from one procedure call to another. This method is clean but often requires knowledge of the temporary table(s) being used and how to clean them up properly. Remember: if you use a procedure that uses a temporary table, make sure you drop that table afterwards.

===`tdb_entry_list`===
<big>'''Structure'''</big>
{| border="1"
|'''Field'''
|'''Type'''
|'''Attributes'''
|'''Key'''
|'''Null'''
|'''Default'''
|'''Extra'''
|'''Comment'''
|-
|[[#entry|entry]]
|int
|unsigned
|
|NO
|0
|
|any entry
|}

<big>'''Description of the fields'''</big>
====entry====
Any entry, used by multiple tables

==Examples==
Here are some raw views at what the stored procedures actually look like. We hope you can use these to better understand what, exactly, a stored procure is (and maybe, use these as inspiration to write your own!). Please refer to the PDF manuals at the [[#What is a MySQL Stored Procedure | top of this page]] for more information on stored procedures and how they are used here at TDB.

===sp_CheckNpcEntry===
<pre>DELIMITER //
CREATE PROCEDURE `sp_CheckNpcEntry` (IN creature_entry INT)
BEGIN
/**
* DEGREE: UTILITY
* TABLES AFFECTED: creature_template
* PROCS USED: none
*
* Error handling for TDB procedure: check if creature of provided ID exists in database
*
* creature_entry - Entry of the npc to check
*
* CALL `sp_CheckNpcEntry` (257); -- will error out if invalid npc entry (creature_template.entry = 257)
*/
DECLARE Check_entry INT;
SET Check_entry = (SELECT COUNT(entry) FROM `creature_template` WHERE `entry`= creature_entry);

IF Check_entry = 0 THEN
CALL INVALID_CREATURE_ENTRY();
ELSE
SET Check_entry = 0;
END IF;
END//

DELIMITER ;</pre>

===sp_CheckNPCOrGO===
<pre>DELIMITER //
CREATE PROCEDURE `sp_CheckNPCOrGO`(IN npc_or_go_entry INT(10), IN entry_type VARCHAR(10))
BEGIN
/**
* DEGREE: ERROR HANDLER
* TABLES AFFECTED: creature_template, gameobject_template
* PROCS USED: none
*
* npc_or_go_entry - entry of creature or gameobject from *_template
* entry_type: ["GO"|"NPC"]
*
* ex: CALL `sp_CheckNPCOrGO` (257, "NPC"); -- check if NPC with entry = 257 exists in database
*/
DECLARE Check_entry INT;
CASE entry_type
WHEN "NPC" THEN
SET Check_entry = (SELECT COUNT(`entry`) FROM `creature_template` WHERE `entry`=npc_or_go_entry);
WHEN "GO" THEN
SET Check_entry = (SELECT COUNT(`entry`) FROM `gameobject_template` WHERE `entry`=npc_or_go_entry);
ELSE
CALL INCORRECT_ENTRY_TYPE();
END CASE;
IF Check_entry=0 THEN
CALL INCORRECT_CREATURE_OR_GO_ID();
END IF;
END //

DELIMITER ;</pre>

===sp_MakeNotLootable===
<pre>DELIMITER //
CREATE PROCEDURE `sp_MakeNotLootable`(IN npc_entry INT(10))
BEGIN
/**
* DEGREE: BASIC
* TABLES AFFECTED: creature_template
* PROCS USED: sp_CheckNpcEntry
*
* Update creature to become not lootable
*
* creature_entry - ID of NPC from `creature_template`.`entry`
*
* ex: CALL `sp_MakeNotLootable` (257); -- makes creature of ID 257 (Kobold Worker) not lootable
*/
CALL `sp_CheckNpcEntry` (npc_entry);
UPDATE `creature_template` SET `dynamicflags`=`dynamicflags`&~1 WHERE `entry`= npc_entry;
END //

DELIMITER ;</pre>

===sp_SetLootIdByList===
<pre>DELIMITER //
CREATE PROCEDURE `sp_SetLootIdByList` (IN entryList TEXT,IN lootID MEDIUMINT(5))
BEGIN
/**
* DEGREE: AVERAGE
* TABLES AFFECTED: creature_template, tdb_entry_list (temp)
* PROCS USED: sp_GetEntryList
*
* Sets the loot id of a specified NPC
*
* npcEntry - Entry of the npc whose lootid you would like to set
* loot - NULLABLE. If provided, set the specified NPC's lootid to this value. If NULL, NPC uses its own entry.
*
* ex: CALL `sp_SetLootIdByList` ('1,2,3,4,5','99999'); -- sets the loot id of 5 specified npcs to 99999
*/
CALL `sp_GetEntryList` (entryList);
UPDATE `creature_template` SET `lootid`=IFNULL(lootID,`entry`) WHERE `entry` IN (SELECT DISTINCT * FROM `tdb_entry_list`);

DROP TEMPORARY TABLE `tdb_entry_list`; -- dont forget the cleanup!
END//

DELIMITER ;</pre>

← Older revision		Revision as of 16:55, 21 January 2013
Line 18:		Line 18:
	* See more information at: [http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html MySQL Reference Manual]		* See more information at: [http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html MySQL Reference Manual]

	==MySQL Stores Procedures on ~~Trinity~~==		==MySQL Stores Procedures on SkyFire==
	~~Since updatepack 27 we’re implementing stored MySQL procedures to be used for fix submissions.~~		MySQL stored procedures are expected to be used in most of submitted fixes on SkyFire forums once the project's fully established.
	MySQL stored procedures are expected to be used in most of ~~submited~~ fixes on ~~TDB~~ forums once the project's fully established.

	===Error-Handling Procedure===		===Error-Handling Procedure===

How-to:MySQLStoredProcedures - Revision history

Admin: /* MySQL Stores Procedures on Trinity */

Admin: Created page with "{| align="right" | TOC |} Category: Guides for SkyFire ==What is a MySQL Stored Procedure== * A procedure in MySQL is a '''set of queries''' that will be run once pr..."

How-to:MySQLStoredProcedures - Revision history

Admin: /* MySQL Stores Procedures on Trinity */

Admin: Created page with "{| align="right" | __TOC__ |} Category: Guides for SkyFire ==What is a MySQL Stored Procedure== * A procedure in MySQL is a '''set of queries''' that will be run once pr..."

Admin: Created page with "{| align="right" | TOC |} Category: Guides for SkyFire ==What is a MySQL Stored Procedure== * A procedure in MySQL is a '''set of queries''' that will be run once pr..."