Stored Procedure in MySQL

In this post, I am sharing the basic about MySQL Stored Procedure, type of parameter in Stored Procedure, how to call Stored Procedure.

Let me start with the basic theory:

What is Stored Procedures?

Stored Procedure is set of SQL / PLSQL command which all are compiled and stored in database servers.
Stored Procedure is the object of Database Server which stores all the SQL query and SQL Program into one object.
After the creation of Stored Procedure, any application or program can use this in any number of times.
Stored Procedure can accept input parameters in which user can pass a different parameter. At the same time, many users can call the same stored procedure with the different parameters.

Why Stored Procedure?

• Store one time and use many times
• Store common code in one place and avoid the duplication
• This is will also reduce network traffic and give the best performance
• For security purpose also Stored Procedure is being used where common user cannot see code of Stored Procedure.

Understand MySQL Stored Procedure using below example:

Step -1 :Create database and table using below script.

1 2 3 4 5 6 7 8 9 10 11 12

/*First let's create Employee Database.*/ CREATE DATABASE Employee; /*Create sample EmployeeDetails table.*/ CREATE TABLE Employee.tbl_EmployeeDetails (      EmpID INTEGER AUTO_INCREMENT     ,EmpFirstName VARCHAR(50)     ,EmpLastName VARCHAR(50)     ,GENDER CHAR(1)     ,DOB DATETIME         ,CONSTRAINT pk_tbl_EmployeeDetails_EmpID PRIMARY KEY (EmpID) )ENGINE = InnoDB;

In the above code, I created a database name is Employee and created a table for storing employee basic details. In MySQL database also called as Schema.

I applied Auto_Increment value for EmpID which is the default increment by one and not required to pass during insertion.

Step -2 :

Create Stored Procedure to insert data into the tbl_Employee table:
This Stored Procedure is basically with a list of Input Parameters.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

/* Sample stored procedure to insert records*/ DELIMITER // CREATE PROCEDURE Employee.usp_InsertEmployeeDetails (      InputEmpFirstName VARCHAR(50)     ,InputEmpLastName VARCHAR(50)     ,InputGENDER CHAR(1)     ,InputDOB DATETIME ) /******************************************************************* Authors Name : Anvesh Patel Created Date : 2015-05-07 Description : This is demo stored procedure to insert               record into table.Basically for www.dbrnd.com readers. ********************************************************************/ BEGIN   INSERT INTO Employee.tbl_EmployeeDetails   (          EmpFirstName          ,EmpLastName          ,GENDER          ,DOB   )   VALUES   (          InputEmpFirstName         ,InputEmpLastName         ,InputGENDER         ,InputDOB   );   END // DELIMITER ;

This is a simple stored procedure to store data into tbl_EmployeeDetails using input parameters. The user has to pass all Employee details as an input parameter and code will store all data into EmployeeDetails table.

Below is a sample command to call the Stored Procedure for insertion:

1 2 3

CALL Employee.usp_InsertEmployeeDetails('Jack','Roy','M','1990-01-26'); CALL Employee.usp_InsertEmployeeDetails('Martin','Meloy','M','1988-05-23'); CALL Employee.usp_InsertEmployeeDetails('Jenny','Rose','F','1991-09-01');

Above, I called this stored procedure three different to store three employee details.

Step-3:

Now Sample stored procedure to select the employee data:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

DELIMITER // CREATE PROCEDURE Employee.usp_GetEmployeeDetails() /****************************************************************** Authors Name : Anvesh Patel Created Date : 2015-05-07 Description : This is demo stored procedure to select               record from table.Basically for www.dbrnd.com readers. ********************************************************************/ BEGIN SELECT       EmpFirstName      ,EmpLastName      ,GENDER      ,DOB FROM Employee.tbl_EmployeeDetails;   END // DELIMITER ;

This is a sample stored procedure is to select employee data. You can also set input parameter to apply the filters on data.

Sample code to call the stored procedure:

1	CALL Employee.usp_GetEmployeeDetails();

Step – 4:

This is a sample stored procedure to show the output parameter and use of it.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

DELIMITER // CREATE PROCEDURE Employee.usp_GetEmployeeCount_ByGender ( InputGender CHAR(1)     ,OUT TotalCount INTEGER ) /*********************************************************************** Authors Name : Anvesh Patel Created Date : 2015-05-07 Description : This is demo stored procedure to select count               of employee by gender.Basically for www.dbrnd.com readers. ************************************************************************/ BEGIN      SELECT COUNT(1)      INTO TotalCount      FROM Employee.tbl_EmployeeDetails      WHERE Gender=InputGender;     END // DELIMITER ;

In the above stored procedure, I have created an output parameter. Many times output parameter is required for application when table result is not required. You can also define multiple output parameters. In the above code, I stored a count of the employee based on the gender into one output parameter.

Now let’s call this stored procedure:

1 2	CALL Employee.usp_GetEmployeeCount_ByGender('M',@TotalCount); SELECT @TotalCount;

When you select @TotalCount, it will return the result of this stored procedure.

Above are different sample of Stored Procedures. You can also drop these all stored procedures using below code.

1 2 3

DROP PROCEDURE IF EXISTS Employee.usp_InsertEmployeeDetails; DROP PROCEDURE IF EXISTS Employee.usp_GetEmployeeDetails; DROP PROCEDURE IF EXISTS Employee.usp_GetEmployeeCount_ByGender;