CODEWITHSUNDEEP
mysqlstored-procedures
Shubham Nigam
Shubham Nigam

Reputation: 3944

Unable to find the error in code for mysql stored procedure

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 : enter image description here

Upvotes: 0

Views: 101

Answers (2)

duduwe
duduwe

Reputation: 1260

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.

Upvotes: 1

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

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 $$

Upvotes: 0

Related Questions