UiacjTiK

1-- == ================= ==
2-- ==     DROP LIST 27  ==
3-- == ================= ==
4
5-- Prevent query execution stop on '' statement
6-- SET max_error_count=0;
7
8-- Procedures
9DROP PROCEDURE IF EXISTS `sp_CheckNPCOrGO`;
10DROP PROCEDURE IF EXISTS `sp_CheckGobjEntry`;
11DROP PROCEDURE IF EXISTS `sp_CheckNpcEntry`;
12DROP PROCEDURE IF EXISTS `sp_CheckQuestEntry`;
13DROP PROCEDURE IF EXISTS `sp_CheckTriggerId`;
14DROP PROCEDURE IF EXISTS `sp_eai_CastSpellOnSpawn`;
15DROP PROCEDURE IF EXISTS `sp_eai_KillCreditOnDeath`;
16DROP PROCEDURE IF EXISTS `sp_eai_KillCreditOnSpellhit`;
17DROP PROCEDURE IF EXISTS `sp_eai_selectID`;
18DROP PROCEDURE IF EXISTS `sp_GetDifficultyEntry`;
19DROP PROCEDURE IF EXISTS `sp_GetLootIdForChest`;
20DROP PROCEDURE IF EXISTS `sp_GetReferenceId`;
21DROP PROCEDURE IF EXISTS `sp_IgnoreAggro`;
22DROP PROCEDURE IF EXISTS `sp_KillQuestgiver`;
23DROP PROCEDURE IF EXISTS `sp_MakeAttackable`;
24DROP PROCEDURE IF EXISTS `sp_MakeLootable`;
25DROP PROCEDURE IF EXISTS `sp_MakeNotAttackable`;
26DROP PROCEDURE IF EXISTS `sp_MakeNotLootable`;
27DROP PROCEDURE IF EXISTS `sp_NotIgnoreAggro`;
28DROP PROCEDURE IF EXISTS `sp_QuestRelations`;
29DROP PROCEDURE IF EXISTS `sp_SetFaction`;
30DROP PROCEDURE IF EXISTS `sp_SetLootId`;
31DROP PROCEDURE IF EXISTS `sp_SetLootIdByList`;
32DROP PROCEDURE IF EXISTS `sp_SetNotSelectable`;
33DROP PROCEDURE IF EXISTS `sp_SetQuestlevel`;
34DROP PROCEDURE IF EXISTS `sp_SetSelectable`;
35DROP PROCEDURE IF EXISTS `sp_SpellScriptTarget`;
36DROP PROCEDURE IF EXISTS `sp_TriggerSettings`;
37DROP PROCEDURE IF EXISTS `sp_ReGuidAlterTables`;
38DROP PROCEDURE IF EXISTS `sp_ReGuid`;
39DROP PROCEDURE IF EXISTS `sp_eAI_SpawnOnSpellhit`;
40DROP PROCEDURE IF EXISTS `sp_GetEntryList`;
41DROP PROCEDURE IF EXISTS `sp_eAI_TablePhaseMask`;
42DROP PROCEDURE IF EXISTS `sp_eAI_InversePhaseMask`;
43DROP PROCEDURE IF EXISTS `sp_UpdateByList`;
44DROP PROCEDURE IF EXISTS `sp_UpdateDifficultyEntries`;
45DROP PROCEDURE IF EXISTS `sp_UpdateByMap`;
46
47-- == =================== ==
48-- ==   DROP LIST 29      ==
49-- == =================== ==
50
51DROP PROCEDURE IF EXISTS `sp_error_trigger`;
52DROP PROCEDURE IF EXISTS `sp_error_entry`;
53DROP PROCEDURE IF EXISTS `sp_error_guid`;
54DROP PROCEDURE IF EXISTS `sp_set_npc_lootable_bylist`;
55DROP PROCEDURE IF EXISTS `sp_set_npc_lootable`;
56DROP PROCEDURE IF EXISTS `sp_set_npc_civilian_bylist`;
57DROP PROCEDURE IF EXISTS `sp_set_npc_civilian`;
58DROP PROCEDURE IF EXISTS `sp_set_npc_attackable`;
59DROP PROCEDURE IF EXISTS `sp_set_npc_attackable_bylist`;
60DROP PROCEDURE IF EXISTS `sp_set_npc_targetable`;
61DROP PROCEDURE IF EXISTS `sp_set_npc_targetable_bylist`;
62DROP PROCEDURE IF EXISTS `sp_set_npc_aggro`;
63DROP PROCEDURE IF EXISTS `sp_set_npc_aggro_bylist`;
64DROP PROCEDURE IF EXISTS `sp_set_npc_faction`;
65DROP PROCEDURE IF EXISTS `sp_set_npc_faction_bylist`;
66DROP PROCEDURE IF EXISTS `sp_set_npc_selectable`;
67DROP PROCEDURE IF EXISTS `sp_set_npc_selectable_bylist`;
68DROP PROCEDURE IF EXISTS `sp_set_npc_deadquest`;
69DROP PROCEDURE IF EXISTS `sp_set_spell_target`;
70DROP PROCEDURE IF EXISTS `sp_set_npc_trigger`;
71DROP PROCEDURE IF EXISTS `sp_set_npc_trigger_bylist`;
72DROP PROCEDURE IF EXISTS `sp_set_npc_lootid`;
73DROP PROCEDURE IF EXISTS `sp_set_npc_lootid_bylist`;
74DROP PROCEDURE IF EXISTS `sp_set_npc_lootid_bytable`;
75DROP PROCEDURE IF EXISTS `sp_set_npc_path`;
76DROP PROCEDURE IF EXISTS `sp_set_item_money`;
77DROP PROCEDURE IF EXISTS `sp_set_item_money_bylist`;
78DROP PROCEDURE IF EXISTS `sp_set_entry_list`;
79DROP PROCEDURE IF EXISTS `sp_set_quest_previous`;
80DROP PROCEDURE IF EXISTS `sp_set_quest_next`;
81DROP PROCEDURE IF EXISTS `sp_set_quest_level`;
82DROP PROCEDURE IF EXISTS `sp_get_ref_id`;
83DROP PROCEDURE IF EXISTS `sp_get_npc_diffentry`;
84DROP PROCEDURE IF EXISTS `sp_get_go_lootid`;
85DROP PROCEDURE IF EXISTS `sp_eai_inverse_phase_mask`;
86DROP PROCEDURE IF EXISTS `sp_eai_table_phase_mask`;
87DROP PROCEDURE IF EXISTS `sp_eai_select_id`;
88DROP PROCEDURE IF EXISTS `sp_eai_quest_credit_on_spellhit`;
89DROP PROCEDURE IF EXISTS `sp_eai_spawn_spellhit`;
90DROP PROCEDURE IF EXISTS `sp_eai_cast_onspawn`;
91DROP PROCEDURE IF EXISTS `sp_eai_kill_ondeath`;
92DROP PROCEDURE IF EXISTS `sp_dev_re_guid`;
93DROP PROCEDURE IF EXISTS `sp_dev_reguid_alter_tables`;
94DROP PROCEDURE IF EXISTS `sp_dev_cleanup_world`;
95DROP PROCEDURE IF EXISTS `sp_dev_cleanup_loot`;
96DROP PROCEDURE IF EXISTS `sp_dev_cleanup_reference_loot`;
97DROP PROCEDURE IF EXISTS `sp_delete_spell_area`;
98DROP PROCEDURE IF EXISTS `sp_delete_spell_position`;
99DROP PROCEDURE IF EXISTS `sp_delete_spell_position_bylist`;
100DROP PROCEDURE IF EXISTS `sp_delete_spell_target`;
101DROP PROCEDURE IF EXISTS `sp_delete_questgiver`;
102DROP PROCEDURE IF EXISTS `sp_delete_questtaker`;
103DROP PROCEDURE IF EXISTS `sp_delete_spawn`;
104DROP PROCEDURE IF EXISTS `sp_delete_script`;
105DROP PROCEDURE IF EXISTS `sp_delete_script_bylist`;
106DROP PROCEDURE IF EXISTS `sp_delete_npc_trainer`;
107DROP PROCEDURE IF EXISTS `sp_delete_npc_vendor`;
108DROP PROCEDURE IF EXISTS `sp_delete_loot`;
109
110-- == =================== ==
111-- ==   UPDATEPACK 29     ==
112-- == =================== ==
113
114-- == =================== ==
115-- == delete procedures   ==
116-- == =================== ==
117
118DELIMITER //
119
120-- sp_delete_spell_area
121CREATE PROCEDURE `sp_delete_spell_area`(IN spell_id INT(10), IN area_id INT(10))
122BEGIN
123/*
124 * DEGREE: BASIC
125 * TABLES AFFECTED: spell_area
126 * PROCS USED:  none
127 *
128 * spell_id - NULLABLE. If present, deletes using the spell as a qualifier (if null, only uses area)
129 * area_id - NULLABLE. If present, deletes using the area as a qualifier (if null, only uses spell)
130 *
131 * CALL `sp_delete_spell_area`(1000,NULL); -- delete spell_area entry for spell 1000
132 * CALL `sp_delete_spell_area`(NULL,200); -- delete spell_area entry for area 200
133 * CALL `sp_delete_spell_area`(1000,200); -- deletes spell_area entry for spell 1000 and area 200
134 */ 
135	IF spell_id IS NOT NULL OR area_id IS NOT NULL THEN
136		DELETE FROM `spell_area` WHERE `spell`=IFNULL(spell_id,`spell`) AND `area`=IFNULL(area_id,`area`);
137	ELSE
138		CALL MUST_PROVIDE_SPELL_OR_AREA;
139	END IF;
140END //
141	
142-- sp_delete_spell_position
143CREATE PROCEDURE `sp_delete_spell_position`(IN spell_id INT(10))
144BEGIN
145/*
146 * DEGREE: BASIC
147 * TABLES AFFECTED: spell_area
148 * PROCS USED:  none
149 *
150 * CALL `sp_delete_spell_position` (1000); -- delete spell target position entry for spell 1000
151 */
152	DELETE FROM `spell_target_position` WHERE `id`=spell_id;
153END //
154
155-- sp_delete_spell_position_bylist
156CREATE PROCEDURE `sp_delete_spell_position_bylist`(IN spell_list LONGTEXT)
157BEGIN
158/*
159 * DEGREE: BASIC
160 * TABLES AFFECTED: spell_area
161 * PROCS USED:  none
162 *
163 * CALL `sp_delete_spell_position` (1000); -- delete spell target position entry for spell 1000
164 */
165	CALL `sp_set_entry_list` (spell_list,null);
166
167	DELETE FROM `spell_target_position` WHERE `id` IN (SELECT * FROM `tdb_entry_list`);
168
169	DROP TABLE `tdb_entry_list`; -- dont forget the cleanup!
170END //
171
172-- sp_delete_spell_target
173CREATE PROCEDURE `sp_delete_spell_target` (IN spell_id INT(10),IN target_entry INT(10))
174BEGIN
175/*
176 * DEGREE: BASIC
177 * TABLES AFFECTED: spell_script_target
178 * PROCS USED:  none
179 *
180 * spell_id - NULLABLE. If present, the spell is used as a qualifier (if null, only use target)
181 * target_entry - NULLABLE. If present, the target (either gobj or npc) is used as a qualifer (if null, only use spell)
182 *
183 * Remove quest relations for gameobject, example of use:
184 * CALL `sp_delete_spell_target`(1000,NULL); -- deletes spell_script_target for spell 1000
185 * CALL `sp_delete_spell_target`(NULL,200); -- deletes required targets with target entry of creature/gobj id 200
186 * CALL `sp_delete_spell_target`(1000,200); -- deletes spell required target for spell id 1000 and creature/gobj entry of 200 only (safest way to delete spell target)
187 */
188	IF spell_id IS NOT NULL OR target_entry IS NOT NULL THEN
189		DELETE FROM `spell_script_target` WHERE `entry`=IFNULL(spell_id,`entry`) AND `targetEntry`=IFNULL(target_entry,`targetEntry`);
190	ELSE
191		CALL MUST_PROVIDE_SPELL_OR_TARGET;
192	END IF;
193END //
194
195-- sp_delete_questgiver	
196CREATE PROCEDURE `sp_delete_questgiver`(IN qg_type VARCHAR(10),IN qg_entry INT(10), IN quest_entry INT(10))
197BEGIN
198/*
199 * DEGREE: BASIC
200 * TABLES AFFECTED: gameobject_questrelation
201 * PROCS USED:  none
202 *
203 * qg_type - The type of quest giver. the only acceptable values are 'GO' and 'NPC'
204 * qg_entry - NULLABLE. If present, entry of the gobj or npc whose questrelations is used as a qualifier (if null, only the quest_entry is used)
205 * quest_entry - NULLABLE. If present, entry of the quest to use as a qualifier (if null, only the entry is used)
206 *
207 * CALL `sp_delete_questgiver`('GO',1000,NULL); -- deletes all quest given by object id 1000
208 * CALL `sp_delete_questgiver`('GO',NULL,200); -- deletes all go relations to quest 200
209 * CALL `sp_delete_questgiver`('NPC',1000,200); -- deletes where NPC 1000 gives quest 200
210 */
211	IF qg_entry IS NOT NULL OR quest_entry IS NOT NULL THEN
212		CASE UCASE(qg_type)
213			WHEN 'NPC' THEN BEGIN
214				DELETE FROM `creature_questrelation` WHERE `id`=IFNULL(qg_entry,`id`) AND `quest`=IFNULL(quest_entry,`quest`);
215			END;
216			WHEN 'GO' THEN BEGIN
217				DELETE FROM `gameobject_questrelation` WHERE `id`=IFNULL(qg_entry,`id`) AND `quest`=IFNULL(quest_entry,`quest`);
218			END;
219			ELSE CALL INVALID_ENTRY_TYPE;
220		END CASE;
221	ELSE CALL MUST_PROVIDE_ENTRY_OR_QUEST;
222	END IF;
223END //
224
225-- sp_delete_questtaker
226CREATE PROCEDURE `sp_delete_questtaker`(IN qt_type VARCHAR(10),IN qt_entry INT(10), IN quest_entry INT(10))
227BEGIN
228/*
229 * DEGREE: BASIC
230 * TABLES AFFECTED: gameobject_questrelation
231 * PROCS USED:  none
232 *
233 * qt_type - The type of quest taker. the only acceptable values are 'GO' and 'NPC'
234 * qt_entry - NULLABLE. If present, entry of the gobj or npc whose involvedrelations is used as a qualifier (if null, only the quest_entry is used)
235 * quest_entry - NULLABLE. If present, entry of the quest to use as a qualifier (if null, only the entry is used)
236 *
237 * CALL `sp_delete_questtaker`('GO',1000,NULL); -- deletes all quest taken by object id 1000
238 * CALL `sp_delete_questtaker`('GO',NULL,200); -- deletes all go involved gobjs to quest 200
239 * CALL `sp_delete_questtaker`('NPC',1000,200); -- deletes where NPC 1000 takes quest 200
240 */
241	IF qt_entry IS NOT NULL OR quest_entry IS NOT NULL THEN
242		CASE UCASE(qt_type)
243			WHEN 'NPC' THEN BEGIN
244				DELETE FROM `creature_involvedrelation` WHERE `id`=IFNULL(qt_entry,`id`) AND `quest`=IFNULL(quest_entry,`quest`);
245			END;
246			WHEN 'GO' THEN BEGIN
247				DELETE FROM `gameobject_involvedrelation` WHERE `id`=IFNULL(qt_entry,`id`) AND `quest`=IFNULL(quest_entry,`quest`);
248			END;
249			ELSE CALL INVALID_ENTRY_TYPE;
250		END CASE;
251	ELSE CALL MUST_PROVIDE_ENTRY_OR_QUEST;
252	END IF;
253END //
254
255-- sp_delete spawn
256CREATE PROCEDURE `sp_delete_spawn`(IN spawn_type VARCHAR(10),IN spawn_guid INT(10), IN spawn_entry INT(10))
257BEGIN
258/*
259 * DEGREE: BASIC
260 * TABLES AFFECTED: gameobject
261 * PROCS USED:  none
262 *
263 * spawn_type - Type of spawn to delete. The only acceptable values are 'GO' or 'NPC'
264 * spawn_guid - NULLABLE. If present, the guid is used as a qualifier (if null, only use id and game_event_* is not affected)
265 * spawn_entry - NULLABLE. If present, the id is used as a qualifier (if null, only use the guid)
266 *
267 * CALL `sp_delete_spawn` ('GO',20000,NULL); -- deletes gobj of guid 200000
268 * CALL `sp_delete_spawn` ('GO',NULL,10000); -- deletes all gobj spawns of id 10000
269 * CALL `sp_delete_spawn` ('NPC',20000,10000); -- deletes a specific npc spawn where guid is 20000 and id is 10000 (safest way to delete spawn)
270 */
271	IF spawn_guid IS NOT NULL OR spawn_entry IS NOT NULL THEN
272		CASE UCASE(spawn_type)
273			WHEN 'NPC' THEN BEGIN
274				DELETE FROM `creature` WHERE `guid`=IFNULL(spawn_guid,`guid`) AND `id`=IFNULL(spawn_entry,`id`);
275				DELETE FROM `game_event_creature` WHERE `guid`=IFNULL(spawn_guid,-1);
276			END;
277			WHEN 'GO' THEN BEGIN
278				DELETE FROM `gameobject` WHERE `guid`=IFNULL(spawn_guid,`guid`) AND `id`=IFNULL(spawn_entry,`id`);
279				DELETE FROM `game_event_gameobject` WHERE `guid`=IFNULL(spawn_guid,-1);
280			END;
281			ELSE CALL INVALID_SPAWN_TYPE;
282		END CASE;
283	ELSE 
284		CALL MUST_PROVIDE_GUID_OR_ENTRY;
285	END IF;
286END //
287
288-- sp_delete_script
289CREATE PROCEDURE `sp_delete_script`(IN script_type VARCHAR(10), IN script_id INT(10))
290BEGIN
291/*
292 * DEGREE: BASIC
293 * TABLES AFFECTED: Any table with a name like *_scripts
294 * PROCS USED:  none
295 *
296 * script_type - The type of script to delete (deterimines which table is modified). 
297 *             - Only accetable values are 'EAI','GO','GOSSIP','Q_START','Q_END','SPELL', or 'WP'
298 * script_id - ID associcated with the script to be deleted
299 * 
300 * CALL `sp_delete_script`('GO',1000); -- deletes the script of id=1000 from gameobject_scripts
301 */
302	CASE UCASE(script_type)
303		WHEN 'EAI' THEN BEGIN
304			DELETE FROM `creature_ai_scripts` WHERE `id`=script_id;
305		END;
306		WHEN 'GO' THEN BEGIN
307			DELETE FROM `gameobject_scripts` WHERE `id`=script_id;
308		END;
309		WHEN 'GOSSIP' THEN BEGIN
310			DELETE FROM `gossip_scripts` WHERE `id`=script_id;
311		END;
312		WHEN 'Q_START' THEN BEGIN
313			DELETE FROM `quest_start_scripts` WHERE `id`=script_id;
314		END;
315		WHEN 'Q_END' THEN BEGIN
316			DELETE FROM `quest_end_scripts` WHERE `id`=script_id;
317		END;
318		WHEN 'SPELL' THEN BEGIN
319			DELETE FROM `spell_scripts` WHERE `id`=script_id;
320		END;
321		WHEN 'WP' THEN BEGIN
322			DELETE FROM `waypoint_scripts` WHERE `id`=script_id;
323		END;
324		ELSE CALL INVALID_SCRIPT_TYPE;
325	END CASE;
326END //
327
328-- sp_delete_script_bylist
329CREATE PROCEDURE `sp_delete_script_bylist`(IN script_type VARCHAR(10), IN script_id_list LONGTEXT)
330BEGIN
331/*
332 * DEGREE: BASIC
333 * TABLES AFFECTED: Any table with a name like *_scripts
334 * PROCS USED:  none
335 *
336 * script_type - The type of script to delete (deterimines which table is modified). 
337 *             - Only accetable values are 'EAI','GO','GOSSIP','Q_START','Q_END','SPELL', or 'WP'
338 * script_id - ID associcated with the script to be deleted
339 * 
340 * CALL `sp_delete_script`('GO',1000); -- deletes the script of id=1000 from gameobject_scripts
341 */
342	CALL `sp_set_entry_list` (script_id_list,null);
343
344	CASE UCASE(script_type)
345		WHEN 'EAI' THEN BEGIN
346			DELETE FROM `creature_ai_scripts` WHERE `id` IN (SELECT * FROM `tdb_entry_list`);
347		END;
348		WHEN 'GO' THEN BEGIN
349			DELETE FROM `gameobject_scripts` WHERE `id` IN (SELECT * FROM `tdb_entry_list`);
350		END;
351		WHEN 'GOSSIP' THEN BEGIN
352			DELETE FROM `gossip_scripts` WHERE `id` IN (SELECT * FROM `tdb_entry_list`);
353		END;
354		WHEN 'Q_START' THEN BEGIN
355			DELETE FROM `quest_start_scripts` WHERE `id` IN (SELECT * FROM `tdb_entry_list`);
356		END;
357		WHEN 'Q_END' THEN BEGIN
358			DELETE FROM `quest_end_scripts` WHERE `id` IN (SELECT * FROM `tdb_entry_list`);
359		END;
360		WHEN 'SPELL' THEN BEGIN
361			DELETE FROM `spell_scripts` WHERE `id` IN (SELECT * FROM `tdb_entry_list`);
362		END;
363		WHEN 'WP' THEN BEGIN
364			DELETE FROM `waypoint_scripts` WHERE `id` IN (SELECT * FROM `tdb_entry_list`);
365		END;
366		ELSE CALL INVALID_SCRIPT_TYPE;
367	END CASE;
368
369	DROP TABLE `tdb_entry_list`; -- dont forget the cleanup!
370END //
371
372-- sp_delete_npc_trainer
373CREATE PROCEDURE `sp_delete_npc_trainer`(IN npc_entry INT(10), IN spell_id INT(10))
374BEGIN
375/*
376 * DEGREE: BASIC
377 * TABLES AFFECTED: npc_trainer
378 * PROCS USED:  none
379 *
380 * npc_entry - NULLABLE. If present, use entry as a qualifier (if null, only use spell)
381 * spell_id - NULLABLE. If present, use spell as a qualifier (if null, only use entry)
382 *
383 * CALL `sp_delete_npc_trainer`(1000,NULL); -- removes entire trainer list for creature with ID 1000
384 * CALL `sp_delete_npc_trainer`(NULL,200); -- deletes all trainer entries for spell 200
385 * CALL `sp_delete_npc_trainer`(1000,200); -- deletes trainer list for npc 1000 and spell 200
386 */
387	IF spell_id IS NOT NULL OR npc_entry IS NOT NULL THEN
388		DELETE FROM `npc_trainer` WHERE `entry`=IFNULL(npc_entry,`entry`) AND `spell`=IFNULL(spell_id,`spell`);
389	ELSE 
390		CALL MUST_PROVIDE_ENTRY_OR_SPELL;
391	END IF;
392END //
393
394-- sp_delete_npc_vendor
395CREATE PROCEDURE `sp_delete_npc_vendor`(IN npc_entry INT(10), IN item_entry INT(10))
396BEGIN
397/*
398 * DEGREE: BASIC
399 * TABLES AFFECTED: npc_trainer
400 * PROCS USED:  none
401 *
402 * npc_entry - NULLABLE. If present, use entry as a qualifier (if null, only use item)
403 * item_entry - NULLABLE. If present, use item as a qualifier (if null, only use entry)
404 * 
405 * CALL `sp_delete_npc_trainer`(1000,NULL); -- deletes vendor list for NPC 1000
406 * CALL `sp_delete_npc_trainer`(NULL,200); -- deletes vendor entries for item 200
407 * CALL `sp_delete_npc_trainer`(1000,200); -- deletes vendor list for npc 1000 and item 200
408 */
409	IF item_entry IS NOT NULL OR npc_entry IS NOT NULL THEN
410		DELETE FROM `npc_vendor` WHERE `entry`=IFNULL(npc_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
411	ELSE
412		CALL MUST_PROVIDE_ENTRY_OR_ITEM;
413	END IF;
414END //
415
416-- sp_delete_loot (bitchslap to this proc brought to you by denyde)
417CREATE PROCEDURE `sp_delete_loot` (IN loot_type VARCHAR(10),IN loot_entry INT(10),IN item_entry INT(10))
418BEGIN
419/*
420 * DEGREE: BASIC
421 * TABLES AFFECTED: *_loot_template
422 * PROCS USED:  none
423 *
424 * loot_type - Type of loot to delete
425 *           - The only acceptable values are 'FISH','NPC','GO','ITEM','DISENCH','PROSPECT','MILL','PICKPOCKET','SKIN','QUEST', or 'REF'
426 * loot_entry - NULLABLE. If present, use entry as a qualifier in the specified table (if null, only use item)
427 * item_entry - NULLABLE. If present, use item as a qualifier in the specified table (if null, only use entry)
428 *
429 * CALL `sp_delete_loot`('GO',20000,NULL); -- deletes all loot of gobj whose type=3 (chest) and data1 is 20000
430 * CALL `sp_delete_loot`('PROSPECT',NULL,10000); -- deletes all loot of item_template entry 10000 from prospecting loot
431 * CALL `sp_delete_loot`('NPC',20000,10000); -- deletes a loot of an npc whose lootid=20000 and drops item 10000 directly from 
432 */
433	IF loot_entry IS NOT NULL OR item_entry IS NOT NULL THEN
434		CASE UCASE(loot_type)
435			WHEN 'FISH' THEN BEGIN
436				DELETE FROM `fishing_loot_template` WHERE `entry`=IFNULL(loot_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
437			END;
438			WHEN 'NPC' THEN BEGIN
439				DELETE FROM `creature_loot_template` WHERE `entry`=IFNULL(loot_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
440			END;
441			WHEN 'GO' THEN BEGIN
442				DELETE FROM `gameobject_loot_template` WHERE `entry`=IFNUofcLL(loot_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
443			END;
444			WHEN 'ITEM' THEN BEGIN
445				DELETE FROM `item_loot_template` WHERE `entry`=IFNULL(loot_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
446			END;
447			WHEN 'DISENCH' THEN BEGIN
448				DELETE FROM `disenchant_loot_template` WHERE `entry`=IFNULL(loot_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
449			END;
450			WHEN 'PROSPECT' THEN BEGIN
451				DELETE FROM `propspecting_loot_template` WHERE `entry`=IFNULL(loot_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
452			END;
453			WHEN 'MILL' THEN BEGIN
454				DELETE FROM `millling_loot_template` WHERE `entry`=IFNULL(loot_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
455			END;
456			WHEN 'PICKPOCKET' THEN BEGIN
457				DELETE FROM `pickpocketing_loot_template` WHERE `entry`=IFNULL(loot_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
458			END;
459			WHEN 'SKIN' THEN BEGIN
460				DELETE FROM `skinning_loot_template` WHERE `entry`=IFNULL(loot_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
461			END;
462			WHEN 'QUEST' THEN BEGIN
463				DELETE FROM `quest_loot_template` WHERE `entry`=IFNULL(loot_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
464			END;
465			WHEN 'REF' THEN BEGIN
466				IF loot_entry IS NOT NULL THEN
467					DELETE FROM `creature_loot_template` WHERE `mincountOrRef`=-loot_entry;
468					DELETE FROM `disenchant_loot_template` WHERE `mincountOrRef`=-loot_entry;
469					DELETE FROM `fishing_loot_template` WHERE `mincountOrRef`=-loot_entry;
470					DELETE FROM `gameobject_loot_template` WHERE `mincountOrRef`=-loot_entry;
471					DELETE FROM `item_loot_template` WHERE `mincountOrRef`=-loot_entry;
472					DELETE FROM `mail_loot_template` WHERE `mincountOrRef`=-loot_entry;
473					DELETE FROM `milling_loot_template` WHERE `mincountOrRef`=-loot_entry;
474					DELETE FROM `pickpocketing_loot_template` WHERE `mincountOrRef`=-loot_entry;
475					DELETE FROM `prospecting_loot_template` WHERE `mincountOrRef`=-loot_entry;
476					DELETE FROM `skinning_loot_template` WHERE `mincountOrRef`=-loot_entry;
477					DELETE FROM `spell_loot_template` WHERE `mincountOrRef`=-loot_entry;
478				END IF;
479				
480				DELETE FROM `reference_loot_template` WHERE `entry`=IFNULL(loot_entry,`entry`) AND `item`=IFNULL(item_entry,`item`);
481			END;
482			ELSE CALL INVALID_LOOT_TYPE;
483		END CASE;
484	ELSE 
485		CALL MUST_PROVIDE_ENTRY_OR_ITEM;
486	END IF;
487END //
488
489
490-- ------------------ --
491-- EAI procedures     --
492-- ------------------ --
493
494CREATE PROCEDURE `sp_eai_select_id`(IN npc_entry INT(10), OUT event_id INT(10))
495BEGIN
496/**
497 * DEGREE: UTILITY
498 * TABLES AFFECT: creature_ai_scripts
499 * PROCS USERD: none
500 *
501 * Check if eAI exists and add pick best id for new entries
502 * To be used inside other eAI procs only!
503 * 
504 * ex: CALL `sp_eai_select_id` (257); -- selects new creature_ai_scripts.id for NPC entry 257 (Kobold Worker) and deletes old eAI added by procedures 
505 */
506	CALL `sp_error_entry`('NPC',npc_entry);
507	SET event_id = (SELECT MAX(id)+1 FROM `creature_ai_scripts` WHERE `creature_id`=npc_entry);
508    DELETE FROM `creature_ai_scripts` WHERE `creature_id`=npc_entry AND `comment` LIKE "Stored procedures eAI%";
509END //
510
511-- `eai_TablePhaseMask`
512CREATE
513    PROCEDURE `sp_eai_table_phase_mask`()
514    BEGIN
515 /*
516 * DEGREE: UTILITY
517 * TABLES AFFECTED: NONE
518 * PROCS USED: NONE
519 *
520 * THIS PROCEDURE IS AN INTEGRAL PART OF `sp_eai_inverse_phase_mask` PROC. AND HAS NO OTHER USE!!!
521 *
522 */
523CREATE TABLE `phase_mask`(
524`phaseID` INT(2) NOT NULL DEFAULT '0' ,
525`phase_mask` INT(11) UNSIGNED NOT NULL DEFAULT '0' ,
526PRIMARY KEY (`phaseID`));
527INSERT INTO phase_mask VALUES
528(0,1),
529(1,2),
530(2,4),
531(3,8),
532(4,16),
533(5,32),
534(6,64),
535(7,128),
536(8,256),
537(9,512),
538(10,1024),
539(11,2048),
540(12,4096),
541(13,8192),
542(14,16384),
543(15,32768),
544(16,65536),
545(17,131072),
546(18,262144),
547(19,524288),
548(20,1048576),
549(21,2097152),
550(22,4194304),
551(23,8388608),
552(24,16777216),
553(25,33554432),
554(26,67108864),
555(27,134217728),
556(28,268435456),
557(29,536870912),
558(30,1073741824),
559(31,2147483648);
560    END//
561
562CREATE
563    PROCEDURE `sp_eai_inverse_phase_mask`(IN max_phase INT, IN phase_list VARCHAR(255))
564    BEGIN
565/*
566 * DEGREE:UTILITY
567 * TABLES AFFECTED: NONE
568 * PROCS USED: sp_set_entry_list
569 *
570 *
571 * Procedure to select value for field event_inverse_phase_mask in creature_ai_scripts table
572 *
573 * max_phase = number of maximum phase used for creature:
574 * phase_list = list of phase IDs in which event will occur 
575 * example call: CALL `sp_eai_inverse_phase_mask`(3,"2,1");
576 * If creature will should enter phase: 0,1,2,3 - then max_phase value is 3
577 * phase_list assigns in which phases event WILL occur
578 * so if we want NPC using 3 phases (0-3) to take action from eAI while he's in phase 2 OR 3
579 * the call for procedure would be: CALL `sp_eAI_InversePhaseMask`(3,"2,3");
580 *
581 */
582DECLARE max_phase_mask INT;
583DECLARE phase_yes INT;
584DECLARE inverse_phase_mask INT;
585CALL `sp_eai_table_phase_mask`();
586CALL `sp_set_entry_list`(phase_list);
587SET max_phase_mask = (SELECT SUM(phase_mask) FROM phase_mask WHERE phaseID <= max_phase);
588SET phase_yes = (SELECT SUM(phase_mask) FROM phase_mask WHERE phaseID IN (SELECT `value` FROM tdb_entry_list));
589SET inverse_phase_mask = (max_phase_mask - phase_yes);
590DROP TABLE `tdb_entry_list`;
591DROP TABLE `phase_mask`;
592IF inverse_phase_mask < 0 THEN
593SELECT "PhaseID is bigger then maximum phase entered" AS `inverse_phase_mask`;
594ELSE IF phase_list="0" THEN
595SELECT 0 AS `inverse_phase_mask`;
596ELSE
597SELECT inverse_phase_mask;
598END IF;
599END IF;
600    END//
601
602-- ------------------ --
603--  SINGLE RUN PROC   --
604-- ------------------ --
605
606CREATE
607    PROCEDURE `sp_dev_reguid_alter_tables`()
608    BEGIN
609/**
610 * DEGREE: UTILITY
611 * TABLES AFFECT: many
612 * PROCS USERD: none
613 *
614 * THIS PROCEDURE IS A PART OF `sp_dev_re_guid` AND HAS NO OTHER USE!
615 */
616            CREATE TABLE `creature_temp` (
617  `guid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Global Unique Identifier',
618  `id` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Creature Identifier',
619  `map` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Map Identifier',
620  `spawnMask` TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
621  `phaseMask` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '1',
622  `modelid` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
623  `equipment_id` MEDIUMINT(9) NOT NULL DEFAULT '0',
624  `position_x` FLOAT NOT NULL DEFAULT '0',
625  `position_y` FLOAT NOT NULL DEFAULT '0',
626  `position_z` FLOAT NOT NULL DEFAULT '0',
627  `orientation` FLOAT NOT NULL DEFAULT '0',
628  `spawntimesecs` INT(10) UNSIGNED NOT NULL DEFAULT '120',
629  `spawndist` FLOAT NOT NULL DEFAULT '5',
630  `currentwaypoint` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
631  `curhealth` INT(10) UNSIGNED NOT NULL DEFAULT '1',
632  `curmana` INT(10) UNSIGNED NOT NULL DEFAULT '0',
633  `DeathState` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
634  `MovementType` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
635  `old_guid` INT(10) UNSIGNED NOT NULL,
636  PRIMARY KEY (`guid`),
637  KEY `idx_map` (`map`),
638  KEY `idx_id` (`id`)
639) ENGINE=MYISAM AUTO_INCREMENT=250001 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Creature System';
640        ALTER TABLE `creature_addon` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
641        ALTER TABLE `creature_formations` ADD COLUMN `new_guid_leader` INT(10) UNSIGNED NOT NULL;
642        ALTER TABLE `creature_formations` ADD COLUMN `new_guid_member` INT(10) UNSIGNED NOT NULL;
643        ALTER TABLE `creature_linked_respawn` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
644        ALTER TABLE `creature_linked_respawn` ADD COLUMN `new_linked_guid` INT(10) UNSIGNED NOT NULL;
645        ALTER TABLE `game_event_creature` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
646        ALTER TABLE `pool_creature` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
647        ALTER TABLE `game_event_model_equip` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
648        ALTER TABLE `game_event_npc_gossip` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
649        ALTER TABLE `game_event_npc_vendor` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
650        ALTER TABLE `game_event_npcflag` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
651        ALTER TABLE `npc_gossip` ADD COLUMN `new_guid` INT(10) UNSIGNED NOT NULL;
652    END//
653	
654	
655CREATE
656    PROCEDURE `sp_dev_re_guid`(IN new_base_guid INT(10))
657    BEGIN
658/**
659 * DEGREE: UTILITY
660 * TABLES AFFECT: any with creature.guid value in use
661 * PROCS USED: `sp_dev_re_guid_alter_tables`
662 *
663 * THIS PROCEDURE IS IN A TESTING PHASE, USE AT OWN RISK!
664 * 
665 * ex: CALL `sp_dev_re_guid`(1000); -- will renumber all existing guids in creature table starting with 1000 as initial
666 */
667    CALL `sp_dev_reguid_alter_tables`();
668    SET @s = CONCAT("ALTER TABLE `creature_temp` AUTO_INCREMENT=",new_base_guid,";");
669    PREPARE STM FROM @s;
670    EXECUTE STM;
671    INSERT INTO `creature_temp` (id,map,spawnMask,phaseMask,modelid,equipment_id,position_x,position_y,position_z,orientation,spawndist,currentwaypoint,curhealth,curmana,DeathState,MovementType,old_guid)
672    SELECT id,map,spawnMask,phaseMask,modelid,equipment_id,position_x,position_y,position_z,orientation,spawndist,currentwaypoint,curhealth,curmana,DeathState,MovementType,guid FROM `creature` ORDER BY `id` ASC;
673    UPDATE `creature_temp` SET guid = old_guid WHERE `old_guid`>250000;    
674		UPDATE game_event_npc_gossip p
675                INNER JOIN creature_temp pp
676                ON p.guid = pp.old_guid
677                SET p.new_guid = pp.guid;
678                UPDATE game_event_npc_vendor p
679                INNER JOIN creature_temp pp
680                ON p.guid = pp.old_guid
681                SET p.new_guid = pp.guid;
682                UPDATE game_event_npcflag p
683                INNER JOIN creature_temp pp
684                ON p.guid = pp.old_guid
685                SET p.new_guid = pp.guid;
686                UPDATE npc_gossip p
687                INNER JOIN creature_temp pp
688                ON p.npc_guid = pp.old_guid
689                SET p.new_guid = pp.guid;
690                UPDATE game_event_model_equip p
691                INNER JOIN creature_temp pp
692                ON p.guid = pp.old_guid
693                SET p.new_guid = pp.guid;
694                UPDATE creature_addon p
695                INNER JOIN creature_temp pp
696                ON p.guid = pp.old_guid
697                SET p.new_guid = pp.guid;
698                UPDATE creature_formations p
699                INNER JOIN creature_temp pp
700                ON p.leaderGUID = pp.old_guid
701                SET p.new_guid_leader = pp.guid;
702                UPDATE creature_formations p
703                INNER JOIN creature_temp pp
704                ON p.memberGUID = pp.old_guid
705                SET p.new_guid_member = pp.guid;
706                UPDATE creature_linked_respawn p
707                INNER JOIN creature_temp pp
708                ON p.guid = pp.old_guid
709                SET p.new_guid = pp.guid;
710                UPDATE creature_linked_respawn p
711                INNER JOIN creature_temp pp
712                ON p.guid = pp.old_guid
713                SET p.new_linked_guid = pp.guid;
714                UPDATE game_event_creature p
715                INNER JOIN creature_temp pp
716                ON p.guid = pp.old_guid
717                SET p.new_guid = pp.guid;
718                UPDATE pool_creature p
719                INNER JOIN creature_temp pp
720                ON p.guid = pp.old_guid
721                SET p.new_guid = pp.guid;
722                ALTER TABLE `creature_temp` DROP COLUMN `old_guid`;
723                DROP TABLE `creature`;
724                RENAME TABLE `creature_temp` TO `creature`;
725                ALTER TABLE game_event_npc_gossip DISABLE KEYS;
726                UPDATE `game_event_npc_gossip` SET `guid`=`new_guid`;        
727                ALTER TABLE `game_event_npc_gossip` DROP COLUMN `new_guid`;
728                ALTER TABLE game_event_npc_gossip ENABLE KEYS;
729                ALTER TABLE game_event_npc_vendor DISABLE KEYS;
730                UPDATE `game_event_npc_vendor` SET `guid`=`new_guid`;        
731                ALTER TABLE `game_event_npc_vendor` DROP COLUMN `new_guid`;
732                ALTER TABLE game_event_npc_vendor ENABLE KEYS;
733                ALTER TABLE game_event_npcflag DISABLE KEYS;
734                UPDATE `game_event_npcflag` SET `guid`=`new_guid`;        
735                ALTER TABLE `game_event_npcflag` DROP COLUMN `new_guid`;
736                ALTER TABLE game_event_npcflag ENABLE KEYS;
737                ALTER TABLE `npc_gossip` DROP PRIMARY KEY;
738                UPDATE `npc_gossip` SET `npc_guid`=`new_guid`;      
739                SELECT npc_guid,new_guid FROM npc_gossip;  
740                ALTER TABLE `npc_gossip` DROP COLUMN `new_guid`;
741                ALTER TABLE `npc_gossip` ADD PRIMARY KEY(`npc_guid`);
742                ALTER TABLE game_event_model_equip DISABLE KEYS;
743                UPDATE `game_event_model_equip` SET `guid`=`new_guid`;        
744                ALTER TABLE `game_event_model_equip` DROP COLUMN `new_guid`;
745                ALTER TABLE game_event_model_equip ENABLE KEYS;
746                ALTER TABLE `creature_addon` DROP PRIMARY KEY;
747                UPDATE `creature_addon` SET `guid`=`new_guid`;        
748                ALTER TABLE `creature_addon` DROP COLUMN `new_guid`;
749                ALTER TABLE `creature_addon` ADD PRIMARY KEY(`guid`);
750                ALTER TABLE creature_formations DISABLE KEYS;
751                UPDATE `creature_formations` SET leaderGUID = new_guid_leader;
752                ALTER TABLE `creature_formations` DROP COLUMN `new_guid_leader`;
753                UPDATE `creature_formations` SET memberGUID = new_guid_member;
754                ALTER TABLE `creature_formations` DROP COLUMN `new_guid_member`;
755                ALTER TABLE creature_formations ENABLE KEYS;
756                ALTER TABLE `creature_linked_respawn` DROP PRIMARY KEY;
757                UPDATE `creature_linked_respawn` SET guid = new_guid;
758                ALTER TABLE `creature_linked_respawn` DROP COLUMN `new_guid`;
759                UPDATE `creature_linked_respawn` SET linkedGuid = new_linked_guid;
760                ALTER TABLE `creature_linked_respawn` DROP COLUMN `new_linked_guid`;
761                ALTER TABLE `creature_linked_respawn` ADD PRIMARY KEY(`guid`);
762                ALTER TABLE `game_event_creature` DROP PRIMARY KEY;
763                UPDATE `game_event_creature` SET guid = new_guid;
764                ALTER TABLE `game_event_creature` DROP COLUMN `new_guid`;
765                ALTER TABLE `game_event_creature` ADD PRIMARY KEY(`guid`);
766                ALTER TABLE pool_creature DISABLE KEYS;
767                UPDATE `pool_creature` SET guid = new_guid;
768                ALTER TABLE `pool_creature` DROP COLUMN `new_guid`;
769                ALTER TABLE pool_creature ENABLE KEYS;
770
771    END //
772	
773	
774CREATE
775    PROCEDURE `sp_dev_cleanup_reference_loot`()
776    BEGIN
777/*SQL by Gyullo
778 * Cleanup procedure for referenced loot tables
779 * Remember to have backup before running!
780 * Example call:
781 * USE `your_world_db_name`;
782 * CALL sp_dev_cleanup_reference_loot();
783 * */
784-- DROP TABLE IF EXISTS `RL_temp`;
785CREATE TABLE `RL_temp` (
786    `ref_id` INT(8) UNSIGNED NOT NULL PRIMARY KEY DEFAULT '0'
787);
788INSERT IGNORE INTO `RL_temp` SELECT DISTINCT(`mincountOrRef`)*-1 FROM `gameobject_loot_template` WHERE `mincountOrRef` <0;
789INSERT IGNORE INTO `RL_temp` SELECT DISTINCT(`mincountOrRef`)*-1 FROM `item_loot_template` WHERE `mincountOrRef` <0;
790INSERT IGNORE INTO `RL_temp` SELECT DISTINCT(`mincountOrRef`)*-1 FROM `creature_loot_template` WHERE `mincountOrRef` <0;
791INSERT IGNORE INTO `RL_temp` SELECT DISTINCT(`mincountOrRef`)*-1 FROM `spell_loot_template` WHERE `mincountOrRef` <0;
792INSERT IGNORE INTO `RL_temp` SELECT DISTINCT(`mincountOrRef`)*-1 FROM `prospecting_loot_template` WHERE `mincountOrRef` <0;
793INSERT IGNORE INTO `RL_temp` SELECT DISTINCT(`mincountOrRef`)*-1 FROM `milling_loot_template` WHERE `mincountOrRef` <0;
794INSERT IGNORE INTO `RL_temp` SELECT DISTINCT(`mincountOrRef`)*-1 FROM `mail_loot_template` WHERE `mincountOrRef` <0;
795INSERT IGNORE INTO `RL_temp` SELECT DISTINCT(`mincountOrRef`)*-1 FROM `reference_loot_template` WHERE `mincountOrRef`<0;
796DELETE FROM `reference_loot_template` WHERE `entry` NOT IN (SELECT `ref_id` FROM `RL_temp`);
797DROP TABLE `RL_temp`;
798
799    END //
800
801	
802CREATE
803    PROCEDURE `sp_dev_cleanup_loot`()
804    BEGIN
805/*
806 * Cleanup procedure for loot tables
807 * Remember to have backup before running!
808 * Example call:
809 * USE `your_world_db_name`;
810 * CALL sp_dev_cleanup_loot();
811  */
812-- DROP TABLE IF EXISTS `Loot_temp`;
813CREATE TABLE `Loot_temp` (
814    `lootid` INT(8) UNSIGNED NOT NULL PRIMARY KEY DEFAULT '0'
815);
816INSERT IGNORE INTO `Loot_temp` SELECT `entry` FROM `item_template`;
817DELETE FROM `prospecting_loot_template` WHERE `entry` NOT IN (SELECT `lootid` FROM `Loot_temp`);
818DELETE FROM `Loot_temp`;
819INSERT IGNORE INTO `Loot_temp` SELECT `skinloot` FROM `creature_template` WHERE skinloot>0;
820DELETE FROM `skinning_loot_template` WHERE `entry` NOT IN (SELECT `lootid` FROM `Loot_temp`);
821DELETE FROM `Loot_temp`;
822INSERT IGNORE INTO `Loot_temp` SELECT `lootid` FROM `creature_template` WHERE lootid>0;
823DELETE FROM `creature_loot_template` WHERE `entry` NOT IN (SELECT `lootid` FROM `Loot_temp`);
824DELETE FROM `Loot_temp`;
825DROP TABLE `Loot_temp`;
826
827DELETE FROM `disenchant_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);
828DELETE FROM `disenchant_loot_template` WHERE `entry` NOT IN (SELECT `disenchantid` FROM `item_template`);
829DELETE FROM `pickpocketing_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);
830DELETE FROM `prospecting_loot_template` WHERE `entry` NOT IN (SELECT `entry` FROM `item_template`);
831DELETE FROM `prospecting_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);
832DELETE FROM `mail_loot_template` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);
833    END //
834	
835	
836CREATE
837    PROCEDURE `sp_dev_cleanup_world`()
838    BEGIN
839/* Procedure to clean world database
840 *
841 *Example call:
842 * USE `your_world_db_name`;
843 * CALL sp_dev_cleanup_world();
844 *
845 */
846DELETE FROM `gameobject` WHERE `id` NOT IN (SELECT `entry` FROM `gameobject_template`);
847DELETE FROM `creature` WHERE `id` NOT IN (SELECT `entry` FROM `creature_template`);
848DELETE FROM areatrigger_involvedrelation WHERE `quest` NOT IN (SELECT `entry` FROM `quest_template`);
849DELETE FROM `battlemaster_entry` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_template`);
850DELETE FROM `battlemaster_entry` WHERE `bg_template` NOT IN (SELECT `id` FROM `battleground_template`);
851UPDATE `creature` SET `equipment_id`=0 WHERE `equipment_id` NOT IN (SELECT `entry` FROM `creature_equip_template`) AND `equipment_id`!=0;
852DELETE FROM `creature_addon` WHERE `guid` NOT IN (SELECT `guid` FROM `creature`);
853DELETE FROM `creature_ai_scripts` WHERE `creature_id` NOT IN (SELECT `entry` FROM `creature_template`);
854DELETE FROM `creature_formations` WHERE leaderGUID NOT IN (SELECT `guid` FROM `creature`);
855DELETE FROM `creature_formations` WHERE memberGUID NOT IN (SELECT `guid` FROM `creature`);
856DELETE FROM `creature_involvedrelation` WHERE `id` NOT IN (SELECT `entry` FROM `creature_template`);
857DELETE FROM `creature_involvedrelation` WHERE `quest` NOT IN (SELECT `entry` FROM `quest_template`);
858DELETE FROM `creature_linked_respawn` WHERE `guid` NOT IN (SELECT `guid` FROM `creature`);
859DELETE FROM `creature_linked_respawn` WHERE `linkedGuid` NOT IN (SELECT `guid` FROM `creature`);
860DELETE FROM `creature_onkill_reputation` WHERE `creature_id` NOT IN (SELECT `entry` FROM `creature_template`);
861DELETE FROM `creature_questrelation` WHERE `id` NOT IN (SELECT `entry` FROM `creature_template`);
862DELETE FROM `creature_questrelation` WHERE `quest` NOT IN (SELECT `entry` FROM `quest_template`);
863DELETE FROM `creature_template_addon` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_template`);
864DELETE FROM `game_event_creature_quest` WHERE `id` NOT IN (SELECT `entry` FROM `creature_template`);
865DELETE FROM `game_event_creature_quest` WHERE `quest` NOT IN (SELECT `entry` FROM `quest_template`);
866DELETE FROM `game_event_creature_quest` WHERE `event` NOT IN (SELECT `entry` FROM `game_event`);
867DELETE FROM `game_event_gameobject_quest` WHERE `id` NOT IN (SELECT `entry` FROM `gameobject_template`);
868DELETE FROM `game_event_gameobject_quest` WHERE `quest` NOT IN (SELECT `entry` FROM `quest_template`);
869DELETE FROM `game_event_gameobject_quest` WHERE `event` NOT IN (SELECT `entry` FROM `game_event`);
870DELETE FROM `game_event_npc_gossip` WHERE `event_id` NOT IN (SELECT `entry` FROM `game_event`);
871DELETE FROM `game_event_model_equip` WHERE `event` NOT IN (SELECT `entry` FROM `game_event`);
872DELETE FROM `game_event_battleground_holiday` WHERE `event` NOT IN (SELECT `entry` FROM `game_event`);
873DELETE FROM `game_event_condition` WHERE `event_id` NOT IN (SELECT `entry` FROM `game_event`);
874DELETE FROM `game_event_creature` WHERE `event` NOT IN (SELECT `entry` FROM `game_event`);
875DELETE FROM `game_event_gameobject` WHERE `event` NOT IN (SELECT `entry` FROM `game_event`);
876DELETE FROM `game_event_npc_vendor` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);
877DELETE FROM `game_event_npc_vendor` WHERE `event` NOT IN (SELECT `entry` FROM `game_event`);
878DELETE FROM `game_event_npcflag` WHERE `event_id` NOT IN (SELECT `entry` FROM `game_event`);
879DELETE FROM `game_event_pool` WHERE `event` NOT IN (SELECT `entry` FROM `game_event`);
880DELETE FROM `game_event_prerequisite` WHERE `event_id` NOT IN (SELECT `entry` FROM `game_event`);
881DELETE FROM `game_event_prerequisite` WHERE `prerequisite_event` NOT IN (SELECT `entry` FROM `game_event`);
882DELETE FROM `game_event_quest_condition` WHERE `event_id` NOT IN (SELECT `entry` FROM `game_event`);
883DELETE FROM `game_event_quest_condition` WHERE `quest` NOT IN (SELECT `entry` FROM `quest_template`);
884DELETE FROM `gameobject_involvedrelation` WHERE `id` NOT IN (SELECT `entry` FROM `gameobject_template`);
885DELETE FROM `gameobject_involvedrelation` WHERE `quest` NOT IN (SELECT `entry` FROM `quest_template`);
886DELETE FROM `item_required_target` WHERE `targetEntry` NOT IN (SELECT `entry` FROM `creature_template`) AND `type` IN (1,2);
887DELETE FROM `mail_level_reward` WHERE `senderEntry` NOT IN (SELECT `entry` FROM `creature_template`);
888DELETE FROM `npc_gossip` WHERE `npc_guid` NOT IN (SELECT `guid` FROM `creature`);
889DELETE FROM `npc_spellclick_spells` WHERE `npc_entry` NOT IN (SELECT `entry` FROM `creature_template`);
890DELETE FROM `npc_spellclick_spells` WHERE `quest_start` NOT IN (SELECT `entry` FROM `quest_template`) AND `quest_start`<>0;
891DELETE FROM `npc_spellclick_spells` WHERE `quest_end` NOT IN (SELECT `entry` FROM `quest_template`) AND `quest_end`<>0;
892DELETE FROM `npc_trainer` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_template`);
893DELETE FROM `npc_vendor` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_template`);
894DELETE FROM `npc_vendor` WHERE `item` NOT IN (SELECT `entry` FROM `item_template`);
895DELETE FROM `pet_levelstats` WHERE `creature_entry` NOT IN (SELECT `entry` FROM `creature_template`);
896DELETE FROM `quest_end_scripts` WHERE `id` NOT IN (SELECT `CompleteScript` FROM `quest_template`);
897DELETE FROM `quest_start_scripts` WHERE `id` NOT IN (SELECT `StartScript` FROM `quest_template`);
898DELETE FROM `spell_script_target` WHERE `targetEntry` NOT IN (SELECT `entry` FROM `creature_template`) AND `type` IN (1,2,3) AND `targetEntry`!=0;
899DELETE FROM `spell_script_target` WHERE `targetEntry` NOT IN (SELECT `entry` FROM `gameobject_template`) AND `type`=0 AND `targetEntry`!=0;
900    END //
901	
902
903-- ------------------ --
904--   ERROR HANDLERS   --
905-- ------------------ --
906
907-- sp_error_entry
908CREATE PROCEDURE `sp_error_entry`(IN entry_type VARCHAR(10), IN e_entry INT(10))
909BEGIN
910/**
911 * DEGREE: ERROR-HANDLING
912 * TABLES AFFECTED: creature_template, gameobject_template
913 * PROCS USED: none
914 * 
915 * Validate whether or not a entry for an npc/gameobject exists in its respective spawn table. Will error out if does not exist.
916 *
917 * e_entry - entry of the npc or gameobject you are validating
918 * entry_type - 'NPC','GO','ITEM', and 'QUEST' are the only values accepted (for npc or gameobject)
919 *
920 * ex: CALL `sp_error_entry`('NPC',98753); -- make sure an npc spawn of entry 98753 exists in the db before performing actions using that entry
921*/
922	DECLARE check_entry INT;
923
924	CASE UPPER(entry_type)
925		WHEN 'NPC' THEN
926			SET check_entry = (SELECT COUNT(`entry`) FROM `creature_template` WHERE `entry`=e_entry);
927		WHEN 'GO' THEN
928			SET check_entry = (SELECT COUNT(`entry`) FROM `gameobject_template` WHERE `entry`=e_entry);
929		WHEN 'ITEM' THEN
930			SET check_entry = (SELECT COUNT(`entry`) FROM `item_template` WHERE `entry`=e_entry);
931		WHEN 'QUEST' THEN
932			SET check_entry = (SELECT COUNT(`entry`) FROM `quest_template` WHERE `entry`=e_entry);
933		ELSE
934			CALL INCORRECT_ENTRY_TYPE;
935	END CASE;
936	
937	IF check_entry=0 THEN
938		CALL INCORRECT_ENTRY;
939	END IF;
940END//	
941	
942CREATE
943    PROCEDURE `sp_error_trigger`(IN trigger_entry INT)
944    BEGIN
945/**
946 * DEGREE: ERROR HANDLER
947 * TABLES AFFECTED: quest_template
948 * PROCS USED: none
949 *
950 * Error handling for TDB procedure: check if triggerID for eAI is objective of any quest
951 *
952 * trigger_id - ID to check against the db for quest objective
953 *
954 * ex: CALL `sp_error_trigger` (257); -- make sure trigger (creature_template.entry = 257) is requirement for a quest
955 */
956    DECLARE Check_trigger INT;
957    SET Check_trigger = (SELECT COUNT(ReqCreatureOrGOId1) FROM `quest_template` WHERE `ReqCreatureOrGOId1`= trigger_entry)
958    + (SELECT COUNT(ReqCreatureOrGOId2) FROM `quest_template` WHERE `ReqCreatureOrGOId2`= trigger_entry)
959    + (SELECT COUNT(ReqCreatureOrGOId3) FROM `quest_template` WHERE `ReqCreatureOrGOId3`= trigger_entry)
960    + (SELECT COUNT(ReqCreatureOrGOId4) FROM `quest_template` WHERE `ReqCreatureOrGOId4`= trigger_entry);
961    IF Check_trigger = 0 THEN
962        CALL NO_QUEST_WITH_REQUIREMENT();
963    END IF;
964    END//
965	
966-- sp_error_guid
967CREATE PROCEDURE `sp_error_guid`(IN guid_type VARCHAR(10),IN npc_or_go_guid INT(10))
968BEGIN
969/**
970 * DEGREE: ERROR-HANDLING
971 * TABLES AFFECTED: creature, gameobject
972 * PROCS USED: none
973 * 
974 * Validate whether or not a guid for an npc/gameobject exists in its respective spawn table. Will error out if does not exist.
975 *
976 * npc_or_go_guid - Guid of the npc or gameobject spawn you are validating
977 * guid_type - 'NPC' or 'GO' are the only values accepted (for npc or gameobject)
978 *
979 * ex: CALL `sp_error_guid`('NPC',98753); -- make sure an npc spawn of guid 98753 exists in the db before performing actions using that guid
980*/
981	DECLARE check_guid INT;
982
983	CASE UPPER(guid_type)
984		WHEN 'NPC' THEN
985			SET check_guid = (SELECT COUNT(`guid`) FROM `creature` WHERE `guid`=npc_or_go_guid);
986		WHEN 'GO' THEN
987			SET check_guid = (SELECT COUNT(`guid`) FROM `gameobject` WHERE `entry`=npc_or_go_entry);
988		ELSE
989			CALL INCORRECT_GUID_TYPE;
990	END CASE;
991	
992	IF check_guid=0 THEN
993		CALL INCORRECT_CREATURE_OR_GO_GUID;
994	END IF;
995END//
996
997-- ------------------ --
998--     UTILITIES      --
999-- ------------------ --
1000
1001-- sp_get_ref_id
1002CREATE PROCEDURE `sp_get_ref_id` (IN refType VARCHAR(10),OUT reference MEDIUMINT(5))
1003BEGIN
1004/**
1005 * DEGREE: BASIC
1006 * TABLES AFFECTED: reference_loot_template
1007 * PROCS USED: none
1008 *
1009 * Get a generated loot reference id based on the type of loot its to be used for
1010 *
1011 * refType - [SKIN,ITEM,FISH,MILL,RAID_GOBJ,MINE,PROSPECT,WORLD,RAID_CRE,DUNGEON]
1012 * reference - OUT. Variable that is filled with the desired reference id.
1013 *
1014 * ex: CALL `sp_get_util_refid`('RAID_CRE',@Test);
1015 *     SELECT @Test
1016 */
1017    CASE UCASE(refType)
1018        WHEN 'SKIN' THEN BEGIN
1019            SET @Low :=00000;
1020            SET @High :=1000;
1021        END;
1022        WHEN 'ITEM' THEN BEGIN
1023            SET @Low :=10000;
1024            SET @High :=10999;
1025        END;
1026        WHEN 'FISH' THEN BEGIN
1027            SET @Low :=11000;
1028            SET @High :=11799;
1029        END;
1030        WHEN 'MILL' THEN BEGIN
1031            SET @Low :=11800;
1032            SET @High :=11999;
1033        END;
1034        WHEN 'RAID_GOBJ' THEN BEGIN
1035            SET @Low :=12000;
1036            SET @High :=12899;
1037        END;
1038        WHEN 'MINE' THEN BEGIN
1039            SET @Low :=12900;
1040            SET @High :=12999;
1041        END;
1042        WHEN 'PROSPECT' THEN BEGIN
1043            SET @Low :=13000;
1044            SET @High :=13999;
1045        END;
1046        WHEN 'WORLD' THEN BEGIN
1047            SET @Low :=14000;
1048            SET @High :=29000;
1049        END;
1050        WHEN 'RAID_CRE' THEN BEGIN
1051            SET @Low :=34000;
1052            SET @High :=34999;
1053        END;
1054        WHEN 'DUNGEON' THEN BEGIN
1055            SET @Low :=35000;
1056            SET @High :=35999;
1057        END;
1058        ELSE CALL INVALID_REFERENCE_TYPE;
1059    END CASE;
1060    SET reference :=1+(SELECT `entry` FROM `reference_loot_template` WHERE `entry` BETWEEN @Low AND @High ORDER BY `entry` DESC LIMIT 1);
1061END//
1062
1063-- ------------------ --
1064-- BASIC PROCEDURES   --
1065-- ------------------ --
1066
1067
1068-- sp_set_npc_path
1069CREATE PROCEDURE `sp_set_npc_path`(IN npc_guid INT(10),OUT path INT(10))
1070BEGIN
1071/**
1072 * DEGREE: BASIC
1073 * TABLES AFFECTED: creature, creature_addon
1074 * PROCS USED: sp_error_guid
1075 *
1076 * Set appropriate flags to enable a spawned creature to move along waypoints
1077 *
1078 * guid - Guid of the creature spawn you wish
1079 * path - OUT. Variable whose value represents the path_id assigned to the supplied guid
1080 * 
1081 * ex: CALL `sp_set_npc_path`(98753,@PATH); -- makes spawn 98753 (creature.guid=98753) able to move along waypoints
1082 *     SELECT @PATH; -- use the path_id in later queries
1083 */
1084	CALL `sp_error_guid`('NPC',npc_guid);
1085	
1086	SELECT npc_guid*10 INTO path;
1087
1088	UPDATE `creature` SET `MovementType`=2,`spawndist`=0 WHERE `guid`=npc_guid;
1089
1090	IF (SELECT COUNT(*) FROM `creature_addon` WHERE `guid`=npc_guid) > 0 THEN
1091		UPDATE `creature_addon` SET `path_id`=path WHERE `guid`=npc_guid;
1092	ELSE
1093		INSERT INTO `creature_addon` (`guid`,`path_id`) VALUES (npc_guid,path);
1094	END IF;
1095	
1096	DELETE FROM `waypoint_data` WHERE `id`=path;
1097END//
1098
1099CREATE PROCEDURE `sp_set_quest_level`(IN quest_entry INT(10), IN quest_level INT(10))
1100BEGIN
1101/**
1102 * DEGREE: BASIC
1103 * TABLES AFFECTED: quest_template
1104 * PROCS USED: sp_error_entry
1105 *
1106 * Update quest to provided level
1107 * ONLY FOR SEASONAL QUESTS WHERE LEVELS ARE NOT CORRECT FROM WDB!
1108 *
1109 * quest_entry - ID of a quest from quest_template
1110 * quest_level - new MinLevel value
1111 * 
1112 * ex: CALL `quest_Level` (11335,30) - sets MinLevel of quest ID 11335 (Call to Arms: Arathi Basin) to 30
1113 */
1114CALL `sp_error_entry`('QUEST',quest_entry);
1115UPDATE `quest_template` SET `MinLevel`= quest_level WHERE `entry`= quest_entry;
1116END //
1117
1118
1119CREATE PROCEDURE `sp_set_quest_next`(IN quest_entry INT(10), IN next_quest INT(10))
1120BEGIN
1121/**
1122 * DEGREE: BASIC
1123 * TABLES AFFECTED: quest_template
1124 * PROCS USED: sp_error_entry
1125 *
1126 * Update next quest value
1127 *
1128 * quest_entry - ID of a quest from quest_template
1129 * next_quest - new NextQuestId
1130 * 
1131 * ex: CALL `quest_Level` (11335,11230) - sets NextQuestId after quest ID 11335 (Call to Arms: Arathi Basin) to 11230
1132 */
1133CALL `sp_error_entry`('QUEST',quest_entry);
1134UPDATE `quest_template` SET `NextQuestId`= next_quest WHERE `entry`= quest_entry;
1135END //
1136
1137
1138CREATE PROCEDURE `sp_set_quest_previous`(IN quest_entry INT(10), IN prev_quest INT(10))
1139BEGIN
1140/**
1141 * DEGREE: BASIC
1142 * TABLES AFFECTED: quest_template
1143 * PROCS USED: sp_error_entry
1144 *
1145 * Update prev quest value
1146 *
1147 * quest_entry - ID of a quest from quest_template
1148 * next_quest - new PrevQuestId
1149 * 
1150 * ex: CALL `quest_Level` (11335,11230) - sets PrevQuestId for quest ID 11335 (Call to Arms: Arathi Basin) to 11230
1151 */
1152CALL `sp_error_entry`('QUEST',quest_entry);
1153UPDATE `quest_template` SET `PrevQuestId`= prev_quest WHERE `entry`= quest_entry;
1154END //
1155
1156
1157-- sp_set_entry_list
1158CREATE PROCEDURE `sp_set_entry_list` (IN input LONGTEXT,IN appendExisting BIT) 
1159BEGIN
1160/**
1161 * DEGREE: UTILITY
1162 * TABLES AFFECTED: tdb_entry_list (temp)
1163 * PROCS USED: none
1164 *
1165 * Utility procedure to split a comma-delimited list into a temporary table to be used outside of the procedure.
1166 * ***USE WITH CARE! Drop up the temporary table after using it!***
1167 *
1168 * input - comma-delimited list of entries to be split and inserted individually into a temporary table
1169 * appendExisting - NULLABLE. If present and true, does not drop existing temp table, rather, appends the existing table
1170 *
1171 * ex: CALL sp_set_entry_list ('1,2,3,4,5,6',null);
1172 *     SELECT * FROM `tdb_entry_list`;
1173 *     DROP TABLE `tdb_entry_list`;
1174 */
1175    DECLARE cur_position INT DEFAULT 1;
1176    DECLARE remainder TEXT;
1177    DECLARE cur_string VARCHAR(10);
1178    DECLARE entry_count MEDIUMINT;
1179
1180    IF appendExisting IS NULL OR appendExisting IS FALSE THEN
1181	CREATE TABLE `tdb_entry_list` (`value` INT NOT NULL PRIMARY KEY) ENGINE=MYISAM;
1182    END IF;
1183
1184    SET remainder = input;
1185    WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
1186        SET cur_position = INSTR(remainder, ',');
1187        IF cur_position = 0 THEN
1188            SET cur_string = remainder;
1189        ELSE
1190            SET cur_string = LEFT(remainder, cur_position-1);
1191        END IF;
1192
1193        IF TRIM(cur_string) != '' AND(SELECT COUNT(*) FROM `tdb_entry_list` WHERE `value`=cur_string)=0 THEN
1194            INSERT INTO `tdb_entry_list` VALUES (cur_string);
1195        END IF;
1196
1197        SET remainder = SUBSTRING(remainder, cur_position+1);
1198    END WHILE;
1199END//
1200
1201-- sp_set_npc_aggro
1202CREATE PROCEDURE `sp_set_npc_aggro`(IN creature_entry INT(10),IN on_off BOOLEAN)
1203BEGIN
1204/**
1205 * DEGREE: BASIC
1206 * TABLES AFFECTED: creature_template
1207 * PROCS USED: sp_CheckNpcEntry
1208 *
1209 * Update creature to ignore aggro
1210 *
1211 * creature_entry - ID of NPC from `creature_template`.`entry`
1212 * on_off - whether to turn aggro on or off
1213 * 
1214 * ex: CALL `sp_set_npc_aggro`(257,false); -- makes NPC with ID 257 ignore aggro
1215 */
1216	CALL `sp_error_entry`('NPC',creature_entry);
1217	IF on_off = 1 THEN
1218		UPDATE `creature_template` SET `flags_extra`=`flags_extra`&~2 WHERE `entry`= creature_entry;
1219	ELSE 
1220		UPDATE `creature_template` SET `flags_extra`=`flags_extra`|2 WHERE `entry`= creature_entry;
1221	END IF;
1222END //
1223
1224-- sp_set_npc_aggro_bylist
1225CREATE PROCEDURE `sp_set_npc_aggro_bylist`(IN entry_list LONGTEXT,IN on_off BOOLEAN)
1226BEGIN
1227/**
1228 * DEGREE: BASIC
1229 * TABLES AFFECTED: creature_template
1230 * PROCS USED: sp_CheckNpcEntry
1231 *
1232 * Update creature to ignore aggro
1233 *
1234 * entry_list - Comma-delimited list of NPC IDs from `creature_template`.`entry`
1235 * on_off - whether to turn aggro on or off
1236 * 
1237 * ex: CALL `sp_set_npc_aggro`(257,false); -- makes NPC with ID 257 ignore aggro
1238 */
1239	CALL `sp_set_entry_list` (entry_list,null);
1240
1241	IF on_off = 1 THEN
1242		UPDATE `creature_template` SET `flags_extra`=`flags_extra`&~2 WHERE `entry` IN (SELECT * FROM `tdb_entry_list`);
1243	ELSE 
1244		UPDATE `creature_template` SET `flags_extra`=`flags_extra`|2 WHERE `entry` IN (SELECT * FROM `tdb_entry_list`);
1245	END IF;
1246
1247	DROP TABLE `tdb_entry_list`; -- dont forget the cleanup!
1248END //
1249
1250-- sp_set_npc_faction
1251CREATE PROCEDURE `sp_set_npc_faction`(IN npc_entry INT(10), factionA INT(10), factionH INT(10))
1252BEGIN
1253/**
1254 * DEGREE: BASIC
1255 * TABLES AFFECTED: creature_template
1256 * PROCS USED: sp_CheckNpcEntry
1257 *
1258 * Update creature's faction
1259 *
1260 * npc_entry - ID of NPC from `creature_template`.`entry`
1261 * factionA - NULLABLE. Alliance faction to assign to the specified npc (if null, uses current value).
1262 * factionH - NULLABLE. Horde faction to assign to the specified npc (if null, uses current value).
1263 * 
1264 * ex: CALL `sp_set_npc_faction`(257,7,7); -- sets faction to 7 for NPC with ID 257 (Kobold Worker)
1265 */
1266	CALL `sp_error_entry`('NPC',npc_entry);
1267	UPDATE `creature_template` SET `faction_A`=IFNULL(factionA,`faction_A`),`faction_H`=IFNULL(factionH,`faction_A`) WHERE `entry`=npc_entry;
1268END //
1269
1270-- sp_set_npc_faction_bylist
1271CREATE PROCEDURE `sp_set_npc_faction_bylist`(IN entry_list LONGTEXT, factionA INT(10), factionH INT(10))
1272BEGIN
1273/**
1274 * DEGREE: BASIC
1275 * TABLES AFFECTED: creature_template
1276 * PROCS USED: sp_CheckNpcEntry
1277 *
1278 * Update creature's faction
1279 *
1280 * entry_list - Comma-delimited list of NPC IDs from `creature_template`.`entry`
1281 * factionA - NULLABLE. Alliance faction to assign to the specified npc (if null, uses current value).
1282 * factionH - NULLABLE. Horde faction to assign to the specified npc (if null, uses current value).
1283 * 
1284 * ex: CALL `sp_set_npc_faction`(257,7,7); -- sets faction to 7 for NPC with ID 257 (Kobold Worker)
1285 */
1286	CALL `sp_set_entry_list` (entry_list,null);
1287
1288	UPDATE `creature_template` SET `faction_A`=IFNULL(factionA,`faction_A`),`faction_H`=IFNULL(factionH,`faction_A`) WHERE `entry` IN (SELECT * FROM `tdb_entry_list`);
1289
1290	DROP TABLE `tdb_entry_list`; -- dont forget the cleanup!
1291END //
1292
1293-- sp_set_npc_selectable
1294CREATE PROCEDURE `sp_set_npc_selectable`(IN npc_entry INT(10),IN on_off BOOLEAN)
1295BEGIN
1296/**
1297 * DEGREE: BASIC
1298 * TABLES AFFECTED: creature_template
1299 * PROCS USED: sp_CheckNpcEntry
1300 *
1301 * Update creature to make it selectable
1302 *
1303 * npc_entry - ID of NPC from `creature_template`.`entry`
1304 * on_off - whether or not the specified NPC should be selectable
1305 * 
1306 * ex: CALL `sp_set_npc_selectable`(257,true) - makes creature with ID 257 (Kobold Worker) selectable 
1307 */
1308	CALL `sp_error_entry`('NPC',npc_entry);
1309	IF on_off=1 THEN
1310		UPDATE `creature_template` SET `unit_flags`=`unit_flags`&~33554432 WHERE `entry`=npc_entry;
1311	ELSE
1312		UPDATE `creature_template` SET `unit_flags`=`unit_flags`|33554432 WHERE `entry`=npc_entry;
1313	END IF;
1314END //
1315
1316-- sp_set_npc_selectable_bylist
1317CREATE PROCEDURE `sp_set_npc_selectable_bylist`(IN entry_list LONGTEXT,IN on_off BOOLEAN)
1318BEGIN
1319/**
1320 * DEGREE: BASIC
1321 * TABLES AFFECTED: creature_template
1322 * PROCS USED: sp_CheckNpcEntry
1323 *
1324 * Update creature to make it selectable
1325 *
1326 * npc_entry - ID of NPC from `creature_template`.`entry`
1327 * on_off - whether or not the specified NPC should be selectable
1328 * 
1329 * ex: CALL `sp_set_npc_selectable_bylist`('257,3,6',true) - makes creature with IDs of 257,3, and 6 to be selectable 
1330 */
1331	CALL `sp_set_entry_list` (entry_list,null);
1332
1333	IF on_off=1 THEN
1334		UPDATE `creature_template` SET `unit_flags`=`unit_flags`&~33554432 WHERE `entry` IN (SELECT * FROM `tdb_entry_list`);
1335	ELSE
1336		UPDATE `creature_template` SET `unit_flags`=`unit_flags`|33554432 WHERE `entry` IN (SELECT * FROM `tdb_entry_list`);
1337	END IF;
1338
1339	DROP TABLE `tdb_entry_list`; -- dont forget the cleanup!
1340END //
1341
1342-- sp_set_npc_deadquest
1343CREATE PROCEDURE `sp_set_npc_deadquest`(IN npc_entry INT(10))
1344BEGIN
1345/**
1346 * DEGREE: BASIC
1347 * TABLES AFFECTED: creature_template_addon, creature
1348 * PROCS USED: sp_error_entry
1349 *
1350 * Update creature to appear death but still react to eAI / give or take quests
1351 *
1352 * creature_entry - ID of NPC from `creature_template`.`entry`
1353 * 
1354 * ex: CALL `sp_set_npc_deadquest` (257); -- Makes creature with entry 257 appear dead but still albe to give / take quests or react to spellhits
1355 */
1356	DECLARE check_addon_exists INT;
1357	CALL `sp_error_entry`('NPC',npc_entry);
1358
1359	UPDATE `creature` SET `MovementType`=0,`spawndist`=0,`Deathstate`=0 WHERE `id`=npc_entry;
1360	UPDATE `creature_template` SET `flags_extra`=`flags_extra`|2 WHERE `entry`=npc_entry;
1361    
1362	-- if has creature_template_addon entry, update, else insert new row	
1363	SET check_addon_exists = (SELECT COUNT(`entry`) FROM `creature_template_addon` WHERE `entry`=npc_entry);
1364	IF check_addon_exists > 0 THEN 
1365		UPDATE `creature_template_addon` SET `bytes1`=7 WHERE `entry`=npc_entry;
1366	ELSE 
1367		INSERT INTO `creature_template_addon` VALUES (npc_entry,0,0,7,0,0, '');
1368	END IF;
1369END //
1370
1371-- sp_set_npc_trigger
1372CREATE PROCEDURE `sp_set_npc_trigger` (IN npc_entry INT(10),IN on_off BOOLEAN)
1373BEGIN
1374/**
1375 * DEGREE: BASIC
1376 * TABLES AFFECTED: creature, creature_template
1377 * PROCS USED: sp_error_entry
1378 *
1379 * Sets NPC as a trigger (disable movements, ignore aggro, and disable targetting)
1380 *
1381 * npc_entry - Entry of the npc for whom template is updated
1382 * on_off - If true, sets all spawns to respond accordingly and updates the template, if false, removes flags
1383 *
1384 * ex: CALL `sp_tdb_TriggerSettings` (257,true); -- sets creature having `creature_template`.`entry` = 257 to act as trigger
1385 */
1386	CALL `sp_error_entry`('NPC',npc_entry);
1387
1388	IF on_off=1 THEN
1389		UPDATE `creature` SET `spawndist`=0,`MovementType`=0 WHERE `id`=npc_entry;
1390		UPDATE `creature_template` SET `flags_extra`=`flags_extra`|2,`unit_flags`=`unit_flags`|33554432  WHERE `entry`=npc_entry;        
1391	ELSE
1392		UPDATE `creature_template` SET `flags_extra`=`flags_extra`&~2,`unit_flags`=`unit_flags`&~33554432  WHERE `entry`=npc_entry; 
1393	END IF;
1394END //
1395
1396-- sp_set_npc_trigger_bylist
1397CREATE PROCEDURE `sp_set_npc_trigger_bylist` (IN entry_list LONGTEXT,IN on_off BOOLEAN)
1398BEGIN
1399/**
1400 * DEGREE: BASIC
1401 * TABLES AFFECTED: creature, creature_template
1402 * PROCS USED: sp_error_entry
1403 *
1404 * Sets NPC as a trigger (disable movements, ignore aggro, and disable targetting)
1405 *
1406 * npc_entry - Entry of the npc for whom template is updated
1407 * on_off - If true, sets all spawns to respond accordingly and updates the template, if false, removes flags
1408 *
1409 * ex: CALL `sp_tdb_TriggerSettings` (257,true); -- sets creature having `creature_template`.`entry` = 257 to act as trigger
1410 */
1411	CALL `sp_set_entry_list` (entry_list,null);
1412
1413	IF on_off=1 THEN
1414		UPDATE `creature` SET `spawndist`=0,`MovementType`=0 WHERE `id` IN (SELECT * FROM `tdb_entry_list`);
1415		UPDATE `creature_template` SET `flags_extra`=`flags_extra`|2,`unit_flags`=`unit_flags`|33554432  WHERE `entry` IN (SELECT * FROM `tdb_entry_list`);        
1416	ELSE
1417		UPDATE `creature_template` SET `flags_extra`=`flags_extra`&~2,`unit_flags`=`unit_flags`&~33554432  WHERE `entry` IN (SELECT * FROM `tdb_entry_list`); 
1418	END IF;
1419
1420	DROP TABLE `tdb_entry_list`; -- dont forget the cleanup!
1421END //
1422
1423-- sp_set_npc_lootid
1424CREATE PROCEDURE `sp_set_npc_lootid` (IN npcEntry MEDIUMINT(5),IN lootID MEDIUMINT(5))
1425BEGIN    
1426/**
1427 * DEGREE: BASIC
1428 * TABLES AFFECTED: creature_template
1429 * PROCS USED: sp_error_entry
1430 *
1431 * Sets the loot id of a specified NPC
1432 *
1433 * npcEntry - Entry of the npc whose lootid you would like to set
1434 * loot - OPTIONAL. If provided, set the specified NPC's lootid to this value. If NULL, NPC uses its own entry.
1435 * 
1436 * ex: CALL `sp_set_npc_lootid` (99999,NULL); -- sets the lootid to its own entry number
1437 */
1438    CALL `sp_error_entry`('NPC',npcEntry);
1439    UPDATE `creature_template` SET `lootid`=IFNULL(lootID,npcEntry) WHERE `entry`=npcEntry;
1440END//
1441
1442-- `sp_set_npc_lootid_bylist`
1443CREATE PROCEDURE `sp_set_npc_lootid_bylist` (IN entryList LONGTEXT,IN lootID MEDIUMINT(5))
1444BEGIN    
1445/**
1446 * DEGREE: AVERAGE
1447 * TABLES AFFECTED: creature_template, tdb_entry_list (temp)
1448 * PROCS USED: sp_set_entry_list
1449 *
1450 * Sets the loot id of a specified NPC
1451 *
1452 * npcEntry - Entry of the npc whose lootid you would like to set
1453 * loot - NULLABLE. If provided, set the specified NPC's lootid to this value. If NULL, NPC uses its own entry.
1454 * 
1455 * ex: CALL `sp_get_npc_lootid_bylist`('1,2,3,4,5','99999'); -- sets the loot id of 5 specified npcs to 99999
1456 */
1457    CALL `sp_set_entry_list` (entryList,null);
1458    UPDATE `creature_template` SET `lootid`=IFNULL(lootID,`entry`) WHERE `entry` IN (SELECT DISTINCT * FROM `tdb_entry_list`);
1459    
1460    DROP TABLE `tdb_entry_list`; -- dont forget the cleanup!
1461END//
1462
1463-- sp_set_npc_lootid_bytable
1464CREATE PROCEDURE `sp_set_npc_lootid_bytable` (IN lootID MEDIUMINT(5))
1465BEGIN    
1466/**
1467 * DEGREE: AVERAGE
1468 * TABLES AFFECTED: creature_template, tdb_entry_list
1469 * PROCS USED: sp_set_entry_list
1470 *
1471 * Sets the loot id of specified NPCs based on entries in tdb_entry_list
1472 *
1473 * loot - NULLABLE. If provided, set the specified NPC's lootid to this value. If NULL, NPC uses its own entry.
1474 * 
1475 * ex: CALL `sp_get_npc_lootid_bytable`('99999'); -- sets the loot id of all npcs in tdb_entry_list to 99999
1476 */
1477    UPDATE `creature_template` SET `lootid`=IFNULL(lootID,`entry`) WHERE `entry` IN (SELECT DISTINCT * FROM `tdb_entry_list`);
1478END//
1479
1480-- sp_get_npc_diffentry
1481CREATE PROCEDURE `sp_get_npc_diffentry` (IN normalEntry MEDIUMINT(5),IN difficulty TINYINT(1),OUT output MEDIUMINT(8))
1482BEGIN
1483/**
1484 * DEGREE: BASIC
1485 * TABLES AFFECTED: creature_template
1486 * PROCS USED: sp_error_entry
1487 *
1488 * Retrieves the specified difficulty entry for a given NPC
1489 *
1490 * normalEntry - ID of the npc whose difficulty entry is to be 
1491 * difficulty - level of difficulty who entry should be retrieved
1492 * output - OUT. desired difficulty entry is placed in this variable for use by the caller
1493 *
1494 * ex: CALL `sp_get_npc_diffentry`(10184,1,@Test);
1495 *     SELECT @Test;
1496 */
1497    CALL `sp_error_entry`('NPC',normalEntry);
1498
1499    CASE difficulty
1500        WHEN 1 THEN BEGIN 
1501            SELECT `difficulty_entry_1` FROM `creature_template` WHERE `entry`=normalEntry INTO output; 
1502        END;
1503        WHEN 2 THEN BEGIN 
1504            SELECT `difficulty_entry_2` FROM `creature_template` WHERE `entry`=normalEntry INTO output; 
1505        END;
1506        WHEN 3 THEN BEGIN 
1507            SELECT `difficulty_entry_3` FROM `creature_template` WHERE `entry`=normalEntry INTO output; 
1508        END;
1509        ELSE CALL INVALID_DIFFICULTY();
1510    END CASE;
1511END//
1512
1513-- sp_set_spell_target
1514CREATE PROCEDURE `sp_set_spell_target`(IN target_type VARCHAR(10),IN target_entry INT(11),IN spell_id INT(11))
1515BEGIN
1516/**
1517 * DEGREE: BASIC
1518 * TABLES AFFECTED: creature_template, spell_script_target
1519 * PROCS USED: sp_error_entry
1520 *
1521 * Sets target requirement for spellcast
1522 *
1523 * target_type - [GO|NPC|DEAD_NPC|MINION]
1524 * target_entry - ID of creature or gameobject (if zero, spell focus object if type='GO', target player for AOE if type='NPC')
1525 * spell_id - ID of spell we want to set target for
1526 * 
1527 * ex: CALL `sp_set_spell_target` ('NPC',257,4444); -- allows spell 4444 to be cast only on living creature with `creature_template`.`entry` = 257
1528 */
1529    CASE UCASE(target_type)
1530        WHEN 'GO' THEN BEGIN
1531	    IF target_entry > 0 THEN 
1532		CALL `sp_error_entry`('GO',target_entry); 
1533	    END IF;
1534            DELETE FROM `spell_script_target` WHERE `entry`=spell_id;
1535            INSERT INTO `spell_script_target`(`entry`,`type`,`targetEntry`) VALUES (spell_id,0,target_entry);
1536        END;
1537        WHEN 'NPC' THEN BEGIN
1538	    IF target_entry > 0 THEN 
1539		CALL `sp_error_entry`('NPC',target_entry); 
1540	    END IF;
1541            DELETE FROM `spell_script_target` WHERE `entry`=spell_id;
1542            INSERT INTO `spell_script_target`(`entry`,`type`,`targetEntry`) VALUES (spell_id,1,target_entry);
1543        END;
1544        WHEN 'DEAD_NPC' THEN BEGIN
1545	    CALL `sp_error_entry`('NPC',target_entry);
1546            DELETE FROM `spell_script_target` WHERE `entry`=spell_id;
1547            INSERT INTO `spell_script_target`(`entry`,`type`,`targetEntry`) VALUES (spell_id,2,target_entry);
1548        END;
1549        WHEN 'MINION' THEN BEGIN
1550	    CALL `sp_error_entry`('NPC',target_entry);
1551            DELETE FROM `spell_script_target` WHERE `entry`=spell_id;
1552            INSERT INTO `spell_script_target`(`entry`,`type`,`targetEntry`) VALUES (spell_id,3,target_entry);
1553        END;
1554        ELSE CALL INCORRECT_TARGET_TYPE;
1555    END CASE;
1556END //
1557
1558-- sp_get_go_lootid
1559CREATE PROCEDURE `sp_get_go_lootid`(IN gobjID MEDIUMINT(6),OUT gobjLootID INT(10))
1560BEGIN
1561/**
1562 * DEGREE: BASIC
1563 * TABLES AFFECTED: gameobject_template
1564 * PROCS USED: sp_error_entry
1565 *
1566 * Get the loot ID for a specified gameobject (data1 field). Must be a chest (type=3).
1567 *
1568 * gobjID - ID of the gameobject whose loot id is to be gathered
1569 * gobjLootID - variable to store the retrieved value in
1570 *
1571 * ex: CALL `sp_get_go_lootid`(195709,@Test);
1572 *     SELECT * FROM `gameobject_loot_template` WHERE `entry`=@Test;
1573 */
1574    CALL `sp_error_entry`('NPC',gobjID);
1575    SELECT `data1` FROM `gameobject_template` WHERE `entry`=gobjID AND `type`=3 INTO gobjLootID;
1576END//
1577
1578CREATE PROCEDURE `sp_set_npc_lootable_bylist`(IN npc_entry TEXT, IN on_off BOOLEAN)
1579BEGIN
1580/**
1581 * DEGREE: BASIC
1582 * TABLES AFFECTED: creature_template
1583 * PROCS USED:  sp_error_entry
1584 *
1585 * Disable /enable loot option on NPC from list
1586 *
1587 * npc_entry - ID of NPC from `creature_template`.`entry`
1588 * 
1589 * ex: CALL `sp_set_npc_lootable` ("257,258,259",1); -- makes creature of ID 257,258,259 (Kobold Worker) lootable
1590 * ex: CALL `sp_set_npc_lootable` ("257,258,259",0); -- makes creature of ID 257,258,259 (Kobold Worker) lootable
1591 */  
1592DECLARE remaining INT;
1593DECLARE min_entry INT;
1594
1595CALL sp_set_entry_list(npc_entry);
1596SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1597-- error check part
1598WHILE remaining > 0 DO
1599SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1600CALL sp_error_entry('NPC',min_entry);
1601DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1602SET remaining = remaining -1;
1603END WHILE;
1604CALL sp_set_entry_list(npc_entry);
1605SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1606
1607CALL sp_set_entry_list(npc_entry);
1608SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1609
1610WHILE remaining > 0 DO
1611SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1612CALL sp_error_entry('NPC',min_entry);
1613DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1614SET remaining = remaining -1;
1615END WHILE;
1616
1617CALL sp_set_entry_list(npc_entry);
1618SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1619
1620IF on_off = 1 THEN
1621WHILE remaining > 0 DO
1622SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1623UPDATE `creature_template` SET `dynamicflags`=`dynamicflags`&~1 WHERE `entry`= min_entry;
1624DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1625SET remaining = remaining -1;
1626END WHILE;
1627END IF;
1628IF on_off = 0 THEN
1629WHILE remaining > 0 DO
1630SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1631UPDATE `creature_template` SET `dynamicflags`=`dynamicflags`|1 WHERE `entry`= min_entry;
1632DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1633SET remaining = remaining -1;
1634END WHILE;
1635END IF;
1636
1637END //
1638
1639
1640CREATE PROCEDURE `sp_set_npc_lootable`(IN npc_entry INT(10), IN on_off BOOLEAN)
1641BEGIN
1642/**
1643 * DEGREE: BASIC
1644 * TABLES AFFECTED: creature_template
1645 * PROCS USED:  sp_error_entry
1646 *
1647 * Disable /enable loot option on NPC
1648 *
1649 * npc_entry - ID of NPC from `creature_template`.`entry`
1650 * 
1651 * ex: CALL `sp_set_npc_lootable` (257,1); -- makes creature of ID 257 (Kobold Worker) lootable
1652 * ex: CALL `sp_set_npc_lootable` (257,0); -- makes creature of ID 257 (Kobold Worker) not lootable
1653 */
1654
1655CALL sp_error_entry('NPC',npc_entry);
1656IF on_off = 1 THEN
1657UPDATE `creature_template` SET `dynamicflags`=`dynamicflags`&~1 WHERE `entry`= npc_entry;
1658ELSEIF on_off = 0 THEN
1659UPDATE `creature_template` SET `dynamicflags`=`dynamicflags`|1 WHERE `entry`= npc_entry;
1660END IF;
1661END //
1662
1663
1664CREATE PROCEDURE `sp_set_npc_civilian_bylist`(IN npc_entry TEXT, IN on_off BOOLEAN)
1665BEGIN
1666/**
1667 * DEGREE: BASIC
1668 * TABLES AFFECTED: creature_template
1669 * PROCS USED: sp_error_entry
1670 *
1671 * Update creature to ignore aggro
1672 * npc_entry - ID of NPC from `creature_template`.`entry`
1673 * 
1674 * ex: CALL `sp_set_npc_civilian_bylist` ("257,258,259",1); -- makes NPC with ID 257,258,259 ignore aggro
1675 * ex: CALL `sp_set_npc_civilian_bylist` ("257,258,259",1); -- makes NPC with ID 257,258,259 not ignore aggro
1676 */
1677  
1678DECLARE remaining INT;
1679DECLARE min_entry INT;
1680
1681CALL sp_set_entry_list(npc_entry);
1682SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1683-- error check part
1684WHILE remaining > 0 DO
1685SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1686CALL sp_error_entry('NPC',min_entry);
1687DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1688SET remaining = remaining -1;
1689END WHILE;
1690CALL sp_set_entry_list(npc_entry);
1691SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1692
1693CALL sp_set_entry_list(npc_entry);
1694SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1695
1696WHILE remaining > 0 DO
1697SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1698CALL sp_error_entry('NPC',min_entry);
1699DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1700SET remaining = remaining -1;
1701END WHILE;
1702
1703CALL sp_set_entry_list(npc_entry);
1704SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1705
1706IF on_off = 1 THEN
1707WHILE remaining > 0 DO
1708SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1709UPDATE `creature_template` SET `flags_extra`=`flags_extra`|2 WHERE `entry`= min_entry;
1710DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1711SET remaining = remaining -1;
1712END WHILE;
1713END IF;
1714IF on_off = 0 THEN
1715WHILE remaining > 0 DO
1716SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1717UPDATE `creature_template` SET `flags_extra`=`flags_extra`&~2 WHERE `entry`= min_entry;
1718DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1719SET remaining = remaining -1;
1720END WHILE;
1721END IF;
1722
1723END //
1724
1725
1726CREATE PROCEDURE `sp_set_npc_civilian`(IN npc_entry INT(10), IN on_off BOOLEAN)
1727BEGIN
1728/**
1729 * DEGREE: BASIC
1730 * TABLES AFFECTED: creature_template
1731 * PROCS USED: sp_error_entry
1732 *
1733 * Update creature to ignore aggro
1734 * npc_entry - ID of NPC from `creature_template`.`entry`
1735 * 
1736 * ex: CALL `sp_set_npc_civilian` (257,1); -- makes NPC with ID 257 ignore aggro
1737 * ex: CALL `sp_set_npc_civilian` (257,0); -- makes NPC with ID 257 not ignore aggro
1738 */
1739CALL sp_error_entry('NPC',npc_entry);
1740IF on_off = 1 THEN
1741UPDATE `creature_template` SET `flags_extra`=`flags_extra`|2 WHERE `entry`= npc_entry;
1742ELSEIF on_off = 0 THEN
1743UPDATE `creature_template` SET `flags_extra`=`flags_extra`&~2 WHERE `entry`= npc_entry;
1744END IF;
1745END //
1746
1747
1748CREATE PROCEDURE `sp_set_npc_attackable`(IN npc_entry INT, IN on_off BOOLEAN)
1749BEGIN
1750/**
1751 * DEGREE: BASIC
1752 * TABLES AFFECTED: creature_template
1753 * PROCS USED: sp_error_entry
1754 *
1755 * Toggle on / off attack option for NPC
1756 *
1757 * creature_entry - ID of NPC from `creature_template`.`entry`
1758 * 
1759 * ex: CALL `sp_set_npc_attackable` (257,1); -- enables attacking of creature with ID 257 (creature_template.entry - Kobold Worker)
1760 */
1761CALL sp_error_entry('NPC',npc_entry);
1762IF on_off = 1 THEN
1763UPDATE `creature_template` SET `unit_flags`=`unit_flags`|256 WHERE `entry`= npc_entry;
1764END IF;
1765IF on_off = 0 THEN
1766UPDATE `creature_template` SET `unit_flags`=`unit_flags`&~256 WHERE `entry`= npc_entry;
1767END IF;
1768END //
1769
1770
1771CREATE PROCEDURE `sp_set_npc_attackable_bylist`(IN npc_entry TEXT, IN on_off BOOLEAN)
1772BEGIN
1773/**
1774 * DEGREE: BASIC
1775 * TABLES AFFECTED: creature_template
1776 * PROCS USED: sp_error_entry
1777 *
1778 * Toggle on / off attack option for listed NPCs
1779 *
1780 * creature_entry - ID of NPC from `creature_template`.`entry`
1781 * 
1782 * ex: CALL `sp_set_npc_attackable` ("257,258,259",1); -- enables attacking of creature with ID 257, 258, 259 (creature_template.entry - Kobold Worker)
1783 * ex: CALL `sp_set_npc_attackable` ("257,258,259",0); -- disables attacking of creature with ID 257, 258, 259 (creature_template.entry - Kobold Worker)
1784 */
1785DECLARE remaining INT;
1786DECLARE min_entry INT;
1787CALL sp_set_entry_list(npc_entry);
1788SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1789-- error check part
1790WHILE remaining > 0 DO
1791SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1792CALL sp_error_entry('NPC',min_entry);
1793DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1794SET remaining = remaining -1;
1795END WHILE;
1796CALL sp_set_entry_list(npc_entry);
1797SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1798IF on_off = 1 THEN
1799WHILE remaining > 0 DO
1800SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1801UPDATE `creature_template` SET `unit_flags`=`unit_flags`|256 WHERE `entry`= min_entry;
1802DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1803SET remaining = remaining -1;
1804END WHILE;
1805END IF;
1806IF on_off = 0 THEN
1807WHILE remaining > 0 DO
1808SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1809UPDATE `creature_template` SET `unit_flags`=`unit_flags`&~256 WHERE `entry`= min_entry;
1810DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1811SET remaining = remaining -1;
1812END WHILE;
1813END IF;
1814END //
1815
1816
1817CREATE PROCEDURE `sp_set_npc_targetable`(IN npc_entry INT(10), IN on_off BOOLEAN)
1818BEGIN
1819/**
1820 * DEGREE: BASIC
1821 * TABLES AFFECTED: creature_template
1822 * PROCS USED:  none
1823 * FUNCTIONS USED: sp_error_entry
1824 *
1825 * Disable /enable targetting option on NPC
1826 *
1827 * npc_entry - ID of NPC from `creature_template`.`entry`
1828 * 
1829 * ex: CALL `sp_set_npc_targetable` (257,1); -- makes creature of ID 257 (Kobold Worker) targetable
1830 * ex: CALL `sp_set_npc_targetable` (257,0); -- makes creature of ID 257 (Kobold Worker) not targetable
1831 */
1832
1833CALL sp_error_entry('NPC',npc_entry);
1834IF on_off = 1 THEN
1835UPDATE `creature_template` SET `unit_flags`=`unit_flags`&~33554432 WHERE `entry`= npc_entry;
1836ELSEIF on_off = 0 THEN
1837UPDATE `creature_template` SET `unit_flags`=`unit_flags`|33554432 WHERE `entry`= npc_entry;
1838END IF;
1839END //
1840
1841
1842CREATE PROCEDURE `sp_set_npc_targetable_bylist`(IN npc_entry TEXT, IN on_off BOOLEAN)
1843BEGIN
1844/**
1845 * DEGREE: BASIC
1846 * TABLES AFFECTED: creature_template
1847 * PROCS USED:  sp_error_entry
1848 *
1849 * Disable /enable targetting option on NPC from list
1850 *
1851 * npc_entry - ID of NPC from `creature_template`.`entry`
1852 * 
1853 * ex: CALL `sp_set_npc_targetable_bylist` ("257,258,259",1); -- makes creature of ID 257,258,259 (Kobold Worker) targetable
1854 * ex: CALL `sp_set_npc_targetable_bylist` ("257,258,259",0); -- makes creature of ID 257,258,259 (Kobold Worker) not targetable
1855 */  
1856DECLARE remaining INT;
1857DECLARE min_entry INT;
1858
1859CALL sp_set_entry_list(npc_entry);
1860SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1861-- error check part
1862WHILE remaining > 0 DO
1863SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1864CALL sp_error_entry('NPC',min_entry);
1865DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1866SET remaining = remaining -1;
1867END WHILE;
1868CALL sp_set_entry_list(npc_entry);
1869SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1870
1871CALL sp_set_entry_list(npc_entry);
1872SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1873
1874IF on_off = 1 THEN
1875WHILE remaining > 0 DO
1876SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1877UPDATE `creature_template` SET `unit_flags`=`unit_flags`&~33554432 WHERE `entry`= min_entry;
1878DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1879SET remaining = remaining -1;
1880END WHILE;
1881END IF;
1882IF on_off = 0 THEN
1883WHILE remaining > 0 DO
1884SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1885UPDATE `creature_template` SET `unit_flags`=`unit_flags`|33554432 WHERE `entry`=  min_entry;
1886DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1887SET remaining = remaining -1;
1888END WHILE;
1889END IF;
1890END //
1891
1892
1893
1894CREATE PROCEDURE `sp_set_item_money` (IN item_entry INT, IN min_money INT, IN max_money INT)
1895BEGIN
1896/**
1897 * DEGREE: BASIC
1898 * TABLES AFFECTED: item_template
1899 * PROCS USED:  sp_error_entry
1900 *
1901 * set min / max money loot for item
1902 * 
1903 * ex: CALL `sp_set_item_money` (25,10,100); -- set max money loot to 100 and min money loot to 10 for item with entry = 25
1904 *
1905 */
1906CALL sp_error_entry('ITEM',item_entry);
1907UPDATE item_template SET minMoneyLoot=min_money, maxMoneyLoot=max_money WHERE entry = item_entry;
1908END //
1909
1910
1911
1912CREATE PROCEDURE `sp_set_item_money_bylist`(IN item_entry TEXT, IN min_money INT, IN max_money INT)
1913BEGIN
1914/**
1915 * DEGREE: BASIC
1916 * TABLES AFFECTED: item_template
1917 * PROCS USED:  sp_error_entry
1918 *
1919 * set min / max money loot for item in list
1920 * 
1921 * ex: CALL `sp_set_item_money` ("25,26,27",10,100); -- set max money loot to 100 and min money loot to 10 for item with entry = 25 and 26 and 27
1922 *
1923 */
1924
1925DECLARE remaining INT;
1926DECLARE min_entry INT;
1927
1928CALL sp_set_entry_list(npc_entry);
1929SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1930-- error check part
1931WHILE remaining > 0 DO
1932SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1933CALL sp_error_entry('ITEM',min_entry);
1934DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1935SET remaining = remaining -1;
1936END WHILE;
1937CALL sp_set_entry_list(npc_entry);
1938SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1939
1940CALL sp_set_entry_list(npc_entry);
1941SET remaining = (SELECT COUNT(`value`) FROM tdb_entry_list);
1942
1943WHILE remaining > 0 DO
1944SET min_entry = (SELECT MIN(`value`) FROM tdb_entry_list);
1945UPDATE item_template SET minMoneyLoot=min_money, maxMoneyLoot=max_money WHERE entry = item_entry;
1946DELETE FROM tdb_entry_list WHERE `value`=min_entry;
1947SET remaining = remaining -1;
1948END WHILE;
1949
1950END //
1951
1952
1953
1954-- -------------------- --
1955--  AVERAGE PROCEDURES  --
1956-- -------------------- --
1957
1958CREATE PROCEDURE `sp_eai_kill_ondeath`(IN npc_entry INT(10), trigger_id INT(10))
1959BEGIN
1960/**
1961 * DEGREE: AVERAGE
1962 * TABLES AFFECT: creature_template, creature_ai_scripts
1963 * PROCS USED: sp_error_entry, sp_eai_select_id
1964 *
1965 * Create eAI script for NPC to give credit on death
1966 *
1967 * npc_entry - ID of NPC from `creature_template`.`entry`
1968 * trigger_id - ID of trigger NPC that needs to be killed for quest objective
1969 * 
1970 * ex: CALL `sp_eai_kill_ondeath`(46,257); -- NPC of ID 46 (Murloc Forager) when killed will give credit for killing NPC with ID 257 (Kobold Worker)
1971 */
1972    CALL `sp_error_entry`('NPC',npc_entry);
1973    UPDATE `creature_template` SET `AIName`= 'EventAI' WHERE `entry`=npc_entry; -- enable eAI
1974
1975    -- EAI reacting on spellhit, gives credit for kill and despawns
1976    CALL `sp_eai_select_id`(npc_entry, @event_id);
1977    INSERT INTO `creature_ai_scripts` (`id`,`creature_id`,`event_type`,`event_inverse_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`action1_type`,`action1_param1`,`action1_param2`,`action1_param3`,`action2_type`,`action2_param1`,`action2_param2`,`action2_param3`,`action3_type`,`action3_param1`,`action3_param2`,`action3_param3`,`comment`) VALUES 
1978        (@event_id,npc_entry,6,0,100,0,0,0,0,0,33,trigger_id,6,0,23,1,0,0,0,0,0,0, 'Stored procedures eAI: quest - kill trigger on NPC death');
1979END //
1980
1981CREATE PROCEDURE `sp_eai_cast_onspawn` (IN npc_entry INT(10), spell_id MEDIUMINT(6))
1982BEGIN
1983/**
1984 * DEGREE: AVERAGE
1985 * TABLES AFFECT: creature_template, creature_ai_scripts
1986 * PROCS USED: sp_error_entry, sp_eai_select_id
1987 *
1988 * Create eAI script for NPC to cast spell on self upon spawn
1989 *
1990 * npc_entry - ID of NPC from `creature_template`.`entry`
1991 * spell_ID - ID of spell we want to set target for
1992 * 
1993 * ex: CALL `sp_eai_cast_onspawn`(257,4444); -- Creature of ID 257 (Kobold Worker) will cast spell of ID 4444 on self when spawned
1994 */
1995    CALL `sp_error_entry`('NPC',npc_entry);
1996
1997    UPDATE `creature_template` SET `AIName`= 'EventAI' WHERE `entry`=npc_entry; -- enable eAI
1998    
1999    CALL `sp_eai_select_id`(npc_entry,@event_id);
2000    
2001    INSERT INTO `creature_ai_scripts` (`id`,`creature_id`,`event_type`,`event_inverse_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`action1_type`,`action1_param1`,`action1_param2`,`action1_param3`,`action2_type`,`action2_param1`,`action2_param2`,`action2_param3`,`action3_type`,`action3_param1`,`action3_param2`,`action3_param3`,`comment`) VALUES 
2002        (@event_id,npc_entry,11,0,100,0,0,0,0,0,11,spell_id,0,0,0,0,0,0,0,0,0,0, 'Stored procedures eAI: NPC cast spell on self');
2003END //
2004
2005CREATE PROCEDURE `sp_eai_spawn_spellhit` (IN npc_entry INT(10),IN spell_id MEDIUMINT(6),IN spawn_id INT(10),IN despawn_time INT(10))
2006BEGIN
2007/**
2008 * DEGREE: AVERAGE
2009 * TABLES AFFECT: creature_template, creature_ai_scripts
2010 * PROCS USED: sp_error_entry, sp_eai_select_id
2011 *
2012 * Create eAI script for NPC to summon another NPC upon spellhit
2013 *
2014 * npc_entry - ID of NPC from `creature_template`.`entry`
2015 * spawn_id - creature that will be spawned at current location of NPC
2016 * spell_ID - ID of spell which will run the event
2017 * despawn_time - time after which summoned mob despawns in miliseconds
2018 * 
2019 * ex: CALL `sp_eai_spawn_spellhit` (1234,4444,1235,100000); -- summon c1235 when c1234 is hit with s4444 (c1235 depsawns after 10s)
2020 */
2021    CALL `sp_error_entry`('NPC',npc_entry);
2022    CALL `sp_error_entry`('NPC',spawn_id);
2023
2024    UPDATE `creature_template` SET `AIName`= 'EventAI' WHERE `entry`=npc_entry; -- enable eAI
2025    CALL `sp_eai_select_id`(npc_entry,@event_id); -- select event ID
2026
2027    INSERT INTO `creature_ai_scripts` (`id`,`creature_id`,`event_type`,`event_inverse_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`action1_type`,`action1_param1`,`action1_param2`,`action1_param3`,`action2_type`,`action2_param1`,`action2_param2`,`action2_param3`,`action3_type`,`action3_param1`,`action3_param2`,`action3_param3`,`comment`) VALUES 
2028        (@event_id,npc_entry,8,0,100,1,spell_id,-1,0,0,32,spawn_id,6,0,41,0,0,0,0,0,0,0, 'Stored procedures eAI: quest - summon mob on spellcast');
2029    INSERT INTO `creature_ai_scripts` (`id`,`creature_id`,`event_type`,`event_inverse_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`action1_type`,`action1_param1`,`action1_param2`,`action1_param3`,`action2_type`,`action2_param1`,`action2_param2`,`action2_param3`,`action3_type`,`action3_param1`,`action3_param2`,`action3_param3`,`comment`) VALUES 
2030        (@event_id+1,spawn_id,1,1,100,0,despawn_time,despawn_time,despawn_time,despawn_time,41,0,0,0,0,0,0,0,0,0,0,0, 'Stored procedures eAI: despawn after defined time');
2031END //
2032
2033
2034CREATE PROCEDURE `sp_eai_quest_credit_on_spellhit` (IN npc_entry INT(10), spell_id MEDIUMINT(6), trigger_id INT(10), despawn_time INT(10))
2035BEGIN
2036/**
2037 * DEGREE: AVERAGE
2038 * TABLES AFFECT: creature_template, creature_ai_scripts
2039 * PROCS USED: sp_error_entry, sp_error_trigger, sp_eai_select_id
2040 *
2041 * Create eAI script for NPC to give credit on spellhit
2042 *
2043 * spell_id - ID of spell we want to set target for
2044 * npc_entry - ID of NPC from `creature_template`.`entry`
2045 * trigger_id - ID of trigger NPC that needs to be killed for quest objective
2046 * despawn_time - time before NPC despawns in miliseconds
2047 * 
2048 * ex: CALL `sp_eai_quest_credit_on_spellhit` (257,4444,1235,10000); -- 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
2049 */
2050    CALL `sp_error_entry`('NPC',npc_entry);
2051    CALL `sp_error_trigger` (trigger_id);
2052    UPDATE `creature_template` SET `AIName`= 'EventAI' WHERE `entry`=npc_entry; -- enable eAI
2053    -- EAI reacting on spellhit, gives credit for kill and despawns
2054        CALL `sp_eai_select_id` (npc_entry, @event_id);
2055    INSERT INTO `creature_ai_scripts` (`id`,`creature_id`,`event_type`,`event_inverse_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`action1_type`,`action1_param1`,`action1_param2`,`action1_param3`,`action2_type`,`action2_param1`,`action2_param2`,`action2_param3`,`action3_type`,`action3_param1`,`action3_param2`,`action3_param3`,`comment`) VALUES 
2056        (@event_id,npc_entry,8,0,100,1,spell_ID,-1,0,0,33,trigger_ID,6,0,23,1,0,0,0,0,0,0, 'Stored procedures eAI: quest - kill trigger on spellcast');
2057    INSERT INTO `creature_ai_scripts` (`id`,`creature_id`,`event_type`,`event_inverse_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`action1_type`,`action1_param1`,`action1_param2`,`action1_param3`,`action2_type`,`action2_param1`,`action2_param2`,`action2_param3`,`action3_type`,`action3_param1`,`action3_param2`,`action3_param3`,`comment`) VALUES 
2058        (@event_id+1,npc_entry,1,1,100,0,despawn_time,despawn_time,despawn_time,despawn_time,41,0,0,0,0,0,0,0,0,0,0,0, 'Stored procedures eAI: despawn after defined time');
2059END //
2060DELIMITER ;
2061
2062-- == =================== ==
2063-- ==   UPDATEPACK 34     ==
2064-- == =================== ==
2065
2066-- Fix a procedure
2067DROP PROCEDURE IF EXISTS `sp_get_go_lootid`;
2068
2069-- Temporarily change the delimiter
2070DELIMITER ||
2071CREATE PROCEDURE `sp_get_go_lootid`(IN gobjID MEDIUMINT(6),OUT gobjLootID INT(10))
2072BEGIN
2073        CALL `sp_error_entry`('GO',gobjID);
2074        SELECT `data1` FROM `gameobject_template` WHERE `entry`=gobjID AND `type`=3 INTO gobjLootID;
2075END||
2076
2077-- Restore delimiter
2078DELIMITER ;
2079
2080-- =====================
2081-- ==  UPDATEPACK 36  ==
2082-- =====================
2083
2084DELIMITER ||
2085DROP PROCEDURE IF EXISTS `sp_set_npc_trigger`||
2086CREATE PROCEDURE `sp_set_npc_trigger`(IN npc_entry INT(10), IN on_off BOOLEAN)
2087BEGIN
2088  CALL `sp_error_entry`('NPC',npc_entry);
2089
2090  IF on_off=1 THEN
2091        UPDATE `creature` SET `spawndist`=0,`MovementType`=0 WHERE `id`=npc_entry;
2092        UPDATE `creature_template` SET `flags_extra`=`flags_extra`|128 WHERE `entry`=npc_entry;         
2093  ELSE
2094        UPDATE `creature_template` SET `flags_extra`=`flags_extra`&~128 WHERE `entry`=npc_entry;
2095  END IF;
2096END||
2097
2098DROP PROCEDURE IF EXISTS `sp_set_npc_trigger_bylist`||
2099CREATE PROCEDURE `sp_set_npc_trigger_bylist`(IN entry_list LONGTEXT,IN on_off BOOLEAN)
2100BEGIN
2101
2102  CALL `sp_set_entry_list` (entry_list,null);
2103
2104  IF on_off=1 THEN
2105        UPDATE `creature` SET `spawndist`=0,`MovementType`=0 WHERE `id` IN (SELECT * FROM `tdb_entry_list`);
2106        UPDATE `creature_template` SET `flags_extra`=`flags_extra`|128 WHERE `entry` IN (SELECT * FROM `tdb_entry_list`);       
2107  ELSE
2108        UPDATE `creature_template` SET `flags_extra`=`flags_extra`&~128 WHERE `entry` IN (SELECT * FROM `tdb_entry_list`);
2109  END IF;
2110
2111  DROP TABLE `tdb_entry_list`;
2112END||
2113DELIMITER ;