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.