MySQL syntax error.. Please help...
Posted by:
Jassm Rahma
Date: September 02, 2013 03:06PM
Hi,
I am getting the following error on MySQL stored procedure but I can't understand the reason! can any help please..
here is the error followed by full code:
[SQL]
IF EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
BEGIN
SET @param_days_since_last_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_noorder by created_date DESC LIMIT 1)));
[Err] 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 'IF EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
BEGIN
SET' at line 1
full code:
BEGIN
DECLARE param_item_id_v1 int;
DECLARE param_item_id_v2 int;
DECLARE param_file_cutoff_days int;
DECLARE param_days_since_last_visit int;
DECLARE param_account_number BIGINT;
DECLARE param_price_list_id INT;
DECLARE param_v1_fees DOUBLE(11, 5);
DECLARE param_v2_fees DOUBLE(11, 5);
DECLARE param_item_description_v1 VARCHAR(255);
DECLARE param_item_description_v2 VARCHAR(255);
SET param_account_number = (SELECT account_number FROM visits WHERE visit_id = param_visit_id);
SET param_price_list_id = (SELECT price_list_id FROM visits WHERE visit_id = param_visit_id);
SET param_item_id_v1 = (SELECT item_id FROM price_list_items WHERE item_code = param_v1);
SET param_item_description_v1 = (SELECT item_name FROM price_list_items WHERE item_id = param_item_id_v1);
SET param_v1_fees = (SELECT price_list_item_prices.item_price FROM price_list_item_prices INNER JOIN price_list_items ON price_list_item_prices.item_id = price_list_items.item_id WHERE price_list_items.item_code = param_v1 AND price_list_item_prices.price_list_id = param_price_list_id);
SET param_item_id_v2 = (SELECT item_id FROM price_list_items WHERE item_code = param_v2);
SET param_item_description_v2 = (SELECT item_name FROM price_list_items WHERE item_id = param_item_id_v2);
SET param_v2_fees = (SELECT price_list_item_prices.item_price FROM price_list_item_prices INNER JOIN price_list_items ON price_list_item_prices.item_id = price_list_items.item_id WHERE price_list_items.item_code = param_v2 AND price_list_item_prices.price_list_id = param_price_list_id);
SET param_file_cutoff_days = (SELECT system_param_value FROM system_param WHERE system_param_name = 'FILE_CUTOFF_DAYS');
IF EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
BEGIN
SET param_days_since_last_visit = DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_no order by created_date DESC LIMIT 1));
IF (param_days_since_last_visit > param_file_cutoff_days) THEN
BEGIN
INSERT INTO visit_items (file_no, visit_id, account_number, item_id, item_code, item_description, item_amount, created_user) VALUES (param_file_no, param_visit_id, param_account_number, param_item_id_v1, param_v1, param_item_description_v1, param_v1_fees, param_created_user);
END;
ELSE
BEGIN
INSERT INTO visit_items (file_no, visit_id, account_number, item_id, item_code, item_description, item_amount, created_user) VALUES (param_file_no, param_visit_id, param_account_number, param_item_id_v2, param_v2, param_item_description_v2, param_v2_fees, param_created_user);
END;
END IF;
END;
ELSE
BEGIN
INSERT INTO visit_items (file_no, visit_id, account_number, item_id, item_code, item_description, item_amount, created_user) VALUES (param_file_no, param_visit_id, param_account_number, param_item_id_v1, param_v1, param_item_description_v1, param_v1_fees, param_created_user);
END;
END IF;
UPDATE visits SET is_received = TRUE, recieved_user = param_created_user, recieved_date = NOW() WHERE visit_id = param_visit_id;
END