Search code examples
mysqlmacosstored-proceduresterminalprocedure

Can't find where is the MySQL syntax error


delimiter %%
create procedure getFileID(in fname varchar(100), out fId int)
begin
    select ID from File
    where Name = fname
    into fId;
end %%
delimiter ;

delimiter $$
create procedure FileINFO(in fname varchar(100))
begin
    declare SMS_done, Par_don boolean default false;
    declare SMSID int;
    declare SMSName varchar(100) default "";
    declare SMSCode varchar(100) default "";
    declare ParamName varchar(100) default "";
    declare fId int default 0;
    call getFileID(fname, fId);
    declare c1 cursor for select ID, Code, Name from SMSTemplate where F_ID = fId;
    declare continue handler for not found set SMS_done = true;
    open c1;
    SMS_loop : loop
        fetch from c1 into SMSID, SMSCode, SMSName;
            if SMS_done then
                close c1;
                leave SMS_loop;
            end if;
        block2 : begin
        declare c2 cursor for 
            select Name from ParameterType where ST_ID = SMSID;
        declare continue handler for not found set Par_done = true;
        open c2;
        Par_loop : loop
            fetch from c2 into ParamName;
            if SMS_done then
            set SMS_done = false;
            close c2;
            leave Par_loop;
            end if;
        insert into FileDetails
        (FileName, SMSName, SMSCode, ParamName)
        values
        (fname, SMSName, SMSCode, ParamName);
        end loop Par_loop;
        end block2;
    end loop SMS_loop;
    select * from FileDetails;
end $$
delimiter ;

and i get that error

ERROR 1064 (42000): 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 c1 cursor for select ID, Code, Name from SMSTemplate where F_ID = fId;' at line 10

Solution

  • for starters get the declares all grouped together at the top. Also there is no such thing as fetch into so those two things were fixed.

    Par_don / Par_done had a typo in the declare.

    Also, for sanity, have a drop if exist for the stored proc right above each one, so it is easily scripted and maintained.

    drop procedure if exists FileINFO;
    delimiter $$
    create procedure FileINFO(in fname varchar(100))
    begin
        declare SMS_done, Par_done boolean default false;
        declare SMSID int;
        declare SMSName varchar(100) default "";
        declare SMSCode varchar(100) default "";
        declare ParamName varchar(100) default "";
        declare fId int;
        declare c1 cursor for select ID, Code, Name from SMSTemplate where F_ID = fId;
        declare continue handler for not found set SMS_done = true;
        call getFileID(fname,  fId );
    
    
    
        open c1;
    
        SMS_loop : loop
            fetch c1 into SMSID, SMSCode, SMSName;
                if SMS_done then
                    close c1;
                    leave SMS_loop;
                end if;
            block2 : begin
            declare c2 cursor for 
                select Name from ParameterType where ST_ID = SMSID;
            declare continue handler for not found set Par_done = true;
            open c2;
            Par_loop : loop
                fetch from c2 into ParamName;
                if SMS_done then
                set SMS_done = false;
                close c2;
                leave Par_loop;
                end if;
            insert into FileDetails
            (FileName, SMSName, SMSCode, ParamName)
            values
            (fname, SMSName, SMSCode, ParamName);
            end loop Par_loop;
            end block2;
        end loop SMS_loop;
        select * from FileDetails;
    end $$
    delimiter ;