Examine this statement and output: Which will provide the same level of detail when the error is encountered within a stored routine? A. B. C. D.

Question

Exhibits

Options

• ADECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @num = NUMBER, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT; SET @full_error = CONCAT("Error ", @errno, " (", @num, "): ", @text); SELECT @full_error; END;
• BDECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @num = NUMBER; SELECT @sqlstate; END;
• CDECLARE 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;
• DDECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @num = NUMBER; SET @full_error = CONCAT("Error ", @errno, " (", @sqlstate, "): ", @num); SELECT @full_error; END;

• A
  3% (1)
• B
  8% (3)
• C
  84% (32)
• D
  5% (2)

Explanation

Option C is correct because it captures all three meaningful diagnostic items - RETURNED_SQLSTATE, MYSQL_ERRNO, and MESSAGE_TEXT - and formats them as Error [errno] ([sqlstate]): [message_text], which matches the same structure and level of detail as the reference output.

Option A is wrong because it puts @num (the internal condition area NUMBER) inside the parentheses instead of the SQLSTATE code - NUMBER is just an index into the diagnostics area, not a meaningful error identifier to display.

Option B is wrong because it retrieves only SQLSTATE and NUMBER, then outputs only @sqlstate - discarding errno and the message text entirely, giving far less detail.

Option D is wrong because it substitutes @num (NUMBER) for MESSAGE_TEXT at the end of the string, so the human-readable error description is completely missing from the output.

Memory tip: Think of a complete error report needing three things - what (MYSQL_ERRNO), where classified (RETURNED_SQLSTATE), and why (MESSAGE_TEXT). Option C is the only one that gathers all three and uses all three in the output; any option that swaps in NUMBER (a diagnostic area index, not a user-facing value) is a trap.

No community discussion yet for this question.