CODEWITHSUNDEEP
mysqlsqlstored-procedures
DNB5brims
DNB5brims

Reputation: 30568

How can I use the newly insert record's id in MySQL's PROCEDURE?

For example, I have a users table, and waitingUsers table: They work like this:

`Users`:
id
name

and 

`WaitingUsers`:
id
user_id

Both Users and WaitingUsers's id is auto increment. I would like to have one PROCEDURE to create the users, and the new id is generated after the users is created, and use it inserts into waitingUsers. How can I do so? Thanks.

Upvotes: 1

Views: 53

Answers (1)

John Woo
John Woo

Reputation: 263703

try this one,

DELIMITER $$
CREATE PROCEDURE InsertRecord(IN _name VARCHAR(30))
BEGIN
    INSERT INTO Users VALUES (_name);
    INSERT INTO WaitingUsers VALUES (LAST_INSERT_ID());
END $$
DELIMITER ;

or

DELIMITER $$
CREATE PROCEDURE InsertRecord(IN _name VARCHAR(30))
BEGIN
    INSERT INTO Users (id, Name) VALUES (NULL, _name);
    INSERT INTO WaitingUsers (id, user_id) VALUES (NULL, LAST_INSERT_ID());
END $$
DELIMITER ;

Upvotes: 3

Related Questions