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;

Rakesh Soni · Accepted Answer · 2015-06-12 06:22:41Z

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

edited Jun 12, 2015 at 6:22

answered Jun 11, 2015 at 5:26

Rakesh Soni

11k6 gold badges48 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

flower Over a year ago

I want to set the resulat to each variable and than update to the other table,so I have to cache the result firstly.I have try to declare,by still wrong:Error Code: 1327 Undeclared variable: v_Point5Num

flower Over a year ago

SET v = 1; WHILE v <= 5 DO BEGIN set @pointlStr=concat('SELECT COUNT(*) INTO v_Point5Num', '', ' from tbpoint where point=',v); select @pointlStr; PREPARE stmt from @pointlStr; EXECUTE stmt; SET v = v + 1; END; END WHILE; --I have DECLARE serviceAttitudeLevelStr VARCHAR(800); DECLARE v_Point5Num INT;

Rakesh Soni Over a year ago

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.

flower Over a year ago

In my sql above,I use concat() just to produce a Statement,and then execute it.But it seems not work. declare only one variable "v_pointMNum"... fetch the value into variable inside loop.... and in the same loop update the other table as well?Can you give me a example?Thank you!

Rakesh Soni Over a year ago

I have added an example in my answer. Please check

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related