MySQL syntax error.. Please help...

Jassim Rahma

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\_

Mycroft Holmes

Jassim Rahma wrote:

param_file_no

I don't see this declared as a variable!

Never underestimate the power of human stupidity RAH

Jassim Rahma

it's passed as parameters. sorry i forgot to metion it. it's like this:

IN param_file_no int, IN param_visit_id int, IN param_v1 int, IN param_v2 int, IN param_created_user int

so what could be the reason for my problem?!

Technology News @ www.JassimRahma.com

GuyThiebaut

I cannot immediately see an error so what I would suggest is running each assignment statement outside of the stored procedure noting down the results. Then use what you have noted down as the input for your exists clause. Keep working through the code in this manner until you find the error - yes, debugging is tedious however the gold at the end of the rainbow is finding the error. Good luck :)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Jassim Rahma

that's what I did and I got the error mentioned in my first post.

[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

Technology News @ www.JassimRahma.com

GuyThiebaut

Jassim Rahma wrote:

that's what I did

and yet what I am seeing in your code are variables such as visit_id and param_file_no rather than the hard-coded values of these variables. I don't need to see the values but I am not sure you understand what I was trying to communicate in my previous post - basically what I am saying is run each part of the stored procedure one line at a time noting the results then run the next line - each time running the line outside of the stored procedure.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Jassim Rahma

that's exactly what I did Christopher. All lines are giving correct results with no error. The line I am having problem with is the following:

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)));

That's the same line mentioned earlier.

Technology News @ www.JassimRahma.com

GuyThiebaut

Jassim Rahma wrote:

_noorder by

That's your issue - you are missing a space before the order keyword.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Jassim Rahma

no it's not. sorry i think it's paste issue. this is the line as it is in MySQL server;

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));

Technology News @ www.JassimRahma.com

GuyThiebaut

Replace

Jassim Rahma wrote:

LIMIT 1)

with

LIMIT 0,1)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Jassim Rahma

[SQL]

IF EXISTS(SELECT visit\_id FROM visits WHERE file\_no = 80016) THEN
BEGIN
	SET @param\_days\_since\_last\_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created\_date) FROM visits where file\_no = 80016 order by created\_date DESC LIMIT 0,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 = 80016) THEN
BEGIN
SET' at line 1

Technology News @ www.JassimRahma.com

Jassim Rahma

i have a feeling the problem is with the DATEDIFF but i don't know what is it..

Technology News @ www.JassimRahma.com

Jassim Rahma

hmm I was right :) the problem is with the DATEDIFF but I still don't know what is it.. I just used TO_DAYS instead and everything is working fine now.

SET param_days_since_last_visit = (SELECT TO_DAYS(NOW()) - TO_DAYS(created_date) FROM visits WHERE file_no = param_file_no ORDER BY created_date DESC LIMIT 1);

Thanks, Jassim Rahma[^] RMC[^] - Universal Medical Equipments[^]

Technology News @ www.JassimRahma.com