MySQL Procedure variable receiving null on count(*)

January 3, 2023

Why when I do Select count(*) From table1 I receive 300 but if I do SELECT end = COUNT(*) FROM table1; returns null

Here is the fiddle example https://dbfiddle.uk/ZHzoaztV

code snippet:

CREATE TABLE table1(
  start int NOT NULL,
  id int PRIMARY KEY AUTO_INCREMENT,
  counter int NOT NULL,
  difference int NOT NULL,
  end int NOT NULL
);

CREATE PROCEDURE doWhile()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE start INT DEFAULT 120;
  DECLARE counter INT DEFAULT 1;
  DECLARE end INT DEFAULT 300;
WHILE (i <= end) DO
  INSERT INTO table1 VALUES (start,null,counter,start+counter,end); 
  SET i = i+1;
  SET counter = counter+1;
END WHILE;
END;
CALL doWhile();
SELECT * FROM table1;


CREATE PROCEDURE insertMore()
BEGIN
  DECLARE start INT;
  DECLARE counter INT DEFAULT 1;
  DECLARE end INT;

  SELECT end = COUNT(*) FROM table1;
  SELECT start = MAX(id)+1 FROM table1;
  -- SELECT COUNT(*) FROM table1;

  WHILE (counter <= end) DO
    INSERT INTO table1 VALUES (start,null,counter,start+counter,end); 
    SET counter = counter+1;
  END WHILE;
END;
CALL insertMore();
SELECT * FROM table1;

I expected to return 300, so hopefully my function should do it right

>Solution :

You have a problem with start and end Variable

Can you try this :

CREATE PROCEDURE insertMore()
BEGIN
  DECLARE start INT;
  DECLARE counter INT DEFAULT 1;
  DECLARE end INT;

  SELECT COUNT(*) into end FROM table1;
  SELECT max(id)+1 into start FROM table1;
  -- SELECT COUNT(*) FROM table1;

  WHILE (counter <= end) DO
    INSERT INTO table1 VALUES (start,null,counter,start+counter,end); 
    SET counter = counter+1;
  END WHILE;
END;

Try it here : https://dbfiddle.uk/X6vP3wKW