MySQL ALTER PROCEDURE Syntax
I usually use SQLYog to write any stored procedures for MySQL. Whenever you alter a procedure the editor essentially generates SQL to drop and then recreate it.
DELIMITER $$ USE `db`$$ DROP PROCEDURE IF EXISTS `my_proc`$$ CREATE DEFINER=`root`@`%` PROCEDURE `my proc`() MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT 'Just an example proc.' BEGIN # // proc defintition END$$ DELIMITER ;
I’ve always wondered why it did this as opposed to using the ALTER PROCEDURE Syntax. I attempted to use this syntax but couldn’t get it to parse whatever I did. As usual the manual explains all;
This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using DROP PROCEDURE and CREATE PROCEDURE. source
Okay! The alter procedure statement is virtually useless to us then! The current method of dropping and then recreating routines to modify them is not atomic and can cause problems on production systems. The stored procedure is temporarily unavailable after the first drop statement so this might freak out any other thread calling it at that moment.
I admit this isn’t a massive problem, and I assume it wouldn’t be that difficult to implement, but it’s something you’d expect DBMS vendors to put in place for stored procedures. I enjoy working with MySQL but some days you can get irrated by a number of these little issues!