MySQL Alter Stored Procedure

Sometimes you may need to edit stored procedure or alter stored procedure in MySQL. Here’s how to edit stored procedure using MySQL ALTER PROCEDURE statement.

MySQL Alter Stored Procedure

Here are the steps to alter stored procedure in MySQL using ALTER PROCEDURE statement.

Let’s say you have the following stored procedure in MySQL.

mysql> DELIMITER //
       CREATE PROCEDURE get_orders()
       BEGIN
       SELECT *  FROM orders;
       END //

      DELIMITER ;

mysql> call get_orders;
+----+------------+--------+----------+
| id | order_date | amount | sequence |
+----+------------+--------+----------+
|  1 | 2020-08-01 |    250 |        1 |
|  2 | 2020-08-02 |    125 |        4 |
|  3 | 2020-08-03 |    300 |        9 |
+----+------------+--------+----------+

Bonus Read : MySQL List All Procedures

How to Edit Stored Procedure in MySQL Command Line

Although MySQL provides ALTER PROCEDURE statement, it does not allow you to change the body or parameters of stored procedure. It only allows you to change the characteristics of stored procedure

Here’s the syntax of ALTER PROCEDURE

ALTER PROCEDURE proc_name [characteristic ...]

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

Here’s an example to add a comment using ALTER PROCEDURE query.

ALTER PROCEDURE get_orders
COMMENT 'test comment';

Bonus Read : MySQL Reset Root Password

However, if you want to change the query, body or parameters of stored procedure in MySQL command line, then you need to DROP PROCEDURE and then CREATE PROCEDURE with new definition. Here’s an example to update the query of get_orders stored procedure defined above.

mysql> drop procedure get_orders;

mysql> DELIMITER //
          CREATE PROCEDURE get_orders()
            BEGIN
            SELECT order_date, amount  FROM orders;
            END //
       DELIMITER ;

mysql> call get_orders;
+------------+--------+
| order_date | amount |
+------------+--------+
| 2020-08-01 |    250 |
| 2020-08-02 |    125 |
| 2020-08-03 |    300 |
+------------+--------+

Hopefully, now you can alter stored procedure in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.