How to rectify Error #1064 in MariaDB procedure?

Question

DELIMITER $$

CREATE PROCEDURE insert_priority_rows()
BEGIN
  DECLARE max_heuristics INT DEFAULT 10;
  DECLARE heuristic_ID INT;

  DECLARE cur CURSOR FOR
    SELECT heuristicID
    FROM heuristics;
    
  DECLARE CONTINUE HANDLER 
  FOR NOT FOUND SET heuristic_ID = 0;
    
  DECLARE @study_ID INT;

  SELECT MAX(studyID) INTO @study_ID FROM study;

  OPEN cur;

  REPEAT
    FETCH cur INTO heuristic_ID;
    IF heuristic_ID = 0 THEN
      LEAVE;
    END IF;

    INSERT INTO heuristic_priority (studyID, heuristicID, h_priority)
    VALUES (@study_ID, heuristic_ID, 'm');
  UNTIL heuristicID = 0 END REPEAT;

  CLOSE cur;
END$$

DELIMITER ;

It throws up an error as follows:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@study_ID INT;

What should I do to rectify this issue?

Answer 1

You don't need most of that code. The loop can be replaced with this query:

DELIMITER $$

CREATE PROCEDURE insert_priority_rows()
BEGIN
    INSERT INTO heuristic_priority (studyID, heuristicID, h_priority)
    SELECT m.max_study_id, h.heuristicID, 'm'
    FROM heuristics AS h
    CROSS JOIN (SELECT MAX(studyID) as max_study_id FROM study) AS m;
END$$