MySQL Forums
Forum List  »  Stored Procedures

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

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL syntax error.. Please help...
2791
September 02, 2013 03:06PM
1201
September 07, 2013 07:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.