EQEmulator Forums - Database Scripts - Question About Good Practise

Hi all. I have a question about good practise when writing mysql scripts to update the database. Let's say I want to write a script that makes thousands of changes to a table or multiple tables. An example of this would be if I wanted to change the levels that classes can use their spells or something. If I have a script a that contains entries like:

Code:

UPDATE spells SET behaviour1=value1,behaviour2=value2 WHERE spellid=123;

UPDATE spells SET behaviour1=value3,behaviour2=value4 WHERE spellid=234;

... then if the schema changes later on I could run into problems and have to edit thousands of lines. It also be more difficult to maintain because I won't know what all the IDs represent etc. It feels intuitive to me to write a stored proc to handle all the database calls for me. Something like:

Code:

CREATE PROCEDURE UPDATE_THE_SPELLS(

        IN spellShortName VARCHAR(255),

        IN behaviour1 VARCHAR(255),

        IN behaviour2 VARCHAR(255)

)

BEGIN

        SET @spellid = (SELECT id FROM spellIdTable WHERE shortName=@spellShortName);

        UPDATE spells SET behaviour1=@behaviour1,behaviour2=@behaviour2 WHERE spellid=@spellid;

END



UPDATE_THE_SPELLS(myCoolSpell,value1,value2);

UPDATE_THE_SPELLS(anotherCoolSpell,value3,value4);

So, in future if the schema changes all I have to do is update the stored proc rather than thousands of UPDATE or INSERT lines. Also, each line should be more readable because I can use names rather than IDs.

Is this good practise? Is there a better way to go about this?