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 an experienced Wordpress/PHP Lead Developer, he has good knowledge of HTML, CSS, PHP, Wordpress, Jquery and AJAX. His hobbies are playing basketball and reading about defence.

Request for Proposal

Name is required

Comment is required

Sending message..