CODEWITHSUNDEEP
mysqlcursor
am90
am90

Reputation: 201

error fetching a cursor

I am working on this cursor that fetch the salary column of a table and see if a value is bigger than 2500 we should take 500 as tax, and ELSE we should take only 200, and finally select and show the table with a new column with salary after taxes:

CREATE DEFINER=`root`@`localhost` PROCEDURE `tax_to_salary`()
BEGIN
DECLARE basic_salary INTEGER;
DECLARE new_salary INTEGER;
DECLARE cur1 CURSOR FOR SELECT salary FROM employee;
OPEN cur1;
l1:LOOP
FETCH cur1 INTO basic_salary;
IF basic_salary>2500 THEN
SET @new_salary := 500;
SET @basic_salary := @basic_salary - @new_salary;
else
SET @new_salary := 200;
SET @basic_salary := @basic_salary - @new_salary;
END IF;
END LOOP;
SELECT emp_name, salary, basic_salary AS 'Salary after taxes' FROM employee; 
END

I have got this error:

zero rows fetched selected or processed

Upvotes: 0

Views: 173

Answers (1)

Gaurav Lad
Gaurav Lad

Reputation: 1808

Read about exit handlers and Continue handlers MySQL handlers

Try something like below:

CREATE DEFINER=`root`@`localhost` PROCEDURE `tax_to_salary`()
BEGIN
DECLARE basic_salary INTEGER;
DECLARE new_salary INTEGER;
DECLARE cur1 CURSOR FOR SELECT salary FROM employee;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;
l1:LOOP
FETCH cur1 INTO basic_salary;
IF basic_salary>2500 THEN
SET @new_salary := 500;
SET @basic_salary := @basic_salary - @new_salary;
else
SET @new_salary := 200;
SET @basic_salary := @basic_salary - @new_salary;
END IF;
END LOOP;
SELECT emp_name, salary, basic_salary AS 'Salary after taxes' FROM employee; 
END

Upvotes: 1

Related Questions