tracing my procedure is not working probably

[y]

hi

 

i made a procedure to add into two tables at once

but i tried to trace it's errors to rollback and it not working as i supposed

 

here is the procedure with the error handler

 

create procedure sp_multi_insert
(in p_name varchar(50),in p_address varchar(100),in p_telephone varchar(20),in p_notes varchar(100),
out p_return_code tinyint unsigned)
begin 
declare exit handler for sqlexception
begin 
set p_return_code=1;
rollback;
end;
declare exit handler for sqlwarning
begin 
set p_return_code=2;
rollback;
end;
declare exit handler for not found
begin
set p_return_code=3;
rollback;
end;
start transaction;
insert into emp(name) values(p_name);
insert into emp_det(id,address,telephone,notes)
values(last_insert_id(),p_address,p_telephone,p_notes);
commit;
set p_return_code=0;
end

 

but every time i tried to call the procedure even if i passed wrong params or null

the return code comes back with 0

 

call sp_multi_insert('','','','',@x);

select @x;

 

no values returns form sqlwarning handler or sqlexceptions

why ??? what's wrong with the procedure statment or the calling ???

 

or who to trace the bug and rollback ????

 

 

 

[y]

 

help plz

[y]

 

up

[fenway]

How are you tracing this? Usually one uses INSERTs.