MySQL Forums
Forum List  »  Stored Procedures

ERROR 1064 , in stored procedure
Posted by: obinna henry
Date: January 27, 2011 04:52AM

hi i have the following stores procedure and each time i try to compile it in workbench, i get the following errors:


ERROR 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 'v_allocation );

                        

            if done2=1 then

           ' at line 46

SQL Statement:

CREATE PROCEDURE `nysc`.`post_special` ()



block1:BEGIN

declare done int default 0;

declare v_course_code varchar(20);

declare v_course_count varchar(10);

declare v_state_posting varchar(10);

declare v_allocation int default 0;

-- get the number of students per special course

declare cursor_students_percourse cursor for

    select course_code , count(course_code) as course_count from datamain_mst

    where course_code in (select code from special_course_mst)

    group by course_code;
declare continue handler for not found
    begin
        set done = 1;
    end;

open cursor_students_percourse;

loopone:loop

    fetch cursor_students_percourse into v_course_code, v_course_count;    

    block2:begin

        declare done2 int default 0;

        declare cursor_state_allocation cursor for
            select state_code, amount, round((amount/100)*v_course_count) as allocation

            from allocation_ratio_mst
            where course_code=v_course_code;

            declare continue handler for not found
            begin
                set done2 = 1;
            end; 
        open cursor_state_allocation;

        looptwo:loop 

            fetch cursor_state_allocation into v_state_posting, v_allocation;
            

            update datamain_mst d set d.state_of_posting=v_state_posting, d.post_status='YES'

            where d.callup_no in (select e.callup_no from datamain_mst e 

                                    where e.post_status is null and 

                                    e.course_code=v_course_code and 

                                    e.state_origin not in 

                                    (select s.code from state_code_mst s

                                    where s.zone = (select p.zone from state_code_mst p where p.code=v_state_posting)) limit v_allocation );

                        

            if done2=1 then
                leave looptwo;
            end if;    

        end loop looptwo;        

    end block2;

    if  done =1 then
        leave loopone;
    end if;
 
end loop loopone;

END block1



Edited 2 time(s). Last edit at 01/27/2011 05:04AM by obinna henry.

Options: ReplyQuote


Subject
Views
Written By
Posted
ERROR 1064 , in stored procedure
3698
January 27, 2011 04:52AM
1103
January 27, 2011 06:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.