How to change my sql to loop in MySql stored procedure in mysql?

Question

When I write my sql in each statement,it works well.Now my sql is:

 SELECT COUNT(*)  INTO v_Point1Num from tbpoint where  Point=1;
 SELECT COUNT(*)  INTO v_Point2Num from tbpoint  where Point=2;
 SELECT COUNT(*)  INTO v_Point3Num from tbpoint  where Point=3;
 SELECT COUNT(*)  INTO v_Point4Num  from tbpoint where Point=4;
 SELECT COUNT(*)  INTO v_Point5Num from tbpoint where Point=5;

and it work well. Now I try to change it to loop,but it is wrong,how to fix it?I wonder it is the reason that I do not use "@" like "@v".

--can not work.
CREATE `SP_Point`()
BEGIN 
DECLARE v INT DEFAULT(0);
DECLARE pointlStr VARCHAR(800);
SET v = 1;  
WHILE v <= 5 DO 
SELECT COUNT(*)  INTO 
   (case v 
   when 1  then concat('v_Point',v,'Num')
     when 2  then concat('v_Point',v,'Num')
       when 3  then concat('v_Point',v,'Num')
         when 4  then concat('v_Point',v,'Num')
           when 5 then concat('v_Point',v,'Num')
           )
   from tbpoint  
   where  Point=v;  
    SET v = v + 1; 
 END WHILE; 
END

I try to change it to the other way,but it is still wrong.

  SET v = 1;  
  WHILE v <= 5 DO 
  set pointlStr=
  'SELECT COUNT(*)  INTO  @v_Point'+@v+'Num from tbpoint
 where  Point='+@v;  
 prepare stmt from @pointlStr;
 execute stmt;
    SET v = v + 1; 
END WHILE; 

Answer 1

You are trying to create a new variables(v_Point1Num, v_Point2Num.... etc) at run time which is not possible into mysql. you must declare a variable before using it.

You can achieve the same output by running single query as well... rather then running multiple queries

SELECT Point, COUNT(*)  from tbpoint 
group by Point
having point > 0 and point <= 5;

Concat() function return the varchar/String not the variable name. declare only one variable "v_pointNum"... fetch the value into variable inside loop.... and in the same loop update the other table as well. –

CREATE `SP_Point`()
BEGIN 

DECLARE v INT DEFAULT(0);
-- declare a variable to hold count value
DECLARE v_pointNum INT DEFAULT(0);
DECLARE serviceAttitudeLevelStr VARCHAR(800);

SET v = 1;  
WHILE v <= 5 DO 

    SELECT COUNT(*)  INTO v_pointNum from tbpoint where  Point=v;  

    -- update another table 
    update <mytable> set <mycol> = v_pointNum where <condition>;


        SET v = v + 1; 
 END WHILE; 
END