CODEWITHSUNDEEP
mysqlsqldatabase
mrjasmin
mrjasmin

Reputation: 1270

Error in Sql Query - Can't find the solution

I'm having big troubles to find the error in my sql query.

CREATE FUNCTION freeSeats(bookingID INT)
RETURNS VARCHAR(30)
BEGIN
DECLARE numberBooked INT ;
DECLARE status VARCHAR(30);

SELECT count(*) FROM passenger WHERE Booking IN(SELECT Id FROM booking WHERE
Flight = (SELECT Flight FROM booking WHERE Id = bookingID)) INTO numberBooked;

IF (numberBooked > 59) THEN SET status =”No free seats”;
ELSE SET status =”OK”;
END IF;

RETURN status;
END;

I get this error message:

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 'free seats”; ELSE SET status =”OK”; END IF;

RETURN status; END' at line 10

I would appreciate some help.

Thanks in advance

Upvotes: 1

Views: 273

Answers (1)

John Woo
John Woo

Reputation: 263693

  • don't for get to change the DELIMITER
  • use single quotes

example,

DELIMITER $$
CREATE FUNCTION freeSeats(bookingID INT)
RETURNS VARCHAR(30)
BEGIN
    DECLARE numberBooked INT ;
    DECLARE status VARCHAR(30);

    SET numberBooked =
    (
    SELECT count(*) 
    FROM passenger 
    WHERE Booking IN 
        (   SELECT  Id 
            FROM    booking 
            WHERE   Flight = (SELECT Flight FROM booking WHERE Id = bookingID)
        ) 
    );

    IF (numberBooked > 59) THEN 
        SET status = 'No free seats';
    ELSE 
        SET status = 'OK';
    END IF;

    RETURN status;
END $$
DELIMITER ;

Upvotes: 1

Related Questions