Creating a MySQL stored procedure with inputs
Posted By : Ishaan Madan | 28-Feb-2019
Introduction:
MySQL is the most popular and an open source relational DBMS capable of working on several platforms. It has features such as multiple user creation and has access to various storage engines, it is backed by Oracle. A major feature of MySQL is that it supports the Stored Procedures which in turn can execute a set of queries/codes and may contain business logic. This blog will show the creation of a stored procedure and its execution.
Prerequisites:
- MySQL server
- SQL basic queries
Steps:
1. Create 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);
2. Create a Stored Procedure:
DELIMITER $$
CREATE PROCEDURE Test.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: In our example, the beginning of the stored procedure starts by setting a Delimiter '$$'. The delimiter in MySQL statements by default is a semicolon (;). Hence, for instructing the engine of MySQL about start as well as the end of a stored procedure code, we need a different delimiter (in our case $$).
3. Stored Procedure Execution:
The stored procedures can be executed by following command-
CALL get_sales('Television');
Upon Execution the result is-
Item_description sales_amount
Television 3600.00
Thanks
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Ishaan Madan
Ishaan is a qualified and dedicated Technical Project Manager with 7 years of experience building web apps, platforms and leading full stack projects that have a meaningful impact to the company and clients. His technical skill set includes, Python, Django, JavaScript and MySQL.