Error handling in procedures in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a procedure with DECLARE CONTINUE HANDLER

Consider the following MySQL procedure. What will be the output when calling CALL test_error_handling();?

MySQL

DELIMITER $$
CREATE PROCEDURE test_error_handling()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT 'Error caught';
  END;
  -- This will cause a division by zero error
  SELECT 10 / 0;
  SELECT 'No error';
END$$
DELIMITER ;

A Error caught

B Procedure completes without output

C No error

D Syntax error in procedure

💡 Hint

Think about what DECLARE CONTINUE HANDLER does when an error occurs inside the procedure.

The DECLARE CONTINUE HANDLER catches the division by zero error and runs the handler block, which outputs 'Error caught'.

🧠 Conceptual

intermediate

Purpose of DECLARE EXIT HANDLER in MySQL procedures

What is the main difference between a DECLARE CONTINUE HANDLER and a DECLARE EXIT HANDLER in MySQL stored procedures?

A Both handlers behave the same and continue execution after handling the error.

B CONTINUE HANDLER stops the procedure after handling the error; EXIT HANDLER allows it to continue.

C EXIT HANDLER is used only for warnings, CONTINUE HANDLER only for errors.

D CONTINUE HANDLER allows the procedure to continue after handling the error; EXIT HANDLER stops the procedure after handling the error.

💡 Hint

Think about what happens to the procedure flow after the handler runs.

A CONTINUE HANDLER lets the procedure continue after the handler code runs, while an EXIT HANDLER causes the procedure to exit immediately after the handler code executes.

📝 Syntax

advanced

Identify the syntax error in this error handler declaration

Which option correctly fixes the syntax error in this MySQL procedure snippet?

MySQL

CREATE PROCEDURE sample_proc()
BEGIN
  DECLARE HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT 'Error';
  END;
END;

A Remove BEGIN and END inside handler block

B Add EXIT keyword: DECLARE EXIT HANDLER FOR SQLEXCEPTION

C Change to DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

D No change needed, syntax is correct

💡 Hint

MySQL requires a handler type keyword before HANDLER.

MySQL requires the handler type (CONTINUE or EXIT) before HANDLER. Omitting it causes a syntax error.

🔧 Debug

advanced

Why does this procedure not catch the error as expected?

Given this procedure, why does the error not get caught by the handler?

MySQL

DELIMITER $$
CREATE PROCEDURE test_proc()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT 'Error caught';
  END;
  SET @x = 1/0;
  SELECT 'No error';
END$$
DELIMITER ;

A The handler is EXIT type, so it exits before printing 'Error caught'

B Division by zero in SET statement does not raise SQLEXCEPTION

C SET @x = 1/0 is evaluated outside the procedure context, so handler does not catch it

D The error occurs before the handler is declared

💡 Hint

Consider where the error happens and how MySQL evaluates expressions.

Division by zero in a SET statement assigning to a user variable raises only a warning (SQLWARNING), not SQLEXCEPTION, so the handler does not trigger.

❓ optimization

expert

Optimizing error handling for multiple error codes

You want to catch multiple specific SQL error codes in a MySQL procedure and handle them differently. Which approach is the most efficient and correct?

A Declare multiple handlers, one for each error code, each with its own handler block

B Use a single EXIT handler for all errors and re-raise errors for unhandled codes

C Declare one handler for SQLWARNING and ignore error codes

D Declare one handler for SQLEXCEPTION and use IF statements inside to check error codes

💡 Hint

MySQL allows multiple handlers for different conditions.

Declaring multiple handlers for specific error codes lets you handle each error precisely and efficiently without extra checks inside the handler.