Create Stored procedure in MYSQL

This blog defines how to create stored procedure in MYSQL.

Syntax to create a stored Procedure:

CREATE

[DEFINER = { user | CURRENT_USER }]

PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

 

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 Sp

 

call sp_inser(123,'rajeev')