Error #1064 in stored procedure in mysql

0
votes

if cant to understand my english is because my english is bad.... i will try to explain my problem as best as possible.

I try to create a stored procedure in mysql (phpmyadmin) but i have this 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 '' at line 3

This is the code of stored procedure:

CREATE PROCEDURE `sp_user_insert` (IN `sp_email` varchar(255), IN `sp_password` text, IN `sp_role` int)
BEGIN
    DECLARE `sp_encrypted_password` text;
    SET `sp_encrypted_password` = SELECT MD5(`sp_encrypted_password`);
    INSERT INTO `user`( `user_email`, `user_password`, `user_role_id`) VALUES ( `sp_email`, `sp_encrypted_password`, `sp_role`);
END

And this is the code of the table user:

CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_email` varchar(255) NOT NULL,
  `user_password` text NOT NULL,
  `user_role_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`)
);

PD: I'm sorry, my english is bad :C

2
mysqlstored-proceduresphpmyadmin

2 Answers

0
votes

This will help. Try once.

DELIMITER $$;
CREATE PROCEDURE sp_user_insert (IN sp_email varchar(255), IN sp_password TEXT, IN sp_role INT(20))
BEGIN
    DECLARE sp_encrypted_password TEXT;
    SELECT MD5(sp_password) INTO sp_encrypted_password;
    INSERT INTO user( user_email, user_password, user_role_id) VALUES ( sp_email, sp_encrypted_password, sp_role);
END
0
votes

See if this helps, you shouldn't need the SELECT to get the MD5 hash.

CREATE PROCEDURE `sp_user_insert` (IN `sp_email` varchar(255), IN `sp_password` text, IN `sp_role` int)
BEGIN
    DECLARE `sp_encrypted_password` text;
    SET `sp_encrypted_password` = MD5(`sp_encrypted_password`);
    INSERT INTO `user`( `user_email`, `user_password`, `user_role_id`) VALUES ( `sp_email`, `sp_encrypted_password`, `sp_role`);
END