MYSQL STORED PROCEDURE

Example 1 : Student Yearwise From IEMIS

1. Student Yearwise(With Total, Male, Female)
   DELIMITER $$
   CREATE PROCEDURE studentYearWise()
   BEGIN
   DECLARE a INT Default 2071;
   set @query = ”;
   simple_loop: LOOP
   set @query = CONCAT(@query,’SELECT concat(‘,a,’) as label, count(Gender) as value from student_detail WHERE (ct_’,a,’Class!=”PAD” and ct‘,a,’Class!=”” and ct‘,2079,’Class!=”FAD” and sss‘,2079,’_Class=””)’);
   SET a=a+1;
   if a<=2079 THEN
   set @query = CONCAT(@query,’ union ‘);
   end if;
   IF a=2080 THEN
   LEAVE simple_loop;
   END IF;
   END LOOP simple_loop;
   PREPARE stmt FROM @query;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt; END $$