Alter Stored Procedures

 Alter Stored Procedures

Summary: in this tutorial, you will learn how to alter an existing stored procedure in the database using MySQL Workbench.

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

Fortunately, MySQL does not have any statement that allows you to directly modify the parameters and body of the stored procedure.

To make such changes, you must drop ad re-create the stored procedure using the DROP PROCEDURE and CREATE PROCEDURE statements.

Altering a stored procedure using MySQL Workbench

MySQL Workbench provides you with a good tool that allows you to change a stored procedure quickly.

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 ;

Suppose that you want to get the total amount by a given sales order. So you need to add a parameter and change the code in the stored procedure.

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

MySQL Workbench will open a new tab that contains the definition of the stored procedure.

Third, make the changes and click the Apply button.

MySQL Workbench will display a SQL Script review window.

As you can see, it uses a sequence of DROP PROCEDURE and CREATE PROCEDURE statements to carry 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.

In this tutorial, you have learned how to alter a stored procedure using MySQL Workbench.