USE `project_six`;

DROP procedure IF EXISTS `balance_due`;

DELIMITER $$

USE `project_six`$$

CREATE PROCEDURE `balance_due` ()

BEGIN

DECLARE invoice_count_row int;

SELECT count(invoice_id)

INTO invoice_count_row

FROM apdb_invoices

WHERE payment_total >= 5000;

IF invoice_count_row > 0 THEN

SELECT concat(invoice_count_row,' invoice(s) exceed $5000') AS message;

else

SELECT 'No invoices exceed $5000' AS message;

end if;

END$$

DELIMITER ;

The question is Modify the script written above and name the procedure balance_due. Include a line that drops the procedure if it already exists, prior to the creation of the procedure. This revised stored procedure shall have an input variable of decimal type that defines the balance due:

bal_due DECIMAL(9,2)

It shall also define an output variable varchar for the resulting message, which will be assigned the count of the number of rows that have a balance due of bal_due or more. The output string will display a line that displays the result in a message like:

3 invoices exceed $.