The ultimate guide to MySQL Stored Procedure

1. What is a stored procedure?

A stored procedure is a batch of SQL queries that can be stored on the server.

After being issued and executed for the first time, stored procedure will be optimized and saved on the server. The next time you need it, just call by its name and the set of queries will be executed easier, faster, and more secure.

2. Why is using stored procedure recommended?

It’s optimized for speed. Stored procedures are compiled once and stored in an executable form, thus calling it would be quick and efficient.
It reduces the redundant works. Compile it once and reuse it anytime you want.
It saves communication costs. One line statement calling the stored procedure instead of multiple lines of the individual query will be sent over the network so it reduces the traffic between the client and the server.
It improves security. It’s one of the best practices to prevent SQL Injection.
It limits direct access to tables via defined roles in the database. You can grant access to a procedure for an user even if that user doesn’t have direct access to the tables.

3. How to use Stored Procedure in MySQL

Started from MySQL 5.0, Stored Procedure is supported in MySQL. Here is the general syntax for Stored Procedure in MySQL:

Create a stored procedure using CREATE PROCEDURE statement:

CREATE PROCEDURE proc_name(params)
BEGIN
DECLARE variables
DECLARE CURSORS
DECLARE handlers
STATEMENTS
END;

In most cases, you should have the stored procedure surrounded by the DELIMITER command like this:

DELIMITER //
CREATE PROCEDURE proc_name(params)
BEGIN
DECLARE variables
DECLARE CURSORS
DECLARE handlers
STATEMENTS
END //
DELIMITER ;

So we just replace the semicolon at the end of the statement with two forward slashes. The purpose is to tell MySQL to use a different delimiter while it creates our stored procedure. Just to avoid the case that MySQL sees the first semicolon and auto identifies it as the delimiter for marking the end of each SQL statement, and our stored procedure would break.

After running the statement above, the stored procedure proc_name is created, you can call it for execution using CALL statement:

CALL proc_name;

You can drop an unused procedure by running this statement:

DROP PROCEDURE proc_name;

Here is an example of using Stored Procedure in MySQL in practices:

To create a stored procedure, listing all metrics from three tables:

CREATE PROCEDURE `GetAllMetrics`()
   BEGIN
   SELECT COUNT(*) FROM DAU;
   SELECT COUNT(*) FROM WAU;
   SELECT COUNT(*) FROM MAU;
   END;

If you are not sure if the procedure with the name GetAllMetrics exists or not, run the drop statement in advance:

DROP PROCEDURE IF EXISTS `GetAllMetrics`();

Then call the newly created stored procedure when you need it:

CALL `GetAllMetrics`();

4. Using Stored Procedure with Parameters

In MySQL, there are three parameter modes that can be used with stored procedures.

1. IN

When you use this parameter mode, you (or your application) must pass the parameter’s value when you call the stored procedure. These parameters are protected. Therefore, its original value is retained after the stored procedure has been executed. If the stored procedure changes the value, it only does so on a copy of the parameter. This mode is the default mode. If you don’t provide the parameter mode, it will be IN.

2. OUT

The value of an OUT parameter can change within the stored procedure, and its value is returned to the calling application.

3. INOUT

This mode is a combination of the IN and OUT modes. You can pass the initial value, the stored procedure can change it, and it will return the new value to the calling application.

Example of using stored procedure with parameters:

Create a new stored procedure:

CREATE PROCEDURE `get_all_metrics`(
IN os_ver TEXT,
OUT dau INT,
OUT wau INT,
OUT mau INT)

BEGIN
    SELECT COUNT(*) INTO dau
    FROM daily_active
    WHERE ver = os_ver;

    SELECT COUNT(*) INTO wau
        FROM weekly_active
        WHERE ver = os_ver;

     SELECT COUNT(*) INTO mau
        FROM monthly_active
        WHERE ver = os_ver;
END;

Then call it with paramenters:

CALL get_all_metrics('mac', @dau, @wau, @mau);

Need a good GUI Tool to work with MySQL? Try TablePlus.

TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.  It’s also free