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 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.

Request for Proposal

Name is required

Comment is required

Sending message..