Error handling in procedures in MySQL - Step-by-Step Execution

Concept Flow - Error handling in procedures

Start Procedure

↓

Execute Statements

↓

Error Occurs?

No→Continue Execution

Yes↓

Handle Error with DECLARE HANDLER

↓

Perform Recovery or Logging

↓

End Procedure

The procedure starts and runs statements. If an error happens, the declared handler catches it and runs recovery code before ending.

Execution Sample

MySQL

CREATE PROCEDURE test_proc()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    SET @error_flag = 1;
  END;
  SET @error_flag = 0;
  INSERT INTO test_table VALUES (1);
END;

This procedure tries to insert a row and sets an error flag if an SQL exception occurs.

Execution Table

Step	Action	Evaluation	Result
1	Start procedure	N/A	Procedure begins execution
2	Declare CONTINUE HANDLER for SQLEXCEPTION	N/A	Handler ready to catch SQL errors
3	Set @error_flag = 0	N/A	@error_flag is 0 (no error yet)
4	Execute INSERT INTO test_table VALUES (1)	Success	Row inserted, no error
5	End procedure	N/A	Procedure ends normally

💡 Procedure ends after successful insert without errors

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
@error_flag	NULL	0	0	0

Key Moments - 2 Insights

Why do we declare a CONTINUE HANDLER before executing statements?

The CONTINUE HANDLER is declared first so it can catch any SQL errors during the statements execution, as shown in step 2 and step 4 of the execution table.

What happens if an error occurs during the INSERT statement?

If an error occurs, the handler runs and sets @error_flag to 1 instead of stopping the procedure, but in this example no error occurs (step 4).

Visual Quiz - 3 Questions

Look at the execution table, what is the value of @error_flag after step 3?

A 1

B 0

C NULL

D Undefined

💡 Hint

Check the variable_tracker table column 'After Step 3' for @error_flag

At which step does the procedure declare the error handler?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the execution_table 'Action' column for when the handler is declared

If the INSERT caused an error, what would happen to @error_flag?

A It would become 1

B It would become NULL

C It would stay 0

D Procedure would stop immediately

💡 Hint

Refer to the handler code in the execution_sample and key_moments about error handling

Concept Snapshot

Error handling in MySQL procedures:
- Use DECLARE HANDLER to catch errors
- CONTINUE HANDLER lets procedure continue after error
- Handler code runs when specified error occurs
- Use variables to track error state
- Place handler declaration before statements
- Helps avoid procedure stopping unexpectedly

Full Transcript

This visual trace shows how error handling works in MySQL procedures. The procedure starts and declares a CONTINUE HANDLER for SQL exceptions. It sets an error flag variable to zero before running an INSERT statement. If the INSERT succeeds, the flag stays zero and the procedure ends normally. If an error occurs during the INSERT, the handler runs and sets the flag to one, allowing the procedure to continue instead of stopping. This method helps manage errors gracefully inside stored procedures.