Bug #25269 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

• Submitted: 25 Dec 2006 11:18
• Modified: 25 Dec 2006 11:35
• Reporter: Shuja Shuja
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: mysql 5.0.22
• OS: unix
• CPU Architecture: Any

[25 Dec 2006 11:18] Shuja Shuja

Description:
I could not wirte the normal select query in the stored procedure if i have defined cursor in that, so this fraqment of code couldnt compile, i don't know wats going on.

create procedure proc12()

begin
        declare v_col1 int;
        declare cnt int;
         declare i int default 0;
        select count(*) into cnt from mytable;
	DECLARE cursor1 CURSOR FOR SELECT * from mytable;
        	       
	open cursor1;
        
LOOP1: loop  fetch cursor1 into  v_col1;
            if i<=cnt then
                close cursor1;
                leave LOOP1;
            end if;
            insert into common (com) values(v_col1); 
            set i=i+1;
        end loop LOOP1;
end ;//

How to repeat:
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 cursor1 CURSOR FOR SELECT a  from firs
t;

[25 Dec 2006 11:35] Valeriy Kravchuk

Thank you for a problem report. Sorry, but it is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/declare.html, and compare that to your code:

         declare i int default 0;
        select count(*) into cnt from mytable;
	DECLARE cursor1 CURSOR FOR SELECT * from mytable;

You have DECLARE, then SQL-statement (SELECT ... INTO), and then DECLARE again! Hence the error message.