How to Create a Stored Procedure in MySQL

In this tutorial, we are going to see how to create a stored procedure in MySQL. In MySQL, you can pass parameters to a stored procedure and it does not return a value like a function.
 

Syntax for creating a stored procedure:

The syntax for creating a stored procedure in MySQL is as follows:

CREATE PROCEDURE my_procedure (parameter1 type, parameter2 type, ...)
BEGIN
	instruction1;
	instruction2;
	...
	instruction3;
END;

 

 

Example : How to Create a Stored Procedure in MySQL

The following query returns all rows from the Customers table:

SELECT * FROM Customers;

Output:

+------------+-----------+--------+-------------------------------+
| CustomerID |    Name   |  age   |             Address           |
+------------+-----------+--------+-------------------------------+
|     1      |    Alex   |   25   | 819 Saint Francis Way         |
|     2      |   Emily   |   15   | 171 Jarvisville Road Michigan |
|     3      |   Jean    |   35   | 188 Clay Street Indiana       |
|     4      |    Bob    |   40   | 285 Java Lane Missouri        |
+------------+-----------+--------+-------------------------------+

 
If you want to save this query on MySQL server for later execution, you can use a stored procedure.
 
The statement CREATE PROCEDURE creates a new stored procedure that wraps the above request:

DELIMITER $
CREATE PROCEDURE getCustomers()
BEGIN
	SELECT * FROM Customers;   
END$
DELIMITER ;

 

 
By definition, a stored procedure is a block of code stored in MySQL server. In this example, we’ve just created a stored procedure called getCustomers().

Once you have registered the stored procedure, you can invoke it using the CALL statement:

CALL getCustomers();

Output:

+------------+-----------+--------+-------------------------------+
| CustomerID |    Name   |  age   |             Address           |
+------------+-----------+--------+-------------------------------+
|     1      |    Alex   |   25   | 819 Saint Francis Way         |
|     2      |   Emily   |   15   | 171 Jarvisville Road Michigan |
|     3      |   Jean    |   35   | 188 Clay Street Indiana       |
|     4      |    Bob    |   40   | 285 Java Lane Missouri        |
+------------+-----------+--------+-------------------------------+

 
The stored procedure returns the same result as the query.
 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More