MySQL - Insert Into 2 Tables on 1 Procedure

Question

I need to create a procedure for inserting records into 2 tables, but on the second table, I want to insert the last ID that was inserted on the first table. Could anyone help me with this?

This is my query

DELIMITER //
DROP PROCEDURE IF EXISTS ROOM_FEATURE_INSERT;
CREATE PROCEDURE ROOM_FEATURE_INSERT (propID INT, featID INT, featNme VARCHAR(50))
BEGIN
-- BEGIN CHECK
IF NOT EXISTS
(
SELECT rFeatureName FROM COMPANY_T3s71.PROPERTY_RFEATURE PRFE
INNER JOIN COMPANY_T3s71.ROOM_FEATURE RFEA ON PRFE.rFeatureID=RFEA.rFeatureID 
WHERE BINARY rFeatureName = featNme AND propertyID = propID
)
AND
(
SELECT rFeatureName FROM COMPANY_T3s71.ROOM_VIEW
WHERE BINARY rFeatureName = featNme
)
THEN
-- IF NOT EXISTS INSERT INTO 1st TABLE
INSERT INTO COMPANY_T3s71.ROOM_FEATURE (rFeatureName) VALUES (featNme);
END IF;
-- END CHECK

-- BEGIN CHECK 2nd TABLE
IF NOT EXISTS
(
SELECT propertyID, rFeatureID FROM COMPANY_T3s71.PROPERTY_RFEATURE
WHERE rFeatureID = featID AND propertyID = propID
)
THEN
-- IF NOT EXISTS INSERT INTO 2nd TABLE
INSERT INTO COMPANY_T3s71.PROPERTY_RFEATURE (propertyID, rFeatureID) VALUES (propID, featID);
END IF;
-- END CHECK 2nd TABLE

END

DELIMITER ;

How do we pass the featID param, when we just inserted it on the first INSERT query?

Thank you before hand.

Answer 1

Use SET featID = LAST_INSERT_ID(); after the first query and then use the variable

INSERT INTO COMPANY_T3s71.ROOM_FEATURE (rFeatureName) VALUES (featNme);
SET featID = LAST_INSERT_ID();

However, if the data is not insert at anytime then you have to make query in the if block to set the value for featID.