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 ment to ease often repeated actions.

See TDB documentation on how to use procedures: Stored Procedures for Users

See TDB project documentation: Stored Procedures

See more information at: 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`

Parameters:

Name	Type	Description

Name	Type	Description
npc_entry	IN INT	Entry of the npc to check

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

Dependencies: none

Tables Affected: creature_template

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

`sp_CheckTriggerId`

Parameters:

Name	Type	Description

Name	Type	Description
trigger_id	IN INT(10)	ID to check against the db for quest objective

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

Dependencies: none

Tables Affected: quest_template

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

.

`sp_CheckQuestEntry`

Parameters:

Name	Type	Description

Name	Type	Description
quest_entry	IN INT(10)	Entry of quest from quest_template

Description: Check if provided quest exists in database (quest_template)

Dependencies: none

Tables Affected: quest_template

CALL `sp_CheckQuestEntry`(9876); -- check if quest with ID 9876 exists in database

.

`sp_CheckNPCOrGO`

Parameters:

Name	Type	Description

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'

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

Dependencies: none

Tables Affected: gameobject_template, creature_template

CALL `sp_CheckNPCOrGO`(257,'NPC'); -- check if NPC with entry = 257 exists in database

.

`sp_CheckGobjEntry`

Parameters:

Name	Type	Description

Name	Type	Description
gameobject_entry	IN INT(10)	Entry of the npc to check

Description: Check if provided gameobject exists in database (table gameobject_template)

Dependencies: none

Tables Affected: gameobject_template, creature_template

CALL `sp_CheckGobjEntry`(175124); -- check if Rookery Egg exists in database, error if not

.

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`

Parameters:

Name	Type	Description

Name	Type	Description
input	IN TEXT	A comma-delimited list of entries to be split and inserted individually into a temporary table

Description: 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.

Dependencies: none

Tables Affected: tdb_entry_list

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!

`sp_ReGuid`

Parameters:

Name	Type	Description

Name	Type	Description
new_base_guid	IN INT(10)	All creature guids will be re-numbered with the first guid being this number

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

Dependencies: `sp_ReGuidAlterTables`

Tables Affected: Any with creature.guid value in use

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

Basic Procedure

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

`sp_TriggerSettings`

Parameters:

Name	Type	Description

Name	Type	Description
npc_entry	IN INT	Entry of the npc for whom template is updated

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

Dependencies: none

Tables Affected: creature, creature_template

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

`sp_SpellScriptTarget`

Parameters:

Name	Type	Description

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

Description: Sets target requirement for spellcast

Dependencies: none

Tables Affected: creature_template, spell_script_target

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

`sp_KillQuestgiver`

Parameters:

Name	Type	Description

Name	Type	Description
npc_entry	IN INT(10)	ID of NPC from `creature_template`.`entry`

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

Dependencies: sp_CheckNpcEntry

Tables Affected: creature_template_addon, creature

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

`sp_MakeAttackable`

Parameters:

Name	Type	Description

Name	Type	Description
npc_entry	IN INT(10)	ID of NPC from `creature_template`.`entry` who should be made attackable

Description: Update npc to be attackable

Dependencies: sp_CheckNpcEntry

Tables Affected: creature_template

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

`sp_IgnoreAggro`

Parameters:

Name	Type	Description

Name	Type	Description
npc_entry	IN INT(10)	ID of NPC from `creature_template`.`entry` who should be made to ignore aggro

Description: Update npc to be ignore aggro

Dependencies: sp_CheckNpcEntry

Tables Affected: creature_template

CALL `sp_IgnoreAggro`(257); -- makes NPC with ID 257 ignore aggro

`sp_MakeLootable`

Parameters:

Name	Type	Description

Name	Type	Description
npc_entry	IN INT(10)	ID of NPC from `creature_template`.`entry` who should be made lootable

Description: Update npc to become lootable

Dependencies: sp_CheckNpcEntry

Tables Affected: creature_template

