Retrieving diagnostic information by using GET DIAGNOSTICS in a handler

Handlers specify the action that an SQL procedure takes when a particular error or condition occurs. In some cases, you want to retrieve additional diagnostic information about the error or warning condition.

About this task

Procedure

You can include a GET DIAGNOSTICS statement in a handler to retrieve error or warning information.

If you include GET DIAGNOSTICS, it must be the first statement that is specified in the handler.

Example: Using GET DIAGNOSTICS to retrieve message text

Suppose that you create an SQL procedure, named divide1, that computes the result of the division of two integers. You include GET DIAGNOSTICS to return the text of the division error message as an output parameter:

CREATE PROCEDURE divide1 
  (IN numerator INTEGER, IN denominator INTEGER,
   OUT divide_result INTEGER, OUT divide_error VARCHAR(1000))
  LANGUAGE SQL
  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
      GET DIAGNOSTICS CONDITION 1 divide_error = MESSAGE_TEXT;
    SET divide_result = numerator / denominator;
  END