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.
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.
After creation, we will insert some data into the table.
The table data will look like this.
Creating the Stored Procedure
Now, let’s create a procedure.
DELIMITER $$ CREATE PROCEDURE fetchData() BEGIN SELECT * FROM accounts; END$$ DELIMITER ;
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.
- 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.
- The new prompt will open again as shown below.
- 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);
After executing the above statements one by one, we get the following result.
As you can see, we have received the expected data from the procedure.
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.