MySQL stored procedure explained with examples

In MySQL, a stored procedure is a group of MySQL statements that’s executed together when you call the procedure.

A stored procedure is somewhat similar to a function. The main difference is that a stored procedure can only be called with the CALL keyword, while a function can be included in a MySQL statement.

A function needs to return a value, but a procedure only executes statements to manipulate your data without returning a value.

Let’s learn how you create and call a stored procedure. First, create an example table named Cities with the following statement:

CREATE TABLE `Cities` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  PRIMARY KEY (`id`)
) 

To create a stored procedure, you use the CREATE PROCEDURE statement followed with the procedure name as follows:

CREATE PROCEDURE getCities()
SELECT * FROM Cities;

The SQL code above will create a getCities() procedure that retrieves all rows and columns of the Cities table.

To call the procedure, use the CALL keyword as shown below:

CALL getCities();

You can also declare parameters to pass into the procedure during the call by adding them to the parentheses after the procedure name.

The following example requires you to pass a cityName to the insertCity() procedure:

CREATE PROCEDURE insertCity(cityName VARCHAR(100))
INSERT INTO `Cities` VALUES (NULL, cityName);

You can then call the insertCity() procedure and pass the cityName argument like this:

CALL insertCity('York');

For a simple stored procedure that only has one line, you can immediately write the statement below the CREATE PROCEDURE statement.

But if you have a complex stored procedure with multiple lines, you need to add a DELIMITER clause and the BEGIN ... END compound statement in your CREATE PROCEDURE syntax.

Consider the following example:

DELIMITER //
CREATE PROCEDURE insertCity(cityName VARCHAR(100))
BEGIN
  INSERT INTO `Cities` VALUES (NULL, cityName);
  SELECT * FROM `Cities`;
END; //

The above procedure will insert a new row into the Cities table, then retrieves all data from the table as an output.

Because the procedure has more than one statement, the delimiter is changed from the default semicolon ; to a double slash // using the DELIMITER keyword.

The change of delimiter symbol causes MySQL to consider statements above as a whole inside the BEGIN ... END statement.

Without defining the delimiter symbol, MySQL will interpret each statement above one by one, causing an error.

Here’s the result of calling the procedure:

CALL insertCity('Bristol');

+----+----------+
| id | name     |
+----+----------+
|  1 | York     |
|  2 | Bristol  |
+----+----------+

And that’s how MySQL stored procedure works. You can add any valid MySQL statements inside the procedure and run them together using the CALL statement.

By storing your statements inside a procedure, you can execute the statements as many times as you need without having to write them in the command line.

Finally, to remove a stored procedure, you can use the DROP PROCEDURE statement as follows:

DROP PROCEDURE IF EXISTS insertCity;

The IF EXISTS clause is added to prevent MySQL from throwing an error when the procedure doesn’t exist.