MySQL – How to Create Stored Procedure in MySQL

MySQL supports Stored Procedures which can execute set of codes by applying relevant business logics. In this post we will see how to create a stored procedure and execute it. It is simpler than you may think to build a stored procedure in MySQL.

Let us create the following tables

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));
INSERT INTO items VALUES (1,'Television');
INSERT INTO items VALUES (2,'Mobile');
INSERT INTO items VALUES (3,'laptop');
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-01-01',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-01-02',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-09',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-29',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-11',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-16',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-16',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-22',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-24',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-24',1200);

Suppose you want to create a stored procedure which will accept item_description and returns the total sales_amount for the enire period, you can do it as shown below

DELIMITER $$
CREATE PROCEDURE Test.usp_get_sales
(
param_item_description VARCHAR(100)
)
BEGIN
SELECT item_description,SUM(sales_amount) AS sales_amount FROM items NATURAL
JOIN sales
WHERE item_description=param_item_description
GROUP BY item_description;
END;
$$
DELIMITER;

Note that the creation of the stored procedure starts with setting the Delimiter $$. The default delimiter for MySQL statements are semicolon so in order to instruct the MySQL engine about the start and end of the stored procedure block, you need to use a different delimiter (which in this case $$ is used).

Now you can execute a stored procedure using CALL keyword as shown below.

Execution 1

CALL usp_get_sales('Television');

When you execute the above code, the result is

Item_description sales_amount
Television 3600.00

Execution 2

CALL usp_get_sales('laptop');

When you execute the above code, the result is

Item_description sales_amount
laptop 6800.00

Note: The parameters do not start with @ like we use in SQL Server. So in order to differentiate between the actual column name and parameter name, the name param_item_description is used. You may need to use different naming conventions as you like.

I have previously written a similar article here: MySQL – How to Create Stored Procedure.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.