A Single Stored Procedure to Perform Insert, Update and Delete in database using My SQL

Here we will learn how to create a stored procedure in MySQL to manipulate the database.
A single stored procedure that will perform insert, update and delete operation in the database.

Step 1: Create a database and a simple table

CREATE DATABASE test2;

use test2;

CREATE TABLE employee(

    id int primary key auto_increment,

    name varchar(100) NOT NULL,

    address TEXT NULL,

    phone varchar(15) NULL

)

Step 2: Create a stored procedure to perform the Insert, Update and Delete. Script is given below

DELIMITER $$

CREATE PROCEDURE operateEmployee

(

IN emp_id INT ZEROFILL,

IN emp_name VARCHAR(100),

IN emp_addr TEXT,

IN emp_phone VARCHAR(15),

IN action_type INT ZEROFILL

)

COMMENT 'action=1 for insert, 2 for update, 3 for delete'

BEGIN

IF action_type=1 THEN

INSERT INTO employee (name,address,phone) VALUES (emp_name,emp_addr,emp_phone);

SELECT last_insert_id();

ELSEIF action_type=2 THEN

      UPDATE employee SET name=emp_name,address=emp_addr,phone=emp_phone WHERE id=emp_id;

ELSEIF action_type=3 THEN

      DELETE FROM employee WHERE id=emp_id;

END IF;

END$$

DELIMITER ;

Step 3: In order to check your stored procedure. You can check using these queries.

            Query 1: If action_type = 1 then it will insert data into the database.

            SET @emp_id='0';

SET @emp_name='Ghulam';

SET @emp_addr='India,Kolkata';

SET @emp_phone='123456';

SET @action_type='1';

CALL `operateEmployee`(@emp_id, @emp_name, @emp_addr, @emp_phone, @action_type);

Query 2: If action_type = 2 then it will update data into the database. 

            SET @emp_id='1';

SET @emp_name='Ghulam Moinuddin';

SET @emp_addr='India,Kolkata,70044';

SET @emp_phone='9831062375';

SET @action_type='2';

CALL `operateEmployee`(@emp_id, @emp_name, @emp_addr, @emp_phone, @action_type);

Query 3: If action_type = 3 then it will delete data from the database.

SET @emp_id='1';

SET @emp_name='';

SET @emp_addr='';

SET @emp_phone='';

SET @action_type='3';

CALL `operateEmployee`(@emp_id, @emp_name, @emp_addr, @emp_phone, @action_type);

 
Thanking You!