MySQL ALTER PROCEDURE Statement

Summary: in this tutorial, you will learn how to use the MySQL ALTER PROCEDURE statement to modify an existing stored procedure in the MySQL database.

Introduction to MySQL ALTER PROCEDURE statement

To change the characteristics of a stored procedure, you use the ALTER PROCEDURE statement:

ALTER PROCEDURE sp_name [characteristic ...]Code language: CSS (css)

In this syntax:

• First, specify the stored procedure name that you want to modify after the ALTER PROCEDURE keywords. Notice that you don’t use parentheses () after the stored procedure name (sp_name).
• Second, specify the characteristics you want to change after the stored procedure name.

Here are the characteristics of a stored procedure that you can change with the ALTER PROCEDURE statement:

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}Code language: JavaScript (javascript)

Sometimes, you may want to modify a stored procedure by adding or removing parameters or even changing its body.

However, it’s important to note that the ALTER PROCEDURE statement does not support this. To implement such modifications, you need to:

• First, drop the stored procedure using the DROP PROCEDURE statement.
• Second, recreate the stored procedure using the CREATE PROCEDURE statement.

It’s important to back up the stored procedure before making changes so that you can restore it if things go wrong.

MySQL ALTER PROCEDURE statement example

First, connect to the classicmodels sample database:

mysql -u root -p classicmodels

Second, create a new stored procedure named GetAllEmployees() that returns all employees:

DELIMITER //

CREATE PROCEDURE GetEmployees()
BEGIN
	SELECT * FROM employees;
END // 

DELIMITER ;Code language: JavaScript (javascript)

Third, show the stored procedure definition:

SHOW CREATE PROCEDURE GetEmployees\G

Output:

*************************** 1. row ***************************
           Procedure: GetEmployees
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `GetEmployees`()
BEGIN
        SELECT * FROM employees;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_bin
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Fourth, add a comment to the stored procedure GetEmployees using the ALTER PROCEDURE statement:

ALTER PROCEDURE GetEmployees COMMENT "Get employees";Code language: JavaScript (javascript)

Finally, show the stored procedure definition to see whether the comments have been added successfully or not:

 SHOW CREATE PROCEDURE GetEmployees\G

Output:

*************************** 1. row ***************************
           Procedure: GetEmployees
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `GetEmployees`()
    COMMENT 'Get employees'
BEGIN
        SELECT * FROM employees;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_bin
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The output indicates we have added the comment successfully:

COMMENT 'Get employees'Code language: JavaScript (javascript)

Modifying a stored procedure using MySQL Workbench

We will show you how to use MySQL Workbench to drop and recreate a stored procedure.

First, create a stored procedure that returns the total amount of all sales orders:

DELIMITER $$

CREATE PROCEDURE GetOrderAmount()
BEGIN
    SELECT 
        SUM(quantityOrdered * priceEach) 
    FROM orderDetails;
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Suppose you want to retrieve the total amount for a given sales order. In this case, you need to add a parameter and modify the code in the stored procedure.

Second, right-click the stored procedure that you want to modify and select Alter Stored Procedure…

MySQL Workbench will open a new tab containing the stored procedure’s definition.

Third, make the necessary changes and click the Apply button.

MySQL Workbench will present an SQL Script review window.

The picture (1) and (2) indicates that MySQL Workbench uses a sequence of DROP PROCEDURE and CREATE PROCEDURE statements to implement the modification.

Fourth, click the Apply button to execute the script.

MySQL Workbench will display a window that shows the status of the script execution.

Finally, click the Finish button to complete the change.

Summary

• Use the MySQL ALTER PROCEDURE statement to modify the characteristics of a stored procedure.
• Drop and recreate a stored procedure to modify its parameters and body.