error 1064 while creating mysql procedure

Question

I am creating mySql procedure in HeidiSql:

   CREATE DEFINER=root@localhost PROCEDURE checkSchedule
 ( IN sDate date, IN eDate date, IN sTime time, IN eTime time, IN weekDay int(7), IN classId int(11) )
   BEGIN 
   select schedule.idSchedule 
       from schedule 
          where ( (sDate>=schedule.startDate and sDate<=schedule.endDate) 
       or (sDate<=schedule.startDate and eDate>=schedule.endDate) 
       or (eDate>=schedule.startDate and eDate<=schedule.endDate) ) 
       and ( (sTime>=schedule.startTime and sTime<=schedule.endTime) 
       or (sTime<=schedule.startTime and eTime>=schedule.endTime) 
       or (eTime>=schedule.startTime and eTime<=schedule.endTime) ) 
       and weekDay=schedule.weekDay and classId=schedule.classroom_idClassRoom; 
    END

and I am getting the next error:

    SQL Error (1064): check the manual corresponds your MariaDB server 
    version for the right syntax to use near '' at line 12

Any ideas how to solve this problem?

Not that difficult andsDate should be and sdate AND if you have set delimiters then things should be fine dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html — P.Salmon
– P.Salmon, Commented Jul 13, 2019 at 12:44
@P.Salmon actually, in my code andsDate is and sDate, it was problem of pasting code — T.S
– T.S, Commented Jul 13, 2019 at 12:53

Mangesh Auti · Accepted Answer · 2019-07-13 12:54:44Z

You need to change DELIMITER temporary to execute the procedure. Default DELIMITER is ; but while creating the procedure you use ; so it creates problems.

DELIMITER $$
CREATE DEFINER=root@localhost PROCEDURE checkSchedule
 ( IN sDate date, IN eDate date, IN sTime time, IN eTime time, IN weekDay int(7), IN classId int(11) )
   BEGIN 
   select schedule.idSchedule 
       from schedule 
          where ( (sDate>=schedule.startDate and sDate<=schedule.endDate) 
       or (sDate<=schedule.startDate and eDate>=schedule.endDate) 
       or (eDate>=schedule.startDate and eDate<=schedule.endDate) ) 
       and ( (sTime>=schedule.startTime and sTime<=schedule.endTime) 
       or (sTime<=schedule.startTime and eTime>=schedule.endTime) 
       or (eTime>=schedule.startTime and eTime<=schedule.endTime) ) 
       and weekDay=schedule.weekDay and classId=schedule.classroom_idClassRoom; 
    END$$

 DELIMITER ;

DEMO

Collectives™ on Stack Overflow

error 1064 while creating mysql procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related