DELIMITER //
CREATE PROCEDURE update_invoices_credit_total
(
invoice_id_param INT,
credit_total_param DECIMAL(9,2)
)
BEGIN
DECLARE sql_error TINYINT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET sql_error = TRUE;
START TRANSACTION;
UPDATE invoices
SET credit_total = credit_total_param
WHERE invoice_id = invoice_id_param;
IF sql_error = FALSE THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END//
CALL update_invoices_credit_total(56, 300);
The Procedure
The call to execute the Procedure
This procedure uses a SQLEXCEPTION to either commit a change or rollback in the event of an error