CODEWITHSUNDEEP
mysqlmariadbprocedure
T.S
T.S

Reputation: 55

error 1064 while creating mysql procedure

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?

Upvotes: 0

Views: 69

Answers (1)

Mangesh Auti
Mangesh Auti

Reputation: 1153

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

Upvotes: 1

Related Questions