Error handling in procedures in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to declare a handler for SQL exceptions in a MySQL procedure.

MySQL

DECLARE CONTINUE HANDLER FOR [1] BEGIN SET @error_message = 'Error occurred'; END;

Drag options to blanks, or click blank then click option'

ANOT FOUND

BSQLEXCEPTION

CSQLWARNING

DSQLSTATE '02000'

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOT FOUND instead of SQLEXCEPTION for general error handling.

Using SQLSTATE '02000' which is specific to 'no data' condition.

The SQLEXCEPTION condition is used to catch all SQL errors in MySQL procedures.

2fill in blank

medium

Complete the code to declare a handler that sets a flag when no data is found.

MySQL

DECLARE CONTINUE HANDLER FOR [1] SET no_data_flag = 1;

Drag options to blanks, or click blank then click option'

ASQLEXCEPTION

BSQLSTATE '45000'

CSQLWARNING

DNOT FOUND

Attempts:

3 left

💡 Hint

Common Mistakes

Using SQLEXCEPTION which catches all errors, not just 'no data'.

Using SQLSTATE '45000' which is a user-defined error.

The NOT FOUND condition is used to handle cases when a SELECT or FETCH returns no rows.

3fill in blank

hard

Fix the error in the handler declaration to correctly catch warnings.

MySQL

DECLARE CONTINUE HANDLER FOR [1] SET warning_flag = TRUE;

Drag options to blanks, or click blank then click option'

ASQLSTATE '01000'

BWARNING

CSQLWARNING

DSQLSTATE 'HY000'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'WARNING' alone which is not a valid condition.

Using incorrect SQLSTATE codes for warnings.

The correct condition to catch warnings in MySQL procedures is SQLWARNING.

4fill in blank

hard

Fill both blanks to declare a handler that sets an error message and exits the procedure on a custom error.

MySQL

DECLARE EXIT HANDLER FOR [1] BEGIN SET @error_msg = 'Custom error'; [2]; END;

Drag options to blanks, or click blank then click option'

ASQLSTATE '45000'

BLEAVE

CSQLEXCEPTION

DRETURN

Attempts:

3 left

💡 Hint

Common Mistakes

Using CONTINUE handler instead of EXIT for stopping execution.

Using LEAVE which is for loops, not procedures.

The handler catches the user-defined error with SQLSTATE '45000' and exits the procedure using RETURN.

5fill in blank

hard

Fill all three blanks to declare a handler that logs an error code, sets a flag, and exits on any SQL exception.

MySQL

DECLARE EXIT HANDLER FOR [1] BEGIN SET @err_code = [2]; [3] = TRUE; END;

Drag options to blanks, or click blank then click option'

ASQLEXCEPTION

BSQLSTATE

Cerror_flag

DSQLWARNING

Attempts:

3 left

💡 Hint

Common Mistakes

Using SQLWARNING instead of SQLEXCEPTION for all errors.

Trying to assign error_flag without declaring it.

The handler catches all SQL exceptions with SQLEXCEPTION, stores the error code from SQLSTATE in @err_code, and sets error_flag to TRUE.

Practice

1. What is the main purpose of using DECLARE HANDLER in a MySQL stored procedure?

easy

A. To declare variables inside the procedure

B. To catch and handle errors during procedure execution

C. To create temporary tables for data processing

D. To optimize query performance automatically

Solution

1. Step 1: Understand the role of DECLARE HANDLER

   DECLARE HANDLER is used to define actions when certain conditions or errors occur during procedure execution.

2. Step 2: Identify the purpose in error handling

   It helps catch errors and decide whether to continue or exit, preventing crashes and allowing custom responses.

3. Final Answer:

   To catch and handle errors during procedure execution -> Option B

4. Quick Check:

   Error handling = DECLARE HANDLER usage [OK]

Hint: DECLARE HANDLER is for managing errors inside procedures [OK]

Common Mistakes:

• Confusing DECLARE HANDLER with variable declaration
• Thinking it creates tables or indexes
• Assuming it optimizes queries automatically

2. Which of the following is the correct syntax to declare a handler for SQL exceptions that continues execution in MySQL?

easy

A. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END;

