CODEWITHSUNDEEP
mysql
Sugumar Venkatesan
Sugumar Venkatesan

Reputation: 4038

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual mysql stored procedure error

I am just trying mysql stored procedure but I am getting the following 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 cur CURSOR for select birth_date from employees;

' at line 3

Code:

delimiter //
create procedure getminmaxbirthdate(OUT minage date,OUT maxage date)
    begin
        DECLARE minsofar,maxsofar,curage,done;

        DECLARE cur CURSOR for
            select birth_date from employees;

        set minsofar = 2040-12-12;
        set maxsofar = 0;
        set done = 0;

        DECLARE continue handler for not found
            set done = 1;

        open cur;
            while done = 0 do
                fetch birthdate into curage;
                if curage > maxsofar then
                    set maxsofar = curage;
                end if;
                if curage < minsofar then
                    set minsofar = curage;
                end if;
            end while;    
        close cur;

        set minage = minsofar;
        set maxage = maxsofar;
    end //
delimiter ;

Upvotes: 1

Views: 1114

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Is there a reason you are not using a simple query?

select min(birth_date), max(birth_date)
from employee;

This could be incorporated into a stored procedure, but that seems superfluous. Using a cursor is just a really, really, really bad practice -- even if you are learning SQL. Cursors should be avoided, except in the situations where they are necessary -- such as calling a stored procedure or invoking dynamic SQL for each row.

Your code also seems to be mixing up "ages" and "dates". It is a bit unclear what you really want to accomplish.

Upvotes: 1

nbk
nbk

Reputation: 49395

You have some bugs in your code

  • DECLARE without a tyoe is nocht valid
  • SET are only possible after DECLARE
  • and your CORSOR is cur not birthdate

you must check the datatypes for minsofar,maxsofar,curage,done

stored procedure:

   delimiter //
 create procedure getminmaxbirthdate(OUT minage date,OUT maxage date)
    begin
        DECLARE minsofar,maxsofar,curage,done VARCHAR(10);

        DECLARE cur CURSOR for
            select birth_date from employees;
            
        DECLARE continue handler for not found
            set done = 1;

        set minsofar = 2040-12-12;
        set maxsofar = 0;
        set done = 0;


        open cur;
            while done = 0 do
                fetch cur into curage;
                if curage > maxsofar then
                    set maxsofar = curage;
                end if;
                if curage < minsofar then
                    set minsofar = curage;
                end if;
            end while;    
        close cur;

        set minage = minsofar;
        set maxage = maxsofar;
    end //
delimiter ;

Upvotes: 1

Related Questions