Example: Procedure providing data validation

DELIMITER //

CREATE PROCEDURE update_invoices_credit_total

( invoice_id_param INT,

credit_total_param DECIMAL(9,2))

BEGIN

-- Validate paramater values

IF credit_total_param < 0 THEN

SIGNAL SQLSTATE '22003'

SET MESSAGE_TEXT =

'The credit_total column must be greater than or equal to 0',

MYSQL_ERRNO = 1264;

ELSEIF credit_total_param >= 1000 THEN

SIGNAL SQLSTATE '22003'

SET MESSAGE_TEXT =

'The credit_total column must be less than 1000.',

MYSQL_ERRNO = 1264;

END IF;

-- Set default values for parameters

IF credit_total_param IS NULL THEN

SET credit_total_param = 100;

END IF;

UPDATE invoices

SET credit_total = credit_total_param

WHERE invoice_id = invoice_id_param;

END//

CALL update_invoices_credit_total(56, -100);

Error Code: 1264.

The credit_total column must be greater than or equal to 0

This procedure is testing for 3 states

Input Less than 0

Input equal to or greater than 1000

NULL Input

Example call and result