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

Code Example 1

 .
 .
 .
 DECLARE sqlState, sqlStateLocal CHAR(5) DEFAULT '00000';
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
    SET sqlStateLocal = sqlState;
 .
 .
 .
 INSERT INTO orderTable VALUES (customerId, orderId, orderStatus, orderComments);
 IF sqlStateLocal <-> '00000' THEN
    LEAVE thisCompoundStatement;
 END IF;
 .
 .
 .

In the example just shown, you could have substituted for every occurrence of sqlCode and sqlCodeLocal the variables sqlState and sqlStateLocal. We do not recommend using SQLSTATE and SQLCODE at the same time, because each statement, including assignment statements, reset both the SQLSTATE and SQLCODE, meaning that the following situation could result. Consider the following two statements:

 SET sqlCodeLocal = sqlCode;
 SET sqlStateLocal = sqlState;

If the two statements just shown should be the first and second statements in a handler, SQL variable sqlStateLocal would receive '00000' as the (reset) value. Because the condition information in the Diagnostics Area is preserved until the handler succeeds, the following would work as a coding alternative:

 SET sqlCodeLocal = sqlCode;
 GET STACKED DIAGNOSTICS sqlStateLocal = RETURNED_SQLSTATE;

Following is code example 2, which is equivalent from a functional point of view, to the previously shown code example 1. In the example that follows, logic that is not shown (for space considerations) is indicated by three vertically arranged periods.