CALL `sp_MakeLootable`(257); -- makes NPC with ID 257 lootable

`sp_SetFaction`

Parameters:

Name	Type	Description

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

Description: Update npc's faction for both alliance and horde

Dependencies: sp_CheckNpcEntry

Tables Affected: creature_template

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

`sp_SetSelectable`

Parameters:

Name	Type	Description

Name	Type	Description
npc_entry	IN INT(10)	ID of NPC from `creature_template`.`entry` who should be made selectable

Description: Update npc to become selectable

Dependencies: sp_CheckNpcEntry

Tables Affected: creature_template

CALL `sp_SetSelectable`(257); -- makes NPC with ID 257 selectable

`sp_SetQuestlevel`

Parameters:

Name	Type	Description

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

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

Dependencies: sp_CheckQuestEntry

Tables Affected: quest_template

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

`sp_SetNotSelectable`

Parameters:

Name	Type	Description

Name	Type	Description
npc_entry	IN INT(10)	ID of NPC from `creature_template`.`entry` who should be not selectable

Description: Update npc to become not selectable

Dependencies: sp_CheckNpcEntry

Tables Affected: creature_template

CALL `sp_SetNotSelectable`(257); -- makes NPC with ID 257 not selectable

`sp_MakeNotLootable`

Parameters:

Name	Type	Description

Name	Type	Description
npc_entry	IN INT(10)	ID of NPC from `creature_template`.`entry` who should not be lootable

Description: Update npc to become not lootable

Dependencies: sp_CheckNpcEntry

Tables Affected: creature_template

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

`sp_NotIgnoreAggro`

Parameters:

Name	Type	Description

Name	Type	Description
npc_entry	IN INT(10)	ID of NPC from `creature_template`.`entry` who should aggro normally

Description: Update npc to aggro normally

Dependencies: sp_CheckNpcEntry

Tables Affected: creature_template

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

`sp_MakeNotAttackable`

Parameters:

Name	Type	Description

Name	Type	Description
npc_entry	IN INT(10)	ID of NPC from `creature_template`.`entry` who should be unattackable

Description: Update npc to become unattackable

Dependencies: sp_CheckNpcEntry

Tables Affected: creature_template

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

`sp_GetLootIdForChest`

Parameters:

Name	Type	Description

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

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

Dependencies: sp_CheckGobjEntry

Tables Affected: gameobject_template

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

`sp_get_ref_id`

Parameters:

Name	Type	Description

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

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

Dependencies: none

Tables Affected: reference_loot_template

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

`sp_SetLootId`

Parameters:

Name	Type	Description

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.

Description: Sets the loot id of a specified NPC

Dependencies: sp_CheckNpcEntry

Tables Affected: creature_template

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

Average Procedure

Average level procedures are significantly more complex than 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`

Parameters:

Name	Type	Description

Name	Type	Description
entry_list	IN TEXT	Entry of the npc whose lootid you would like to set
loot_id	IN INT(10)

MySQLStoredProcedures

What is a MySQL Stored Procedure

MySQL Stores Procedures on Trinity

Error-Handling Procedure

`sp_CheckNpcEntry`

`sp_CheckTriggerId`

`sp_CheckQuestEntry`

`sp_CheckNPCOrGO`

`sp_CheckGobjEntry`

Utility Procedure

`sp_GetEntryList`

`sp_ReGuid`

Basic Procedure

`sp_TriggerSettings`

`sp_SpellScriptTarget`

`sp_KillQuestgiver`

`sp_MakeAttackable`

`sp_IgnoreAggro`

`sp_MakeLootable`

`sp_SetFaction`

`sp_SetSelectable`

`sp_SetQuestlevel`

`sp_SetNotSelectable`

`sp_MakeNotLootable`

`sp_NotIgnoreAggro`

`sp_MakeNotAttackable`

`sp_GetLootIdForChest`

`sp_get_ref_id`

`sp_SetLootId`

Average Procedure

`sp_SetLootIdByList`