How to Alter a Procedure in MySQL

By /
Modify Stored Procedures In Mysql

Hey guys, in this tutorial, we will learn to alter the procedures in MySQL with simple steps. We will be seeing an example for a demonstration with all the necessary snapshots so that you will understand it better and won’t face any trouble.

Also read: Create MySQL Stored Procedure [With Examples]

Before We Start

The most important thing before you proceed to alter the procedure topic is to be clear that MySQL doesn’t provide any functionality to modify an existing procedure. However, MySQL does provide the ALTER PROCEDURE statement to modify the characteristics of the procedure and not the body of it.

In order to modify the stored procedure body, you must drop the procedure first and re-write it with the new statements. Although, MySQL workbench provides the alter stored procedure option where the procedure is dropped first using the DROP PROCEDURE statement and then created again in one go. You will understand it as we dive into the practical.

We will learn to alter the stored procedure using MySQL workbench in this tutorial.

Alter the Stored Procedure Using MySQL Workbench

The MySQL Workbench is a good visual database design tool for a MySQL database. We can easily create, modify and drop the stored procedure using MySQL workbench.

Creating the Table

We will first create a simple stored procedure to fetch the data from a table. We will use the table of the following schema.

Accounts Table
Accounts Table

After creation, we will insert some data into the table.

The table data will look like this.

Accounts Table Data 1
Accounts Table Data

Creating the Stored Procedure

Now, let’s create a procedure.

DELIMITER $$
CREATE PROCEDURE fetchData()
BEGIN
SELECT * FROM accounts;
END$$
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Here, we have used a simple SELECT statement to fetch all data from a table.

Modifying the Stored Procedure

Now suppose we want to display the data of a particular customer from the table. So, we will need to pass an id of the customer from the procedure call.

Let’s modify it from the MySQL workbench.

  • Right-Click on the procedure name and click on the “alter stored procedure” option as shown below.
Right Click On Stored Procedure Name
Right Click On Stored Procedure Name
  • The SQL editor will open with the stored procedure definition.
  • Now make all the changes that you want as shown in the image below and click on the apply button.
Modify Stored Procedure Body
Modify Stored Procedure Body
  • The new prompt will open again as shown below.
Apply The Modification
Apply The Modification
  • You can see, the stored procedure is dropped first using the DROP PROCEDURE statement. And then created again with the modified body.
  • Review the changes and click on the apply button to save the changes.
  • You can see the success prompt. Click on the finish button to return to the MYSQL workbench home screen.

We have done the changes successfully. Let’s test it to check it works correctly!

Testing the Modified Stored Procedure

We have modified the stored procedure and specified a parameter to it. Let’s call the procedure by passing a parameter.

CALL fetchData(1);
CALL fetchData(3);Code language: SQL (Structured Query Language) (sql)

After executing the above statements one by one, we get the following result.

Call Modified Stored Procedure
Call Modified Stored Procedure

As you can see, we have received the expected data from the procedure.

Conclusion

In this tutorial, we learned how to modify an existing stored procedure using MySQL workbench. If you are using MySQL CLI, you must drop the procedure first and type everything again. On the other hand, the same steps are followed in the MySQL workbench as well, but you don’t need to write each statement again as you get an editor to edit the existing procedure.