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
To call the procedure, use the
CALL keyword as shown below:
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
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:
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
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
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;
IF EXISTS clause is added to prevent MySQL from throwing an error when the procedure doesn’t exist.