MySQL: using variables inside a SELECT statement

Please see below statement

INSERT INTO Ongoing_Fees (currentDate, Gross_Fee, Insurance_Fee, Submit_Fee)
    SELECT current_timestamp, New.Gross_Fee,
           @InsuranceFee := (New.Gross_Fee)*(p.E_Fee/100),
           @SubmitFee := @InsuranceFee-(p.s_Fee/100)
    FROM Portfolio p
    WHERE p.idPortfolio = New.idPortfolio;

      

        
        
        
      

    

Here I am using 2 users defining variables @InsuranceFee

and @SubmitFee

. As you can see, you can get @SubmitFee

by subtracting the value from @InsuranceFee

.

Now, for this to work, you first need to evaluate the value @InsuranceFee

. However, the MySQL doc is below.

Typically, other than SET statements, you should never assign a value to a user variable and read the value within the same expression. For example, to increment a variable, it's okay:

SET @a = @a + 1; For other statements such as SELECT, you might get the expected results, but this is not guaranteed. In the following, you might think MySQL will evaluate @a first and then do the second lesson:

SELECT @a, @a: = @a + 1, ...; However, the evaluation order for expressions containing user-defined variables is undefined.

So what is the best way I can use the variables internally SELECT

?

UPDATE

Is it possible to assign values ​​to variables first using a separate SELECT statement? something like below ...

 SET   @InsuranceFee :=    New.Gross_Fee * ((SELECT E_Fee FROM Portfolio WHERE idPortfolio = New.idPortfolio)/100);

 SET   @SubmitFee := @InsuranceFee - ((SELECT s_FeeFROM Portfolio WHERE idPortfolio = New.idPortfolio)/100)

    INSERT INTO Ongoing_Fees (currentDate, Gross_Fee, Insurance_Fee, Submit_Fee)
           current_timestamp, New.Gross_Fee,
                   @InsuranceFee ,
                   @SubmitFee