Control the data that Mysql sends to Nodejs

Question

I developed a Project that "clones" a proposal and its products, in addition to making some updates, but the return that this procedure returns to my Nodejs is not very useful to me.

It is possible to inform/control what Mysql (Procedure in case) should send as response to my back-end so that I can send that same reply to the Front-end.

PROCEDURE (still being finalised!):

I would like to send the value of the last inserted ID (LAST_INSERT_ID) to Nodejs!

DROP PROCEDURE IF EXISTS clone_quote_and_products;

delimiter //
CREATE PROCEDURE clone_quote_and_products(IN quoteID INT, IN editedClone TINYINT(1))

BEGIN

    IF (editedClone = 0) THEN
        INSERT INTO quotes (
            CreatedBy,
         QuoteType,
         AccountName,
         AccountOwner,
         AccountContactName,
         AccountNumber,
         PriceIncrease,
         InitialTerm,
         RenewalTerm,
         FreeMonths,
         EffectiveDate,
         CageUniqueSpaceID,
         URLIdOpportunity,
         IBX,
         QuoteStatus,
         HasTerm,
         NonStandardTerm,
         FreeMonthsORRamp,
         RampMonths,
         RampStartDate,
         RampEndDate,
         RampPerCent,
         Notes,
         MRR,
         NRR
      ) SELECT
         CreatedBy,
         QuoteType,
         AccountName,
         AccountOwner,
         AccountContactName,
         AccountNumber,
         PriceIncrease,
         InitialTerm,
         RenewalTerm,
         FreeMonths,
         EffectiveDate,
         CageUniqueSpaceID,
         URLIdOpportunity,
         IBX,
         QuoteStatus,
         HasTerm,
         NonStandardTerm,
         FreeMonthsORRamp,
         RampMonths,
         RampStartDate,
         RampEndDate,
         RampPerCent,
         Notes,
         MRR,
         NRR  
        FROM quotes
        WHERE quotes.id = quoteID;
   END IF;

   UPDATE quotes AS Q
   SET Q.Version = CONCAT('[{"ID":', (SELECT LAST_INSERT_ID()), ', "Version": 1}]')
   WHERE Q.id = (SELECT LAST_INSERT_ID());

END //
delimiter ;

# CALL clone_quote_and_products(802, 0);

Nodejs:

function Quote_CloneDAO(connection) {
    this._connection = connection;
}

// Retrieve Cloned Quote
Quote_CloneDAO.prototype.cloneQuoteAndProducts = function (id, editedQuote, callback) {
    this._connection.query(`
        CALL clone_quote_and_products(?, ?);
    `, [id, editedQuote], callback)
}

module.exports = () => {
    return Quote_CloneDAO;
}

Answer 1

The first amendment that will be required is to declare a parameter of the type OUT in his PROCEDURE:

-- ...
CREATE PROCEDURE clone_quote_and_products(IN quoteID INT,
                                          IN editedClone TINYINT(1),
                                          OUT id INT)
-- ...

Within the PROCEDURE assigns the value to the variable:

-- ...
SET id = LAST_INSERT_ID();
-- ...

When creating your connection, send the attribute multipleStatements with the value true, more or less like in the example below:

// ...
const connection = mysql.createConnection({multipleStatements: true});
// ...

And in his query send the variable and then perform a SELECT to get the return of the same, as in the example below:

// ...
this._connection.query('CALL clone_quote_and_products(?, ?, @id); SELECT @id', [id, editedQuote], callback);
// ...