Getting value from MYSQL, adding 1 and inserting into new entry

I am trying to make a stored procedure in MySQL that will take the highest number from a column, add one and use it to make the next entry.

DROP PROCEDURE IF EXISTS ops_software.create_invoice;

DELIMITER //

CREATE PROCEDURE ops_software.create_invoice(IN company VARCHAR(50))
BEGIN
    
    SELECT @old_invoice_number := MAX(invoice_number) 
    FROM invoices
    WHERE invoices.company = company;
    
    SET @new_invoice_number := @old_invoice_number + 1
    
    INSERT INTO invoices (company, invoice_number)
    VALUES (company, @new_invoice_number)
    

END//

DELIMITER ;

CALL ops_software.create_invoice('Super Company')

I don't want to use the auto-increment feature because there are several different company names and each has their own invoice numbers

Getting the value works, but I can't add one to it or insert it to make a new entry

Thanks

Solution 1:

CREATE PROCEDURE ops_software.create_invoice(IN in_company VARCHAR(50))
INSERT INTO invoices (company, invoice_number)
SELECT in_company, MAX(invoices.invoice_number) + 1 
FROM invoices
WHERE invoices.company = in_company;

DELIMITER and BEGIN-END not needed.

PS. May produce duplicates in concurrent environment.