B. DECLARE HANDLER CONTINUE FOR SQLEXCEPTION;

C. DECLARE HANDLER FOR CONTINUE SQLEXCEPTION;

D. DECLARE HANDLER FOR SQLEXCEPTION CONTINUE;

Solution

1. Step 1: Recall correct DECLARE HANDLER syntax

   The syntax is: DECLARE [CONTINUE|EXIT] HANDLER FOR condition statement;

2. Step 2: Match syntax with options

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END; correctly uses DECLARE CONTINUE HANDLER FOR SQLEXCEPTION followed by a statement block.

3. Final Answer:

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END; -> Option A

4. Quick Check:

   Correct syntax = DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END; [OK]

Hint: Syntax: DECLARE CONTINUE HANDLER FOR error BEGIN...END [OK]

Common Mistakes:

• Placing CONTINUE after HANDLER incorrectly
• Omitting BEGIN...END block for multiple statements
• Wrong order of keywords

3. Consider this procedure snippet:

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @error_flag = 1;
SET @error_flag = 0;
INSERT INTO users(id, name) VALUES(1, 'Alice');
INSERT INTO users(id, name) VALUES(1, 'Bob');

Assuming id is a primary key, what will be the value of @error_flag after execution?

medium

A. 1

B. 0

C. NULL

D. Procedure will crash without setting @error_flag

Solution

1. Step 1: Analyze the inserts and handler

   The first insert succeeds. The second insert violates primary key (duplicate id), causing an SQLEXCEPTION.

2. Step 2: Understand EXIT HANDLER effect

   The EXIT HANDLER sets @error_flag = 1 and stops further execution.

3. Final Answer:

   1 -> Option A

4. Quick Check:

   Duplicate key error triggers EXIT HANDLER = 1 [OK]

Hint: EXIT HANDLER sets flag on error and stops execution [OK]

Common Mistakes:

• Assuming @error_flag stays 0 despite error
• Thinking procedure continues after EXIT HANDLER
• Confusing CONTINUE and EXIT handlers

4. Identify the error in this MySQL procedure snippet:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE done BOOLEAN DEFAULT FALSE;
WHILE done = FALSE DO
  -- some code
END WHILE;

medium

A. NOT FOUND is not a valid condition for handler

B. Missing semicolon after DECLARE statements

C. WHILE loop condition syntax is incorrect

D. Handler declared before variable 'done' is declared

Solution

1. Step 1: Check declaration order rules

   In MySQL procedures, variables must be declared before handlers.

2. Step 2: Identify declaration order in snippet

   The handler is declared before the variable 'done', which is invalid.

3. Final Answer:

   Handler declared before variable 'done' is declared -> Option D

4. Quick Check:

   Variables before handlers = correct order [OK]

Hint: Declare variables before handlers in procedures [OK]

Common Mistakes:

• Declaring handlers before variables
• Confusing NOT FOUND condition usage
• Ignoring declaration order rules

5. You want to write a procedure that tries to insert a new user but if a duplicate key error occurs, it should update the user's name instead. Which error handling approach is best?

hard

A. Use DECLARE CONTINUE HANDLER for NOT FOUND to insert new user

B. Use DECLARE EXIT HANDLER for any error to rollback transaction

C. Use DECLARE CONTINUE HANDLER for duplicate key error to run UPDATE statement

D. Use no handler and rely on client application to handle errors

Solution

1. Step 1: Understand the goal

   Insert user; if duplicate key error occurs, update existing user's name instead.

2. Step 2: Choose handler type

   CONTINUE HANDLER allows procedure to continue after catching duplicate key error and run UPDATE.

3. Step 3: Evaluate other options

   EXIT HANDLER stops execution, NOT FOUND is unrelated, and no handler defers error handling outside procedure.

4. Final Answer:

   Use DECLARE CONTINUE HANDLER for duplicate key error to run UPDATE statement -> Option C

5. Quick Check:

   CONTINUE HANDLER for duplicate key = update fallback [OK]

Hint: Use CONTINUE HANDLER to catch duplicate key and update [OK]

Common Mistakes:

• Using EXIT HANDLER which stops procedure
• Using NOT FOUND for duplicate key errors
• Ignoring error handling inside procedure