How to rename a stored procedure in MySQL?

If you have a situation in hand where you need to rename a STORED PROCEDURE in MySQL and not DROP it then there are 2 ways I could figure out to tackle it. Before I start describing the methods I would like to state why I needed to rename a STORED PROCEDURE in MySQL?

I always have a STORED PROCEDURE named as “x” in all my projects databases where I write temporary code to tackle a particular problem and when the experiment is successful I create a new STORED PROCEDURE and copy & paste code from “x” PROCEDURE. I was following this process for quite some time happily, but then came a situation where it looked time consuming and I needed a faster method!

I found Method 1 by Google-ing and Method 2 was my sudden discovery!

Method 1: Rename a STORED PROCEDURE

SET name = ‘<new_proc_name>’,
specific_name = ‘<new_proc_name>’
WHERE db = ‘<database>’ AND
name = ‘<old_proc_name>’;

So if I want to rename my “x” PROCEDURE to “new_x” the statement would look like the following:

UPDATE `mysql`.`proc`
SET name = ‘new_x’,
specific_name = ‘new_x’
WHERE db = ‘db_mydb’ AND
name = ‘x’;

Please Also note: If you have granted privileges to users for this procedure you will need to update the procedure name in procs_priv as well.

UPDATE `mysql`.`procs_priv`
SET Routine_name = ‘<new_proc_name>’
WHERE Db = ‘<database>’ AND
Routine_name = ‘<old_proc_name>’;

FLUSH PRIVILEGES;

You can execute the above queries from MySQL GUI Tool like MySQL Query Browser or MySQL Command Prompt.

Method 2: Rename a STORED PROCEDURE with a backup

This method is my sudden discovery and I figured it before I found Method 1 by Google-ing and I execute it in MySQL Query Browsers (needless to say it is still my favorite tool to operate MySQL!)

– Select the PROCEDURE “x” and press F2 or right click and select “Edit Procedure”

– PROCEDURE opens in the Query Browser Editor

– Modify DROP PROCEDURE IF EXISTS `db_mydbs`.`x` to DROP PROCEDURE IF EXISTS `db_mydbs`.`new_x`

– Modify CREATE PROCEDURE `db_mydb`.`x` () to CREATE PROCEDURE `db_mydb`.`new_x` ()

– Click to Continue button

The above steps create a new PROCEDURE named “new_x” and also keeps the PROCEDURE named “x” intact.

I generally use Method 2 because it lets me keep my handy “x” PROCEDURE intact and create another PROCEDURE with the same code in a mouse click. Method 2 may not sound very scientific or authentic but since MySQL respond to it quite nicely I guess it is not as bad :).

© Kamalika Guha Roy

Follow