Create Stored Procedure Via Workbench:
Open the newly installed MySQL Workbench 8.0 and connect it with the root localhost database.
Inside the Workbench, there is a Navigator bar. Beneath this Navigator bar, there are a set of different functions of MySQL. It includes a list of databases, tables, stored procedures, and many more, as you can see from the image.
When you explore the ‘Tables’ option, you will find list of tables in a grid view. As shown below, we have a table ‘social’.
Right-click on the option ‘Stored Procedure’ and click on the ‘Create Stored Procedure’ option as shown in the picture.
A new window will be opened, as seen in the photo below. You can change the name of the stored procedure in the query area by removing the value in inverted commas.
Now, you can edit this query according to your desire. We name the stored procedure as ‘detail’ while getting the data from a table ‘social’ where its value in the column ‘Website’ is equivalent to ‘Instagram.’ This means that store procedure, upon execution, will only show those records from this table where the ‘website’ is ‘Instagram.’ Click on the Apply button.
You will get a preview screen where you can change or modify your query if required. You can see a full syntax of the newly created Stored procedure via the Workbench. Click on the Apply button to execute it.
If there are no mistakes in the query, it will work correctly as shown below. Tap on the Finish button.
When you look at the stored procedures option and refresh it, it will show you a newly created procedure.
When you execute this procedure, it will show you the only records having a column value of ‘Website’ is ‘Instagram’ as below.
Create Stored Procedure via Command-Line Shell:
Open the command-line client shell of MySQL 8.0 and enter the password of MySQL underneath.
Suppose we have a table named ‘record’ in the database schema ‘data’. Let’s check the records of it using the SELECT command as follows:
Use the ‘use data’ command to use the database where you want to add the stored procedure.
Example 01: Stored Procedure without Parameters
To create a procedure without a parameter, you have to create it with a CREATE PROCEDURE command preceded by the keyword ‘DELIMITER’. Then, we create a procedure named ‘Filter’ without parameters. It fetches all the records from the table ‘record’ where the column ‘Country’ has ‘ia’ at the end of its values. The process should be ended with the keyword ‘END’.
We will be using the CALL clause to execute the stored procedure in the command-line. Upon running the CALL command, we have the below results. You can see that the query has to fetch only those records where the column ‘Country’ has ‘ia’ at the end of its values.
Example 02: Stored Procedure with Single Parameter
It’s high time to generate a procedure with a single parameter. For this, use the CREATE PROCEDURE query headed by the keyword ‘DELIMITER.’ So, we have to create a procedure ‘Rec’ that takes one value as an input argument in which in this example is variable ‘Var1’ by a user in its parameters. Begin the procedure by ‘BEGIN’ keyword. The SELECT statement is used to fetch all the records from the table ‘record’ where the column ‘Name’ has the same value as in ‘Var1’. This is a matching of records. End the stored procedure with the keyword ‘END’ followed by ‘&&’ signs.
‘
Run the DELIMITER query first for the stored procedure to get ready. After that, execute the CALL query followed by the procedure name and its input argument value in the braces. You just have to run the below-shown command, and you will get the outcomes. As we have provided, ‘Zafar’ in our parameters, that is why, after the comparison, we have got this result.
Example 03: Stored Procedure with Multiple Parameters
Let’s see how the procedure functions when it has been provided with multiple parameters. Don’t forget to use the keyword ‘DELIMITER’ along with ‘&&’ signs. Use the CREATE PROCEDURE command to create a procedure ‘New’. This procedure will be taking two arguments in its parameters e.g. ‘var1’ and ‘var2’. Begin the procedure with the BEGIN clause. Now it’s something new. The SELECT clause is again fetching all the records from the table ‘record’. The first argument passed by a user will be matched to column ‘Name’ values. On the other hand, the second argument passed by a user will be matched to the column ‘Country’ values. If the records match, it will fetch all the data from consecutive rows. The procedure would be finished with the keyword ‘END’.
Use the DELIMITER keyword to activate the procedure. After that, execute the CALL clause followed by the stored procedure name, which is ‘New’ along with the parameter values. It is clear from the image below that the query will only fetch the record of table ‘record’ where both values entered by the user match.
Conclusion:
In this guide, you have learned about the different ways to create a stored procedure in MySQL Workbench and MySQL command-line client shell e.g., Stored procedure with and without parameters.
