Mysql – Stored Procedure Error

MySQLstored-procedures

I am trying to create this procedure in MySQL and show the error mentioned below.

CREATE DEFINER=`root`@`%` PROCEDURE `synctables`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin

insert into tdest
select s.keyval,s.val1,s.val2,s.val3 from tsource s left join tdest d 
on s.keyval = d.keyval where d.keyval is null;

declare finished INTEGER DEFAULT 0;
declare keyv decimal;
declare v1 decimal;
declare v2 decimal;
declare v3 decimal;

DECLARE c1 CURSOR FOR select s.* from tsource s inner join tdest d on s.keyval = d.keyval;
DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET finished = 1;
open c1;

getvals: loop
fetch c1 into keyv,v1,v2,v3; 

if finished = 1 then
leave getvals;
end if;

update tdest set val1=@v1,val2=@v2,val3=@v3 where keyval=@keyv;

end loop getvals; 
close c1;

end

Error de SQL (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
declare finished INTEGER DEFAULT 0;
declare keyv decimal;
declare v1 decimal;
at line 13 */

When I delete this line the procedure is ok:

   insert into tdest
    select s.keyval,s.val1,s.val2,s.val3 from tsource s left join tdest d 
    on s.keyval = d.keyval where d.keyval is null;

Any ideas why the insert into is giving me an error?

Best Answer

MySQL is particular about the order in which you do things in a CREATE PROCEDURE/FUNCTION statement. I'll be burned if I can find it in the docs right now, beyond a cursory mention under DECLARE Syntax, but the order MUST BE

Header
Declare variables
Declare cursors
Declare handlers
Do actual stuff

With your INSERT ... SELECT in place, you're attempting to declare variables after you've begun "doing stuff", which isn't allowed. Move the INSERT ... SELECT after the last of your DECLAREs, and things should work much better.

Related Question