Question 286100 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – why this query always return that the table is booked?

drop procedure if exists AddValidBooking;
DELIMITER //
CREATE PROCEDURE AddValidBooking(IN booking_date DATE, IN table_number INT)
BEGIN
    DECLARE booked BOOLEAN;

    START TRANSACTION;
    
    SELECT CASE WHEN EXISTS (
        SELECT 1
        FROM Bookings
        WHERE DATE(Booking_Date) = DATE(booking_date) AND Table_Number = table_number
    ) THEN TRUE ELSE FALSE END INTO booked;


    IF booked THEN
        ROLLBACK;
        SELECT CONCAT("table ", table_number, " is booked - booking canceled") AS booking_status;
    ELSE
        SELECT CONCAT("table ", table_number, " is free - booked successfully") AS booking_status;
        INSERT INTO Bookings (Booking_Date, Table_Number) VALUES (booking_date, table_number);
        COMMIT;
    END IF;
END //
DELIMITER ;


CALL AddValidBooking('2023-11-7', 2);

It alwyas return table 2 is booked although it is free
I tried many combinations of dates with table numbers and it still returning the same result

Tags:

2

Answers


  1. 0

    It says here that

    A local variable should not have the same name as a table column. If
    an SQL statement, such as a SELECT ... INTO statement, contains a
    reference to a column and a declared local variable with the same
    name, MySQL currently interprets the reference as the name of a
    variable.

    You could change your query to:

    WHERE DATE(Bookings.Booking_Date) = DATE(booking_date) AND Bookings.Table_Number = table_number

    Or better, change the name of parameters.

    Login or Signup to reply.
  2. 0

    I like to use a leading underscore on proc arguments and local DECLAREd variables. That avoids confusion in Table_Number = table_number. Note that case-folding applies; so that test is always ‘true’. (Frankly, I don’t know whether it is using the proc arg or the table column!)

    If Bookings.Booking_Date is declared DATE (which it should be), then don’t bother using the DATE() function. Ditto for the argument _booking_date. Note: The string '2023-11-7' works fine as a DATE literal.

    DECLARE booked BOOLEAN;
SELECT ... EXISTS(...) THEN TRUE ELSE FALSE END INTO booked;
IF (booked) THEN

    Can be simplified

    IF (EXISTS(...)) THEN

    That avoids booked and TRUE/FALSE from a function (EXISTS()) that already returns true/false.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search