generateTable - Procedure - Balbeer

BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE j INT DEFAULT 0;
DECLARE m INT DEFAULT 0;
DECLARE iv INT DEFAULT 0;
DECLARE hv TEXT;
DECLARE ov TEXT;
DECLARE rv TEXT;
DECLARE nm1 TEXT;
DECLARE nm2 TEXT;
DECLARE r1 TEXT;
DECLARE r2 TEXT;
DECLARE pg1 TEXT;
DECLARE pg2 TEXT;
DECLARE eml1 TEXT;
DECLARE eml2 TEXT;
DECLARE mb1 TEXT;
DECLARE mb2 TEXT;
DECLARE g1 TEXT;
DECLARE g2 TEXT;
DECLARE nm3 TEXT;
DECLARE r3 TEXT;
DECLARE pg3 TEXT;
DECLARE eml3 TEXT;
DECLARE mb3 TEXT;
DECLARE g3 TEXT;
DECLARE fr TEXT;
DECLARE cr TEXT;
DECLARE hos TEXT;
SELECT COUNT(*) FROM mess_master_room_mapping INTO n;
DROP TABLE IF EXISTS mess_room_occupancy;
CREATE TABLE `mess_room_occupancy` (
  `hostel` text DEFAULT NULL,
  `room` text DEFAULT NULL,
  `capacity` text DEFAULT NULL,
  `occupied` text DEFAULT NULL,
  `free` text DEFAULT NULL,
  `roll1` text DEFAULT NULL,
  `name1` text DEFAULT NULL,
  `mobile1` text DEFAULT NULL,
  `prog1` text DEFAULT NULL,
  `mail1` text DEFAULT NULL,
  `gender1` text DEFAULT NULL,
  `roll2` text DEFAULT NULL,
  `name2` text DEFAULT NULL,
  `mobile2` text DEFAULT NULL,
  `prog2` text DEFAULT NULL,
  `mail2` text DEFAULT NULL,
  `gender2` text DEFAULT NULL,
  `roll3` text DEFAULT NULL,
  `name3` text DEFAULT NULL,
  `mobile3` text DEFAULT NULL,
  `prog3` text DEFAULT NULL,
  `mail3` text DEFAULT NULL,
  `gender3` text DEFAULT NULL
);
SET i=0;
WHILE i<n
DO
  SELECT id, hostel ,occupancy , room FROM mess_master_room_mapping LIMIT i , 1 INTO iv , hv , ov , rv ;
  SET i = i + 1;
  SET hos = CONCAT(hv,'%');
  SELECT COUNT(*) FROM mess_student_hostel_hostelinfo WHERE hostel_full_room = rv and hostel_name LIKE hos INTO m;
  IF m = 3 THEN
    SELECT name , rollno , mobile , email , prog , gender FROM mess_student_hostel_hostelinfo WHERE hostel_full_room = rv and hostel_name LIKE hos LIMIT 0 , 1 INTO nm1 , r1 , mb1 , eml1 , pg1 , g1 ;
    SELECT name , rollno , mobile , email , prog , gender FROM mess_student_hostel_hostelinfo WHERE hostel_full_room = rv and hostel_name LIKE hos LIMIT 1 , 1 INTO nm2 , r2 , mb2 , eml2 , pg2 , g2 ;
    SELECT name , rollno , mobile , email , prog , gender FROM mess_student_hostel_hostelinfo WHERE hostel_full_room = rv and hostel_name LIKE hos LIMIT 2 , 1 INTO nm3 , r3 , mb3 , eml3 , pg3 , g3 ;
    INSERT INTO mess_room_occupancy ( hostel , room , capacity , occupied , free , roll1 , name1 , mobile1 , mail1 , prog1 , gender1 , roll2 , name2 , mobile2 , mail2 , prog2 , gender2 , roll3 , name3 , mobile3 , mail3 , prog3 , gender3)
    VALUES ( hv , rv , ov , 3 , (ov-3) , r1 , nm1 , mb1 , eml1 , pg1 , g1 , r2 , nm2 , mb2 , eml2 , pg2 , g2 , r3 , nm3 , mb3 , eml3 , pg3 , g3  );
  ELSEIF m = 2 THEN
    SELECT name , rollno , mobile , email , prog , gender FROM mess_student_hostel_hostelinfo WHERE hostel_full_room = rv and hostel_name LIKE hos LIMIT 0 , 1 INTO nm1 , r1 , mb1 , eml1 , pg1 , g1  ;
    SELECT name , rollno , mobile , email , prog , gender FROM mess_student_hostel_hostelinfo WHERE hostel_full_room = rv and hostel_name LIKE hos LIMIT 1 , 1 INTO nm2 , r2 , mb2 , eml2 , pg2 , g2 ;
    INSERT INTO mess_room_occupancy ( hostel , room , capacity , occupied , free , roll1 , name1 , mobile1 , mail1 , prog1 , gender1 , roll2 , name2 , mobile2 , mail2 , prog2 , gender2 )
    VALUES ( hv , rv , ov , 2 , (ov-2) ,  r1 , nm1 , mb1 , eml1 , pg1 , g1 , r2 , nm2 , mb2 , eml2 , pg2 , g2 );
  ELSEIF m=1 THEN
    SELECT name , rollno , mobile , email , prog , gender FROM mess_student_hostel_hostelinfo WHERE hostel_full_room = rv and hostel_name LIKE hos LIMIT 0 , 1 INTO nm1 , r1 , mb1 , eml1 , pg1 , g1 ;
    INSERT INTO mess_room_occupancy ( hostel , room , capacity , occupied , free , roll1 , name1 , mobile1 , mail1 , prog1 , gender1 )
    VALUES ( hv , rv , ov , 1 , (ov-1) , r1 , nm1 , mb1 , eml1 , pg1 , g1  );
  ELSE
    INSERT INTO mess_room_occupancy ( hostel , room , capacity , occupied , free )
    VALUES ( hv , rv , ov , 0 , ov  );
  END IF;

END WHILE;
End