Reference to dependency data column table in query inserting data-CodePudding

I am learning about MySql database. I have two tables like this ServiceTable(ServiceID(PK),..,Fee) TransactionTable(TransactionID(PK),ServiceID(FK),...,Fee) I write a produce to insert data to TransactionTable:

CREATE DEFINER = 'root'@'localhost'
PROCEDURE bbet.Proc_transactiontable_CreateTransaction(IN userID varchar(36),
IN serviceID varchar(36),
...,
IN amountMoney float
)
BEGIN
SELECT @fee = s.Fee FROM servicetypetable s WHERE s.ServiceID = serviceID;
INSERT INTO transactiontable (TransactionID, ServiceID,..., Fee, TotalMoney,  ModifiedDate)
VALUES (UUID(), serviceID, amountMoney,..., @fee, amountMoney * (1 - @fee), NOW());
END

I want to reference data in produce from Fee column of ServiceTable, but @fee get null in this case. How can I reference data in produce from Fee column of ServiceTable?

CodePudding user response：

CREATE DEFINER = 'root'@'localhost'
PROCEDURE bbet.Proc_transactiontable_CreateTransaction(
    IN userID varchar(36),
    IN serviceID varchar(36),
    ...,
    IN amountMoney float
    )
INSERT INTO transactiontable (TransactionID, ServiceID,..., Fee, TotalMoney,  ModifiedDate)
SELECT UUID(), serviceID, amountMoney,..., s.Fee, amountMoney * (1 - s.Fee), NOW()
FROM servicetypetable s 
WHERE s.ServiceID = serviceID;

PS. I do not recommend you to interfere local variables names and columns names. Use, for example, IN in_serviceID varchar(36)..