user34682 user34682 - 4 months ago 19
SQL Question

Error creating stored procedure

MariaDB 10 on Windows

When creating the following stored procedure in MySQL Workbench, I get error 1064. The error is marked on the last line "LIMIT 1". Trying to create it in HeidiSQL also returns error 1064. This suggests a missing parentheses, but none is missing.

Any help appreciated:

CREATE PROCEDURE `available_room` (in p_client_id int(11), in p_room_id int(11), in dateQF date, in a_gender varchar(3))
BEGIN
SELECT
room_name,
room_type_name,
counted,
num_guests,
minimum,
room_id AS room_id_selected
FROM (SELECT * FROM room
WHERE room_id=p_room_id AND
(NOT EXISTS (
SELECT * FROM invoice
WHERE client_id=p_client_id AND
product_type = 'LODGING' AND
dateQF BETWEEN date1 AND DATE(DATE_ADD(date2, INTERVAL -1 DAY)) AND
commit_invoice=1 AND
room_id = product_id AND
gender != a_gender
) OR
(NOT EXISTS (
SELECT * FROM invoice
WHERE client_id=p_client_id AND
product_type = 'LODGING' AND
dateQF BETWEEN date1 AND DATE(DATE_ADD(date2, INTERVAL -1 DAY)) AND
commit_invoice=1 AND
room_id = product_id
))))A
LEFT JOIN room_type_content ON
room_type_content.room_type_id=A.room_type AND
language_id='en'
LEFT JOIN(SELECT product_id, count(product_id)AS counted, MIN(NULLIF(quantity,0))AS minimum FROM invoice
WHERE gender =a_gender AND
client_id=p_client_id AND
product_type = 'LODGING' AND
dateQF BETWEEN date1 AND DATE(DATE_ADD(date2, INTERVAL -1 DAY)) AND
(commit_invoice=1 OR commit_invoice=3) AND
invoice_set !='SYSTEM'
GROUP BY product_id)B
ON B.product_id=p_room_id
ORDER BY counted DESC
LIMIT 1
END

Answer

You forgot a semi-colon after LIMIT 1.

You also need to change the standard delimiter before and after creating the procedure.

Documentation: CREATE PROCEDURE and CREATE FUNCTION Syntax

Fixed version of your code:

DELIMITER $$
CREATE PROCEDURE `available_room` (in p_client_id int(11), in p_room_id int(11), in dateQF date, in a_gender varchar(3))
BEGIN
SELECT
    room_name,
    room_type_name,
    counted, 
    num_guests,
    minimum,
    room_id AS room_id_selected 
FROM    (SELECT * FROM room
WHERE   room_id=p_room_id AND
(NOT EXISTS (
SELECT  * FROM invoice
WHERE   client_id=p_client_id AND
    product_type = 'LODGING' AND 
    dateQF BETWEEN date1 AND DATE(DATE_ADD(date2, INTERVAL -1 DAY)) AND 
    commit_invoice=1 AND
    room_id = product_id AND
    gender != a_gender
    ) OR
(NOT EXISTS (
SELECT  * FROM invoice
WHERE   client_id=p_client_id AND
    product_type = 'LODGING' AND 
    dateQF BETWEEN date1 AND DATE(DATE_ADD(date2, INTERVAL -1 DAY)) AND  
    commit_invoice=1 AND
    room_id = product_id
    ))))A 
LEFT JOIN room_type_content ON
    room_type_content.room_type_id=A.room_type AND
    language_id='en'
LEFT JOIN(SELECT product_id, count(product_id)AS counted, MIN(NULLIF(quantity,0))AS minimum FROM invoice
WHERE   gender =a_gender AND 
    client_id=p_client_id AND
    product_type = 'LODGING' AND 
    dateQF BETWEEN date1 AND DATE(DATE_ADD(date2, INTERVAL -1 DAY)) AND  
    (commit_invoice=1 OR commit_invoice=3) AND
    invoice_set !='SYSTEM'
GROUP BY product_id)B
ON      B.product_id=p_room_id 
ORDER BY counted DESC
LIMIT 1;
END $$
DELIMITER ;
Comments