In this article, we are going to learn about the stored procedures in MySQL. In this article, I am covering the basics of the stored procedure that includes the following

1. Summary of MySQL Stored Procedure
2. Create a stored procedure using Query and MySQL workbench
3. Create a Parameterized stored procedure
4. Drop the Stored Procedure using query and MySQL workbench

The stored procedure is SQL statements wrapped within the CREATE PROCEDURE statement. The stored procedure may contain a conditional statement like IF or CASE or the Loops. The stored procedure can also execute another stored procedure or a function that modularizes the code.

Following are the benefits of a stored procedure:

1. Reduce the Network Traffic: Multiple SQL Statements are encapsulated in a stored procedure. When you execute it, instead of sending multiple queries, we are sending only the name and the parameters of the stored procedure
2. Easy to maintain: The stored procedure are reusable. We can implement the business logic within an SP, and it can be used by applications multiple times, or different modules of an application can use the same procedure. This way, a stored procedure makes the database more consistent. If any change is required, you need to make a change in the stored procedure only
3. Secure: The stored procedures are more secure than the AdHoc queries. The permission can be granted to the user to execute the stored procedure without giving permission to the tables used in the stored procedure. The stored procedure helps to prevent the database from SQL Injection

The syntax to create a MySQL Stored procedure is the following:

Create Procedure [Procedure Name] ([Parameter 1], [Parameter 2], [Parameter 3] )
Begin
SQL Queries..
End

In the syntax:

1. The name of the procedure must be specified after the Create Procedure keyword
2. After the name of the procedure, the list of parameters must be specified in the parenthesis. The parameter list must be comma-separated
3. The SQL Queries and code must be written between BEGIN and END keywords

To execute the store procedure, you can use the CALL keyword. Below is syntax:

CALL [Procedure Name] ([Parameters]..)

In the syntax:

1. The procedure name must be specified after the CALL keyword
2. If the procedure has the parameters, then the parameter values must be specified in the parenthesis

Let us create a basic stored procedure. For demonstration, I am using the sakila database.

Create a simple stored procedure

Suppose you want to populate the list of films. The output should contain film_id, title, description, release year, and rating column. The code of the procedure is the following:

To create the MySQL Stored Procedure, open the MySQL workbench Connect to the MySQL Database copy-paste the code in the query editor window click on Execute.

You can view the procedure under stored procedures. See the below screenshot.

To execute the procedure, run the below command.

Below is the partial screenshot of the output:

Crete procedure using MySQL workbench wizard

We can use the MySQL workbench wizard to create a stored procedure. Suppose you want to get the list of the customer from the sakila database. To do that, expand the sakila schema Right-click on Stored Procedures Select Create a Stored procedure.

In the New procedure window, a create template has been created. In the template, replace the procedure name with sp_getCustomers. In the code block, enter the following query

Click on Apply. A dialog box, Apply script to database opens. On the Review the script screen, you can view the code of the stored procedure. Click on Apply.

The script is applied successfully, and a stored procedure sp_GetCustomer has been created successfully.

In MySQL Workbench, You can view the stored procedure under the Stored Procedures folder of the sakila schema.

Create a parameterized stored procedure

The MySQL Stored procedure parameter has three modes: IN, OUT, and INOUT. When we declare an IN type parameter, the application must pass an argument to the stored procedure. It is a default mode. The OUT type parameter, the stored procedure returns a final output generated by SQL Statements. When we declare the INOUT type parameter, the application has to pass an argument, and based on the input argument; the procedure returns the output to the application.

When we create a stored procedure, the parameters must be specified within the parenthesis. The syntax is following:

In the syntax:

1. Specify the type of the parameter. It can be IN, OUT or INOUT
2. Specify the name and data type of the parameter

Example of IN parameter

Suppose we want to get the list of films based on the rating. The param_rating is an input parameter, and the data type is varchar. The code of the procedure is the following:

To populate the list of the films with an NC-17 rating, we pass the NC-17 value to the sp_getMoviesByRating() procedure.

Output:

Example of OUT parameter

Suppose we want to get the count of the films that have a PG-13 rating. The Total_Movies is an output parameter, and the data type is an integer. The count of the movies is assigned to the OUT variable (Total_Movies) using the INTO keyword. The code of the procedure is the following:

To store the value returned by the procedure, pass a session variable named @PGRatingMovies.

Example of an INOUT parameter

Suppose we want to get the total count of movies based on the rating. The input parameter is param_rating in the procedure, and the data type is varchar(10). The output parameter is Movies_count, and the data type is an integer.

Code of procedure:

Execute the procedure using CALL keyword and save the output in session variable named @MoviesCount

View the list of stored procedure in a database using a query

To view the list of the stored procedure, you can query the information_schema.routines table. It contains the list of the stored procedure and stored functions created on the database. To view the list of the stored procedure created in a sakila database, run the following query. Moreover, it also provides the owner, created date, security type, and SQL data access to the stored procedures.

Drop a Stored Procedure

To drop the stored procedure, you can use the drop procedure command. The syntax is following

Drop procedure [IF EXISTS] <Procedure Name>

In the syntax, the name of the stored procedure must be followed by the Drop Procedure keyword. If you want to drop the sp_getCustomers procedure from the sakila database, you can run the following query.

When you try to drop the procedure that does not exist on a database, the query shows an error:

ERROR 1305 (42000): PROCEDURE sakila.getCustomer does not exist

To avoid this, you can include the [IF EXISTS] option in the drop procedure command. When you include the IF EXISTS keyword, instead of an error, the query returns a warning:

Query OK, 0 rows affected, 1 warning (0.01 sec) 1305 PROCEDURE sakila.getCustomer does not exist

Drop a Stored Procedure using MySQL workbench wizard

You can use the MySQL workbench wizard to drop the procedure. To drop any procedure, expand sakila schema Expand Stored Procedures Right-click on sp_GetMovies Click on Drop Stored Procedure.

A dialog box opens. You can choose to review the procedure before dropping it, or you can drop it without reviewing it. It is good practice to review the database object before dropping it, so choose Review SQL.

In Review SQL Code to Execute dialog box, you can review the drop statement and the object name.

Click on Execute. The procedure will be dropped successfully.

Summary

In this article, we learned the basics of MySQL Stored procedure. I have covered following topics:

1. Syntax to create a MySQL Stored Procedure and how to create them us Create Procedure statement and MySQL workbench wizard
2. How to create a parameterized MySQL Stored Procedure

In next article, we are going to learn about the MySQL Views.

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022