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:
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.
SET Routine_name = ‘<new_proc_name>’
WHERE Db = ‘<database>’ AND
Routine_name = ‘<old_proc_name>’;
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
Leave a Reply