Thursday, 30 August 2018

MySQL Stored Procedure Does Not Work


I'm very new to MySQL stored procedures, and got the following error:


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 'DECLARE v_lang TINYINT(1) DEFAULT '1'; DECLARE cursor_lang CURSOR FOR SELECT `l' at line 7

When trying to build this stored procedure:
DELIMITER //
CREATE PROCEDURE UpdateUser(IN p_uid INT(11))
BEGIN

DECLARE v_last_login    TIMESTAMP   DEFAULT '2012-01-01 00:00:00' ;
SELECT `last_login`  INTO v_last_login FROM `user` WHERE `id`= p_uid;

DECLARE v_lang  TINYINT(1)  DEFAULT '1';

DECLARE cursor_lang CURSOR FOR SELECT `l_id` INTO v_lang FROM `user_lang` WHERE `user_id` = p_uid LIMIT 0 , 5;

DECLARE no_more_l   TINYINT(1)  DEFAULT 0;
DECLARE  CONTINUE HANDLER FOR NOT FOUND SET  no_more_l = 1;

OPEN cursor_lang;

FETCH  cursor_lang INTO v_lang_str;
REPEAT 

UPDATE user SET `last_login`=CURRENT_TIMESTAMP() WHERE `id`=p_uid AND `l_id` = v_lang_str;

 UNTIL  no_more_l = 1
 END REPEAT;
 CLOSE  cursor_lang;

END //
DELIMITER ;

What do I do wrong?

Are you sure it's ok to use a string as the default for a number variable?
try
DECLARE v_lang  TINYINT(1)  DEFAULT 1;

0 comments:

Post a Comment