CODEWITHSUNDEEP
mysqlstored-procedures
HowIsIt
HowIsIt

Reputation: 33

Variables in stored procedure MySQL

I got a problem with this SIMPLE stored procedure. I want to save LAST_INSERT_ID in a variable and then assign it for next insert.

    DELIMITER //
    CREATE PROCEDURE Adda(
       Name varCHAR(45),Surrname varCHAR(45),City varCHAR(45),PhoneNumber varCHAR(45),photo varCHAR(45))
    BEGIN
    START TRANSACTION;
       INSERT INTO Personal(Name,Surrname)
       VALUES(Name,Surrname);
       INSERT INTO Addres(Id_Personal_Address,Country, City) 
       VALUES(LAST_INSERT_ID(),Country,City);
--i think here i should assign LAST_INSER_ID to variable
        INSERT INTO Images(Id_Personal_Address,photo)
        VALUES(Photo); -- and here use it
    COMMIT;
    END//
    DELIMITER ;

I really don't know how to do this.

Upvotes: 3

Views: 6104

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562300

You know you can call LAST_INSERT_ID() as part of any expression, including the next INSERT:

   INSERT INTO Addres(Id_Personal_Address,Country, City) 
   VALUES(LAST_INSERT_ID(),Country,City);

   INSERT INTO Images(Id_Personal_Address,photo)
   VALUES(LAST_INSERT_ID(), Photo); -- and here use it

But LAST_INSERT_ID() always returns the id value generated by the last INSERT. If your insert to Address has an auto-inc key, this becomes the new "last" id generated.

So if you need to use the value more than once, you should declare a local variable for it, because the INSERT into Images would change the last insert id.

You can declare a local variable in a procedure. This must come right after the BEGIN

BEGIN
    DECLARE personalId INT DEFAULT NULL;

Then use it later:

    SET personalId = LAST_INSERT_ID();

    INSERT INTO Addres(Id_Personal_Address, Country, City) 
    VALUES(personalId, Country, City);

    INSERT INTO Images(Id_Personal_Address, photo)
    VALUES(personalId, Photo);

Tip: Choose a variable name that is distinct from your column names to make your code clear.

So what's the deal with using @ before a variable?

You can use a variable like @addressId without doing a DECLARE first. But the variable will become a User-Defined Session Variable and will retain its value after your procedure exits. No harm done, unless you accidentally overwrite variables from the session scope. Or if you write a stored procedure that calls another stored procedure, and both of them are trying to use the same variable name.

Using DECLARE creates a variable that is truly local to the scope of the procedure.

What's even more confusing is that you can declare a variable of a given name, and then use the same name with an @ but that's a different variable.

For example, here's a demo tested on MySQL 8.0.0:

mysql> delimiter ;;
mysql> create procedure foo()
    -> begin
    ->  declare last_insert_id int default 0;
    ->  set @last_insert_id = 1234;
    ->  select last_insert_id;
    -> end;;
mysql> delimiter ;

mysql> call foo();
+----------------+
| last_insert_id |
+----------------+
|              0 |
+----------------+

mysql> select @last_insert_id;
+-----------------+
| @last_insert_id |
+-----------------+
|            1234 |
+-----------------+

Upvotes: 2

Darshan Mehta
Darshan Mehta

Reputation: 30819

You need to declare a variable and use set to assign the value, e.g.:

DELIMITER //
    CREATE PROCEDURE Adda(
       Name varCHAR(45),Surrname varCHAR(45),City varCHAR(45),PhoneNumber varCHAR(45),photo varCHAR(45))
    BEGIN
    DECLARE last_insert_id INT;
    START TRANSACTION;
       INSERT INTO Personal(Name,Surrname)
       VALUES(Name,Surrname);
       SET last_insert_id = LAST_INSERT_ID(); --assignment
       INSERT INTO Addres(Id_Personal_Address,Country, City) 
       VALUES(last_insert_id,Country,City);
        INSERT INTO Images(Id_Personal_Address,photo)
        VALUES(last_insert_id, Photo); -- use
    COMMIT;
    END//
    DELIMITER ;

Upvotes: 4

Related Questions