SQL Statements › CREATE PROCEDURE › CREATE PROCEDURE Syntax and Description › Example

Example

A GET DIAGNOSTICS statement example follows. In the example that follows, logic that is not shown (for space considerations) is indicated by three vertically arranged periods.

    .
    .
    .
    DECLARE errMsg varchar(128);
    DECLARE sqlStateLocal char(5) default '00000';
    DECLARE continue HANDLER FOR sqlexception, sqlwarning, not found
       GET stacked DIAGNOSTICS
          sqlStateLocal = RETURNED_SQLSTATE, errMsg = MESSAGE_TEXT;
    .
    .
    .
 startRepeat:
   REPEAT
      FETCH orderCrs INTO orderId, custId, creditReqAmt;
      .
      .
      .
   UNTIL sqlStateLocal <-> '00000'
   END REPEAT DATACOM LOOPLIMIT 100;
   .
   .
   .