DBMS > Structural Query Language (SQL) > Simple Example of MySQL Procedure

Procedure without parameter:

Suppose we have the following student table:

CREATE TABLE student (
s_id int(11) NOT NULL,
s_name varchar(50) DEFAULT NULL,
roll int(11) DEFAULT NULL,
bdate date DEFAULT NULL
)

INSERT INTO `student` (`s_id`, `s_name`, `roll`, `bdate`)

VALUES
   (11, 'ASad Bhuiyan', 2303, '2001-12-12'),
   (101, 'Baki Billah', 1, '2002-01-01'),
   (102, 'Anas Lillah', 2, '2001-01-01'),
   (103, 'Sumaiya Ruby', 3, '2010-01-01'),
   (104, 'Jannatul Ferdouse', 4, '2001-01-01'),
   (178, 'Aslam', 1203, '2012-12-12');

 

Suppose we want to create a procedure which will show us all student in descending order. The appropriate SQL can be used in the procedure.

CREATE PROCEDURE get_all_students()
       select * from student order by s_id DESC;

 

Now we can just call the procedure to have all students:  

call get_all_students();

 

Procedure with IN parameter(s):

CREATE PROCEDURE get_student_by_id( IN id int(11) )
      select * from student where s_id=id;

if we want to show student with student id=11 then we will pass 11 as a parameter value:

    call get_student_by_id(11);

    All information of this student will be displayed

Procedure with IN/OUT parameter(s):

CREATE PROCEDURE get_a_student_name_by_id(
    IN id int(11),
    OUT name varchar(40))
select s_name into name from student where s_id=id;

To Show the output the following commands have to run:

    call get_a_student_name_by_id(11,@hisname);

    select @hisname;

 

Prev

Use of Procedure in Mysql

Next

Calculation of CGPA using Procedure

Feedback

Submit