mysqlprocedure

Getting error while creating procedure for filtering data from a table


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 ;

Solution

  • Several comments:

    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.