WHEN ... DO

The WHEN ... DO statement is the optional exception handler for each BEGIN ... END block.

Syntax

<block> ::=

  BEGIN

    [<compound_statement> ...]

    [<when_do> ...]

  END

<compound_statement> ::= {<block> | <statement>}

<when_do> ::= 
  WHEN {<error> [, <error> ...] | ANY}
  DO <compound_statement>
 
<error> ::= 

  { EXCEPTION exception_name

    | SQLCODE number 

    | GDSCODE errorcode 

    | SQLSTATE sqlstatecode}

Whenever an exception occurs, the statements in the block are undone and an exception handler is executed. If there's no exception handler available, the exception is escalated to the parent block.

Then WHEN clause tells you what exceptions are caught, you can use ANY to catch all exceptions. Multiple WHEN clauses can be used for the same block.

Mind you, while SQLCODE and GDSCODE are numbers, SQLSTATE is a string literal. Using GDSCODE is Firebird and InterBase specific.

See Also

Example

In this example, there's a nested BEGIN ... END block. The most inner block has no exception handler, so the exception raised is escalated to the parent block. This block does have an exception handler, but doesn't catch the right exception so it's escalated once more. The outer block has an exception handler that catches all exceptions and simply exits the routine.

execute udsblock()

returns()

as

begin

  delete from TEST_EX_HANDLING;

  insert into TEST_EX_HANDLING (pk)

  values (1);

/*  insert into TEST_EX_HANDLING (pk)

  values (1); */

  begin 

    begin  

      insert into TEST_EX_HANDLING (pk)

      values (2);

      insert into TEST_EX_HANDLING (pk)

      values (3);

      execute procedure TEST_EX_HANDLING_PROC; /* raises exception */

    end

    /* exception handling block OUTSIDE BEGIN..END */

    when sqlcode -803 /* catch primary key violation */

    do Exit;

  end       

  when any /* catch all exceptions */

    do Exit;

end