Stored Procedure in MySQL

Stored Procedures:

In this post I am going to explain basic about Stored Procedure, type of parameter in Stored Procedure, how to call Stored Procedure and sharing this information by simple example. This whole Stored Procedure demo I am going to elaborate using MySQL example.

We start with 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 object of Database Server. This is stored all SQL query and SQL Program into one object. After 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 and Same Stored Procedure will work for a given parameter or purpose. At a same time, many users can call same stored procedure for different parameter values.

Why Stored Procedure is required?.

  • First and most is, to store one time and use many times.
  • Store common code in one place and avoid 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 can not see code behind Stored Procedure.

Understand MySQL Stored Procedure using below example.

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

In the above code, I have created one database name is Employee and created one table to store employee basic details. In MySQL database also called as Schema.

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

Use MySQL InnoDB Engine. Please find more details on this later. I will update link over here.

Step -2 :

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

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

Below is a sample command to call this Stored Procedure for insertion.

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


Now Sample stored procedure to select employee data.

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

Sample code to call this stored procedure.

Step – 4:

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

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

Now let’s call this stored procedure.

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

Now this is a sample of Stored Procedure in MySQL. You can also drop this all stored procedure using below code.

This all basic about Stored Procedures.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of