SQL PROCEDURES – Multi inserts

I’m trying to create SQL procedure that registers a user to the system. for that the user details need to save to a lot of tables, i make this procedure and its work but returns me an error:

14:47:53    call new_procedure  Error Code: 1242. Subquery returns more than 1 row  0.016 sec

here is my procedore:

 CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(
  firstname varchar(36), lastname varchar(36), email varchar(36), pass varchar(255), phone varchar(12))
BEGIN
START TRANSACTION;
   INSERT INTO users (`FIRST_NAME`, `LAST_NAME`,`EMAIL`,`PWD`,`PHONE`)
        VALUES(firstname, lastname, email, '123123', phone);
   SET @user_uniqe_id = (SELECT ID FROM users WHERE EMAIL=email);
   INSERT INTO resources (id) 
        VALUES(@user_uniqe_id);
COMMIT;
END

Answer

This code is the problem:

SET @user_uniqe_id = (SELECT ID FROM users WHERE EMAIL=email);

You think that one of the emails is referring to the parameter. But it is not. The where clause is comparing the column to itself.

I recommend giving parameters names that cannot be confused with columns:

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(
  in_firstname varchar(36),
  in_lastname varchar(36),
  in_email varchar(36),
  in_pass varchar(255),
  in_phone varchar(12)
)
BEGIN
START TRANSACTION;
   INSERT INTO users (`FIRST_NAME`, `LAST_NAME`,`EMAIL`,`PWD`,`PHONE`)
        VALUES(in_firstname, in_lastname, in_email, '123123', in_phone);
   SET @user_uniqe_id = (SELECT u.ID FROM users u WHERE u.EMAIL = in_email);
   INSERT INTO resources (id) 
        VALUES(@user_uniqe_id);
COMMIT;
END;

This still isn’t really the correct way to do this. You should be using last_insert_id(). That would look like:

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(
  in_firstname varchar(36),
  in_lastname varchar(36),
  in_email varchar(36),
  in_pass varchar(255),
  in_phone varchar(12)
)
BEGIN
START TRANSACTION;
   INSERT INTO users (`FIRST_NAME`, `LAST_NAME`, `EMAIL`, `PWD`, `PHONE`)
        VALUES (in_firstname, in_lastname, in_email, '123123', in_phone);
   SET @user_uniqe_id = LAST_INSERT_ID();

   INSERT INTO resources (id) 
        VALUES (@user_uniqe_id);
COMMIT;
END;