Stored procedure in MySql example

Stored procedure is the re-usable piece of code in mysql database, stored procedure are precompiled database object, really helps performance improvement, and applying business logic in one location and reusing again and again.

In this mysql stored procedure tutorial you will learn following things.

• How to create stored procedure
• What is DELIMITER in mysql stored procedure
• How to pass input parameters and output parameters etc
• How to alter and drop stored procedure
• If else statement in stored procedure
• Case statement in stored procedure
• Writing loop in stored procedure
• How to handle exception in stored procedures
• how to use SIGNAL and RESIGNAL for raising errors in stored procedures

Create stored procedure in mysql database

In this example we have created a simple stored procedure that returns some columns from table in some specific order

use DatabaseName;

DELIMITER $$
CREATE PROCEDURE GetStudents()
    BEGIN
        SELECT 
        StuId, 
        firstName, 
        lastName, 
        email, 
        RegDate
        FROM
        tbstudent
        ORDER BY firstName ;
    END $$
DELIMITER ;

here is how to call stored procedure.

call GetStudents()

When we write any SQL statement, we use semicolon at the end of the statement like below.

Select * from tbStudent;
Select * from tbTeacher;
        

We want mysql client to treat the whole stored procedure as one statement, that’s why we need to define a DELIMITER with some different character then again set it back to original character.

DELIMITER delimiterCharacter

In above DELIMITER command you can use delimiterCharacter as single or double character like $$ or // etc, but make sure you don't use any system defined keyword or character, for example if you use backslash \ then that will be treated as escape character in MySQL.

Note : DELIMITER command will be case sensitive, for example if you define DELIMITER character as "AA" and then use as small letter "aa", then that will not work.

DELIMITER AA

select * from tbstudent AA
select * from tbemaillist AA

/* Change DELIMITER back to semicolon */
DELIMITER ;

Like MS-SQL Server, in mySql, there is no alter command for stored procedure, do we need to use drop and create statement to make any modification

To drop or delete any stored procedure you can use following statement, The statement will drop the object if exists.

drop procedure if exists GetStudents;

Here is how we can alter the above stored procedure, first drop the object and then create again.

use wtrdb;

drop procedure if exists GetStudents;

DELIMITER $$ 

create PROCEDURE GetStudents()
BEGIN
    SELECT
        StuId,
        firstName, 
        lastName,
        email,
        RegDate
    FROM
        tbstudent
    ORDER BY firstName ;
    END $$
    DELIMITER ;

Here you learn how to set input parameter in stored procedure

In below example I am creating a stored procedure that will return a student details based one provided student id , defining input parameter like in StuId int.

DELIMITER $$ 

create PROCEDURE GetStudent(in StuId int)
BEGIN
    SELECT * FROM tbstudent
    where StuId = StuId  ;
END $$
DELIMITER ;

MySQL if else statement look like same as any other programming language, here we close the if condition by using end if statement

    if(Condition)
    Then
		 // query
    else
	    // Query
    end if;

In this example we will see how to write if else statement in mysql stored procedure, in following procedure i will add and update student information, if provided student id is 0 then that will be considered as new student, so student information will be added, otherwise information will be updated.

DELIMITER $$ 
create PROCEDURE AddUpdateStudent(
in StuId int,
in fName varchar(50),
in lName varchar(50),
in eMail varchar(50))
BEGIN
    if(StuId=0)
    Then
		Insert into tbstudent
        (firstName, LastName, email, RegDate)
        values
        (fName, lName, eMail, CURDATE());
    else
	  Update tbstudent
      set firstName= fName,
      LastName = lname,
      email = eMail,
      RegDate = CURDATE()
      where StuId = StuId ;
    end if;
END $$
DELIMITER ;

Now in above stored procedure example we could have used case statement instead of if-else statement.

Case statement is normally used when then input value need to be checked with multiple condition, case statement is always faster than in else statement.

CASE caseValue
   WHEN value1 THEN statements
   WHEN value2 THEN statements
   
   [ELSE else-statements]
END CASE;

Here is the example of how we can write case statement in mysql stored procedure

DELIMITER $$ create PROCEDURE AddUpdateStudent1(
in StuId int,
in fName varchar(50),
in lName varchar(50),
in eMail varchar(50))
BEGIN
case (StuId)
when 0
then
Insert into tbstudent
(firstName, LastName, email, RegDate)
values
(fName, lName, eMail, CURDATE());
when StuId>0
then
Update tbstudent
set firstName= fName,
LastName = lname,
        email = eMail,
        RegDate = CURDATE()
        where StuId = StuId ;
    end case;
END $$
DELIMITER ;  

You may be interested to know how to write stored procedure in SQL server server.