CODEWITHSUNDEEP
mysqlstored-procedures
user2251406
user2251406

Reputation:

Mysql stored procedure does not produce expected result

I have a mysql stored procedure to fetch value from one table to another table. When I call the stored procedure it runs fine but problem is, it is inserting previous column value if the current column value is null.

Example : suppose second employee name column is null, since it is running in loop it is inserting first employee name in the place of second employee name but i want column value in my new table to be null if column value in my existing table is null.Below is my stored procedure.

CREATE  PROCEDURE `frstprocedure`()
BEGIN
DECLARE emp_name varchar(225);
DECLARE emp_age varchar(225);
DECLARE emp_dept varchar(225);

DECLARE masterCursor CURSOR for select empname,empage,empdept from Employee;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
SET done=0;
OPEN masterCursor;
my_loop: LOOP
FETCH masterCursor into emp_name,emp_age, emp_dept;
IF done THEN
CLOSE masterCursor;
LEAVE my_loop;
END IF;
insert into second_table values(emp_name,emp_age, emp_dept);
END LOOP my_loop;
END

Upvotes: 2

Views: 173

Answers (2)

user2261252
user2261252

Reputation:

You can also set default values while declaring your variables.see the code below

DECLARE emp_name varchar(225) default null;
DECLARE emp_age varchar(225) default null;
DECLARE emp_dept varchar(225) default null;

Upvotes: 1

Kishan_KP
Kishan_KP

Reputation: 4518

try adding following line after insert statement in your stored procedure,

SET emp_name=null;
SET emp_age=null;
SET emp_dept-null

this will initialize all the field values to null in every iteration, so correct values will be stored.

Upvotes: 3

Related Questions