mysql stored procedure syntax inconsistency

Question

I have a stored procedure which is syntactically acceptable on my local PC, where I have MySQL workbench 5.2.44 and I think my local version of MySQL is 5.5.29 judging by the folder names. Here is the code, which I have reduced to a minimum for illustration

DELIMITER $$

CREATE PROCEDURE `usp_insert_booking_test_2`(
        IN  p_dtmTimeSlot DATETIME  
     )

BEGIN 

     INSERT INTO tblBookings (dtmTimeSlot)
     SELECT p_dtmTimeSlot
     FROM tblBookings
     WHERE NOT EXISTS (SELECT * FROM tblBookings WHERE dtmTimeSlot = p_dtmTimeSlot) LIMIT 1;

END$$

As you can see, I am trying to ensure I do not create two bookings in the same time slot. Yes, I have a constraint on the table to do this also. Belt & braces. When I try to add this procedure on my remote host (running MySQL version 5.1.68-cll), it says

#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 '' at line 12

Line 12 is the WHERE NOT EXISTS line. I have tried removing LIMIT 1, that makes no difference. Is there a way to write the query so that MySQL 5.1 will accept it?

The Last Line should be `END $$` (Space mission) And the line missing after the last line: `DELIMITER ;` — Kovge, Apr 18 '13 at 07:44
A space missing after `END` Same question: http://stackoverflow.com/questions/1102109/mysql-delimiter-syntax-errors — Kovge, Apr 18 '13 at 07:45

Bharat Chodvadiya · Answer 1 · 2013-04-18T13:06:07.783

0

Use this.

DELIMITER $$

CREATE PROCEDURE `usp_insert_booking_test_2`(
    IN  p_dtmTimeSlot DATETIME  
 )

BEGIN 

 INSERT INTO tblBookings (dtmTimeSlot)
 SELECT p_dtmTimeSlot
 FROM tblBookings
 WHERE NOT IN (SELECT * FROM tblBookings WHERE dtmTimeSlot = p_dtmTimeSlot LIMIT 1);

END $$
DELIMITER ;

here somevalue is that you want to insert.

edited Apr 18 '13 at 13:06

answered Apr 18 '13 at 09:27

Bharat Chodvadiya

1,644
4
20
31

Neither of the suggestions work. The suggested answer breaks one SQL statement into two unrelated statements which change the semantics completely. The suggestion that a space is missing does not fix the error. – DJDave Apr 18 '13 at 10:31

score 0 · Answer 2 · answered Apr 18 '13 at 12:13

0

i needed to add

DELIMITER ;

to the end of the code as shown. No idea why.

answered Apr 18 '13 at 12:13

DJDave

865
1
13
28

mysql stored procedure syntax inconsistency

2 Answers2