Unable to find the error in code for mysql stored procedure

Question

I have created one stored procedure in which every thing is written according code

DELIMITER $$;
CREATE DEFINER=`root`@`localhost` PROCEDURE `mark_all_read`(IN user_name VARCHAR(50),IN group_name VARCHAR(50),IN choice CHAR(10),IN nid CHAR(30))
BEGIN
IF choice = 'all' THEN
 UPDATE notifications SET STATUS = 'read' WHERE USER = user_name AND groupId = group_name;
ELSE
 UPDATE notifications SET STATUS = 'read' WHERE USER = user_name AND groupId = group_name AND noticationId = nid;
END IF; 
END $$
DELIMITER;

Error :

are you creating this procedure in new empty tab?? Then don't. To create a procedure right click on stored procedure >create stored procedure>then paste your code there without 'DEFINER=root@localhost. — Ankit Agrawal
– Ankit Agrawal, Commented Feb 5, 2016 at 12:43
sorry i didnt get you .. this screenshot is of query which was fired using SQLyog — Shubham Nigam
– Shubham Nigam, Commented Feb 5, 2016 at 12:47
but i created another procedure using the same thing in which IF condition was not there and it was working with that — Shubham Nigam
– Shubham Nigam, Commented Feb 5, 2016 at 12:48

duduwe · Accepted Answer · 2016-02-08 11:08:51Z

1

1.) check the first statement DELIMITER $$;, it should [just] be DELIMITER $$ with no semi-colon

2.) check the last statement DELIMITER;, it should be DELIMITER ; with space in between

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `mark_all_read`(IN user_name VARCHAR(50),IN group_name VARCHAR(50),IN choice CHAR(10),IN nid CHAR(30))
BEGIN
IF choice = 'all' THEN
 UPDATE notifications SET STATUS = 'read' WHERE USER = user_name AND groupId = group_name;
ELSE
 UPDATE notifications SET STATUS = 'read' WHERE USER = user_name AND groupId = group_name AND noticationId = nid;
END IF; 
END$$
DELIMITER ;

Explanation:

1.) DELIMITER $$; means you are modifying the default delimiter from ; to $$;. However, at the end of your code, the CREATE PROCEDURE statement was terminated by $$ (instead of $$;), which MySQL does not recognize. To make the solution simple, I used $$ and end the CREATE PROCEDURE with $$. You need to modify the delimiter because there are semi-colon ; delimiters within your procedure, else MySQL would not know which delimiter terminates the CREATE PROCEDURE statement.

2.) DELIMITER; (without space separator) means you are terminating the DELIMITER statement just like SELECT * FROM TABLE; and not changing the terminator from $$ back to ; It is also syntactically incorrect. DELIMITER ; (with space separator) simply changes the delimiter back to ;. Why? For one, your codes within your procedures are terminated by ;. MySQL would not be able to terminate the procedure statements on run-time if you forgot to do DELIMITER ; - you will get errors if you execute/call the procedure afterwards.

You may further read MySQL documentation regarding the matter. Hope explanation helps.

edited Feb 8, 2016 at 11:08

answered Feb 5, 2016 at 13:05

duduwe

1,32214 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shubham Nigam Over a year ago

It works.. wants to know why this semi colon spacing matters?

duduwe Over a year ago

@ShubhamNigam, I added "explanation" above. Hopefully it would help you understand the matter.

Shubham Nigam Over a year ago

ya thanks your explanation is quite helpful.. Keep Helping others.. :)

Ankit Agrawal · Accepted Answer · 2016-02-05 12:48:04Z

0

its working

CREATE PROCEDURE `mark_all_read`(IN user_name VARCHAR(50),IN group_name VARCHAR(50),IN choice CHAR(10),IN nid CHAR(30))
BEGIN
IF choice = 'all' THEN
 UPDATE notifications SET STATUS = 'read' WHERE USER = user_name AND groupId = group_name;
ELSE
 UPDATE notifications SET STATUS = 'read' WHERE USER = user_name AND groupId = group_name AND noticationId = nid;
END IF; 
END $$

answered Feb 5, 2016 at 12:48

Ankit Agrawal

2,4541 gold badge17 silver badges28 bronze badges

1 Comment

Shubham Nigam Over a year ago

so what is the issue with DEFINER=root@localhost

Collectives™ on Stack Overflow

Unable to find the error in code for mysql stored procedure

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related