0

I have procedure which should work in mysql workbench, however I receive an error on line 7:


create PROCEDURE addIndividualUser(
    var_login varchar(255),
    var_password varchar(255),
    var_email varchar (255)
    )
BEGIN
    DECLARE var_user_id int;          ---here is the issue
    START TRANSACTION;
    INSERT into user
        (login, password, email, is_active)
    VALUES
    (var_login,var_password, var_email,1);

    set var_user_id = LAST_INSERT_ID();
    if (var_user_id) > 0 THEN
        insert into individual_user ('user_id') values (var_user_id);
        COMMIT;
    ELSE
        ROLLBACK;
    END if;    
END;

This is the DDL for the two tables:

User table


CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `login` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `user`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `login` (`login`),
  ADD UNIQUE KEY `email` (`email`);

ALTER TABLE `user`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=101;

Individual_user table


CREATE TABLE `individual_user` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `individual_user`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);



ALTER TABLE `individual_user`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=51;

ALTER TABLE `individual_user`
  ADD CONSTRAINT `individual_user_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`);

Akina
  • 39,301
  • 5
  • 14
  • 25
  • 'user_id' - should be backticks not single quotes. And did you set delimiters? – P.Salmon Mar 24 '20 at 13:14
  • Does this answer your question? [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) – P.Salmon Mar 24 '20 at 13:14
  • You forget to re-assign `DELIMITER`, it seems... – Akina Mar 24 '20 at 13:22
  • yes it was issue with DELIMITER, it had to be set in beginning and end of query. Thanks very much for answers! – piotr w Mar 25 '20 at 14:43

0 Answers0