Create Stored Procedure In MySQL

This blog defines how to create stored procedure in MySQL.

A stored procedure in a relational database is a collection of SQL statements that are saved on the database server and executed on the server itself. Any SQL query including Insert, Update, Delete can be a part of a stored procedure. Stored procedures are secure way to access and manipulate data. Stored procedures also provide high performance results compare to embedded SQL queries in your code.

 

In this blog, let's see how to create a stored procedure in MySQL database.

 

Syntax to create a stored Procedure: 

CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

Let's look at this in step by step.

 

Step 1: Create a Table 

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`employeeNumber` int(11) NOT NULL,
`lastname` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
)

Step 2: For insertion create a stored procedure: 

delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_inser`
(IN `employeenum` int
, IN `lastname` varchar(200)
)
begin
insert into employees (employeeNumber,lastname) values(employeenum,lastname);
END$$

Step 3: Call stored procedure 

call sp_inser(123,'rajeev')

Learn more details on stored procedures here, Learn Everything About Stored Procedures In SQL Server