Getting error while creating procedure for filtering data from a table

Question

Create a MySQL procedure to declare a cursor to select last name, first name, salary, and hire date from the EMPLOYEE table. Retrieve each row from the cursor and print the employee’s information if the employee’s salary is greater than $50,000 and the hire date is before 31-DEC-1997 (explicit cursor problem). This is my problem statement for which I am creating the procedure, however I am getting an error message near loop:. I am unable to understand the exact cause of error. This is the code:

DELIMITER //

CREATE PROCEDURE GetHighEarnersBefore1998(IN salary_threshold DECIMAL(10,2))

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE emp_last_name VARCHAR(50);
  DECLARE emp_first_name VARCHAR(50);
  DECLARE emp_salary DECIMAL(10,2);
  DECLARE emp_hire_date DATE;

  DECLARE emp_cursor CURSOR FOR SELECT last_name, first_name, salary, hire_date FROM EMPLOYEE;

  OPEN emp_cursor;

loop:
  FETCH emp_cursor INTO emp_last_name, emp_first_name, emp_salary, emp_hire_date;
  SET done = CURSOR_ROWCOUNT = 0;

  IF emp_salary > salary_threshold AND emp_hire_date < '1997-12-31' THEN
    SET done = TRUE;
    SELECT CONCAT(emp_last_name, ', ', emp_first_name), emp_salary, emp_hire_date;
  END IF;

  LEAVE loop WHEN done;
END LOOP;

  CLOSE emp_cursor;
END //

DELIMITER ;

Answer 1

Several comments:

• CURSOR_ROWCOUNT isn't a function in MySQL. Are you accustomed to using IBM DB2? That's the only brand of SQL database that has the CURSOR_ROWCOUNT function, as far as I know.

• LEAVE <label> WHEN <condition> isn't valid syntax for MySQL, or any other brand of SQL as far as I know. Oracle has EXIT WHEN <condition> but that's not MySQL.

• You have confused the loop label with the LOOP keyword. Did you read any documentation or examples?

• If you do a SELECT query in each iteration inside the loop, it will cause the procedure to return a multi-result set. I don't believe this is what you want.

• Assuming you fix the syntax errors, your loop exits on the first row it encounters that does not meet the condition. But you didn't specify any ORDER BY for the cursor query, so you have no guarantee your results will include all rows that do meet the condition. Even if you intended to abort the loop on the first row that does not meet the condition, the order is arbitrary when you don't specify an ORDER BY, so you can't rely on it even being consistent.

• Like almost every use of cursors in MySQL, your cursor is not needed. You can get your result without using a cursor or a loop, and you can get it in a single result set.

Example:

CREATE PROCEDURE GetHighEarnersBefore1998(IN salary_threshold DECIMAL(10,2))
BEGIN
  SELECT last_name, first_name, salary, hire_date 
  FROM EMPLOYEE
  WHERE salary > salary_threshold AND hire_date < '1997-12-31'; 
END

This procedure will produce one result set. It's easier to code and to easier to read.