CODEWITHSUNDEEP
mysqlsqlstored-procedurescursor
am90
am90

Reputation: 201

error in loop of procedure using a cursor

I have this MySQL procedure where I used a cursor to:

CREATE DEFINER=`root`@`localhost` PROCEDURE `tax_to_salary`()
BEGIN
DECLARE basic_salary INTEGER;
DECLARE new_salary INTEGER;
DECLARE done INTEGER;
declare count INTEGER;

DECLARE counter INTEGER default 0;
DECLARE cur1 CURSOR FOR SELECT salary FROM employee;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SELECT count(id) INTO count FROM employee;
SET @counter:=0;

OPEN cur1;
l1:LOOP
FETCH cur1 INTO basic_salary;
SET @counter:=@counter+1;
IF @counter>count THEN
leave l1;
end if;
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;
SELECT emp_name, salary, basic_salary AS 'Salary after taxes' FROM employee; 

END LOOP;
END

And I got this result:

enter image description here

But my procedure should remove 500 from all salaries over 2500 and remove 200 from salaries less than 2500. I tried to put the final SELECT query inside the loop but I get 5 tabs and every tab contain the same of image below.

Upvotes: 0

Views: 56

Answers (1)

Drew
Drew

Reputation: 24949

Schema

create table employee
(   id int auto_increment primary key,
    emp_name varchar(100) not null,
    salary int not null
);

insert employee (emp_name,salary) values
('John',4400),
('Sarah',2700),
('Peter',2150),
('Ali',2650),
('Ashley',2650);

Note your language was greater than 2500 and also you said less than 2500. yet it has no condition for salary equaling 2500 exactly. So the below is one fix to that concept (otherwise there is no reduction).

Case when

best for many conditions, not that yours has it

select emp_name,salary, 
CASE when salary>=2500 then salary-500 
ELSE 
    salary-200 
END as modified_salary 
from employee;
+----------+--------+-----------------+
| emp_name | salary | modified_salary |
+----------+--------+-----------------+
| John     |   4400 |            3900 |
| Sarah    |   2700 |            2200 |
| Peter    |   2150 |            1950 |
| Ali      |   2650 |            2150 |
| Ashley   |   2650 |            2150 |
+----------+--------+-----------------+

If

for simple conditions like yours

select emp_name,salary,  
if(salary>=2500,salary-500,salary-200) as modified_salary  
from employee;
+----------+--------+-----------------+
| emp_name | salary | modified_salary |
+----------+--------+-----------------+
| John     |   4400 |            3900 |
| Sarah    |   2700 |            2200 |
| Peter    |   2150 |            1950 |
| Ali      |   2650 |            2150 |
| Ashley   |   2650 |            2150 |
+----------+--------+-----------------+

There is no reason to be using a row-by-row cursor the way you are. That is what people sometimes do just starting out with sql. Not only are they slow, often unbearably slow, but they keep you from harnessing the power of relations that make sql shine.

Said another way, you are trying to write procedural code and getting in the middle of it all by helping the sql engine figure it out with that mindset. It doesn't want it that way for optimization. You can, but you will slow it down horribly.

The reason you are getting multiple tabs as you say is that with your strategy, even if it worked well number-wise, each select statement returns a result set. And by going the dangerous cursor route, you returned five of them.

Upvotes: 1

Related Questions