ERROR 1064 , in stored procedure
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.
Subject
Views
Written By
Posted
ERROR 1064 , in stored procedure
3698
January 27, 2011 04:52AM
1395
January 27, 2011 05:07AM
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.