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 INTitem_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INTsales_date DATETIMEsales_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_amountVALUES (1,'2014-01-01',1200);
INSERT INTO sales(item_id,sales_date,sales_amountVALUES (2,'2014-01-02',200);
INSERT INTO sales(item_id,sales_date,sales_amountVALUES (3,'2014-01-09',1700);
INSERT INTO sales(item_id,sales_date,sales_amountVALUES (3,'2014-01-29',1700);
INSERT INTO sales(item_id,sales_date,sales_amountVALUES (3,'2014-02-11',1700);
INSERT INTO sales(item_id,sales_date,sales_amountVALUES (1,'2014-02-16',1200);
INSERT INTO sales(item_id,sales_date,sales_amountVALUES (2,'2014-02-16',200);
INSERT INTO sales(item_id,sales_date,sales_amountVALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amountVALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amountVALUES (2,'2014-02-22',200);
INSERT INTO sales(item_id,sales_date,sales_amountVALUES (3,'2014-02-24',1700);
INSERT INTO sales(item_id,sales_date,sales_amountVALUES (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_amountAS 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

About Author

Author Image
Ishaan Madan

Ishaan, a skilled technical project manager, excels at breaking down complex projects into manageable tasks. With a background in both technology and project management, he offers a unique perspective to every project he undertakes. His effective communication skills enable him to collaborate seamlessly with both technical and non-technical stakeholders, ensuring everyone is aligned towards shared objectives. He has hands-on experience in utilizing agile methodologies like Scrum and Kanban to drive project management and foster team collaboration. He leverages project management tools such as JIRA, Trello, and Clickup to monitor progress, manage tasks, and facilitate communication among team members and stakeholders. Moreover, his proficiency in full-stack development empowers him to comprehend the technical aspects of projects and provide guidance to developers when necessary. He demonstrates expertise in utilizing popular Python frameworks like Django and Flask, along with data analysis and manipulation libraries such as NumPy and Pandas. On the front-end, Ishaan adeptly employs JavaScript libraries like React and Angular to craft visually appealing and user-friendly interfaces. Additionally, he possesses proficiency in HTML, CSS, and JavaScript for designing responsive and mobile-friendly layouts.

Request for Proposal

Name is required

Comment is required

Sending message..