How To Create Stored Procedure in MySQL

Last updated on July 20th, 2020 at 04:02 am

Stored procedure allows you to save and call SQL queries, so you don’t need to rewrite or modify queries if you need to use them multiple times. Here’s how to create stored procedure in MySQL.

Table of Contents

What is Stored Procedure in MySQL?

Stored procedure is a way to save SQL queries as subroutine functions in MySQL, which you can call back later on, when needed. It has a name, parameter list and SQL statements. It is a great option if you need to run the same SQL query multiple times, without any modification or with minor modifications.

Bonus Read : How to Create New User in MySQL

How To Create Stored Procedure in MySQL

Here are the steps to create stored procedure in MySQL using MySQL CREATE PROCEDURE statement.

Here’s the syntax to create stored procedure in MySQL.

DELIMITER //
CREATE PROCEDURE procedure_name(optional_list_of_arguments)
BEGIN
	sql_query;
END //
DELIMITER ;

In the above query, procedure_name is the name of stored procedure required to call it later. optional_list_of_arguments is the list of arguments you typically pass to any function.

sql_query is the SQL query to be executed when you call the stored procedure. The list of arguments can be used in SQL query to make it dynamic.

Bonus Read : How to Create Database in MySQL

Here’s a simple SQL query that returns all records from orders table.

mysql> select * from orders;

Here’s the above SQL query wrapped in a stored procedure get_orders

DELIMITER //
CREATE PROCEDURE get_orders()
BEGIN
	SELECT *  FROM orders;
END //
DELIMITER ;

In the above query, we have not defined any parameters for our stored procedure.

Bonus Read : How to Add Foreign Key in MySQL

You need to use CALL statement to call a stored_procedure. Here’s the syntax to call stored procedure.

CALL procedure_name(list of arguments);

Here’s how to call a stored procedure in MySQL.

mysql> call get_orders();
+----+--------------+------------+-------+-------------+---------------------+
| id | product_name | order_date | price | description | created_at          |
+----+--------------+------------+-------+-------------+---------------------+
|  1 | A            | 2020-07-01 |   150 | New product | 2020-06-01 00:00:00 |
|  2 | B            | 2020-07-01 |   235 | Old product | 2020-06-15 00:00:00 |
+----+--------------+------------+-------+-------------+---------------------+

Hopefully, now you can create stored procedure in MySQL.

Bonus Read : How to Create Stored Procedure with Parameter in MySQL

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

How to Prevent SQL Injection Attack

MySQL Add Foreign Key

How to Create Composite Primary Key in MySQL