Example of Exception handling in Procedure MariaDB or MySQL

Example: of Exception Handling in Procedure MariaDB or MySQL

1. Create a table name TEST

Create table test (name varchar(10));

2. Create another table to capture the error occurred in Procedure:

Create table errorhandling ( errordetail varchar(5000),createdate date);

3. Add the following line for handle the exception in procedure or function:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
END;

4. Example of using Procedure with exception handling , insert exception in table that occurred in procedure during run time:

-- create procedure for inserting data into test table:
  
create or replace PROCEDURE tesingexception(IN p_text varchar(255))
begin

-- Exception handling
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
insert into errorhandling  values (@full_error, current_timestamp);
END;

--Procedure statement
insert into test SELECT p_text;
END;

5. On call the procedure it is working fine for value of name is 10 character. If I enter more then 10 character then it will generate an exception and make an entry in errorhandling table used in exception handling of procedure:

--Check with correct value:

call tesingexception ('Atelier');
select * from test;
name   |
-------+
Atelier|

--Check with the more character value which cause exception:

call tesingexception ('Atelier graphique')
select * from errorhandling;
errordetail                                                 |createdate|
------------------------------------------------------------+----------+
ERROR 1406 (22001): Data too long for column 'name' at row 1|2022-04-20|