Rename MySQL Stored Procedures

I’ve previously blogged about the limitations of MySQL Alter Procedure Syntax and I came across a thread on the MySQL forums with a possible solution. I thought it might be handy to wrap this up into a stored procedure akin to SQL Server’s sp_rename.

This procedure will allow you to easily rename MySQL Stored Procedures in any database. Please be aware that this does update the MySQL system tables and has only had minimal testing. As with all tips you find on the Internet please use with caution!

DELIMITER $$
 
USE `mysql`$$
 
DROP PROCEDURE IF EXISTS `mysp_rename_proc`$$
 
CREATE DEFINER=`root`@`%` PROCEDURE `mysp_rename_proc`(IN p_proc_name VARCHAR(64), IN p_new_name VARCHAR(64), IN p_db VARCHAR(64))
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Use to rename stored procedures.'
BEGIN
    proc: BEGIN
	# A few tests to see if the input is sensible
	IF CHARACTER_LENGTH(IFNULL(p_proc_name, '')) = 0 OR CHARACTER_LENGTH(IFNULL(p_new_name, '')) = 0
		OR CHARACTER_LENGTH(IFNULL(p_db, '')) = 0 THEN
	BEGIN
		SELECT 'Error: One of more of the input parameters is zero in length.' AS Error;
		LEAVE proc; 
	END;
	ELSEIF (SELECT COUNT(*)
		 FROM mysql.proc
		 WHERE `name` = p_proc_name
		 AND `type` = 'PROCEDURE'
		 AND db = p_db) = 0 THEN
	BEGIN
		SELECT 'Error: The procedure specified in p_proc_name does not exist in this database.' AS Error;
		LEAVE proc;
	END;
	ELSEIF (SELECT COUNT(*)
		 FROM mysql.proc
		 WHERE `name` = p_new_name
		 AND `type` = 'PROCEDURE'
		 AND db = p_db) = 1 THEN
	BEGIN
		SELECT 'Error: Unable to rename the procedure specified in p_proc_name as it already exists in this database.' AS Error;
		LEAVE proc;
	END;
	END IF;
 
	# Rename the proc
	UPDATE `mysql`.`proc`
	SET `name` = p_new_name,
	specific_name = p_new_name
	WHERE db = p_db 
	AND `name` = p_proc_name
	AND `type` = 'PROCEDURE';
 
	# Update any associated privileges
	UPDATE `mysql`.`procs_priv`
	SET Routine_name = p_new_name
	WHERE db = p_db 
	AND Routine_name = p_proc_name
	AND Routine_type = 'PROCEDURE';
 
	# Check update rowcount to see if privileges need to be flushed
	IF(SELECT ROW_COUNT()) > 0 THEN
	BEGIN
		FLUSH PRIVILEGES;
	END;
	END IF;
 
    END proc;
    END$$
 
DELIMITER ;

Usage is as follows;

CALL mysp_rename_proc('usp_proc', 'usp_new_proc_name', 'database_proc_exists_in');

As the thread poster mentions the Stored Procedure is callable by it’s old name as well as the new one until you reconnect. There doesn’t seem to be any suitable FLUSH command to resolve this.

If you liked this you may like;

MySQL Clone of SP_SpaceUsed

MySQL Clone of SP_MsForEachTable


Leave a Reply

Current ye@r *