SQL Statements › CREATE PROCEDURE › CREATE PROCEDURE Syntax and Description › SQLSTATE and SQLCODE Special Variables

SQLSTATE and SQLCODE Special Variables

As an alternative to using the GET DIAGNOSTICS statement in a handler to retrieve the SQLSTATE into an SQL variable, and for compatibility with other implementations, supply an SQLSTATE or SQLCODE local variable and code the following as the first statement in your handler:

 SET SQL-variable-name = sqlstate-or-sqlcode;

SQL then assigns a value to your SQLSTATE or SQLCODE variable after every statement execution. Instructions for using this feature follows:

1. Declare SQLSTATE (or SQLCODE) as an SQL variable in your condition handler or a compound statement that contains the handler.
2. As the first (or only) statement in a handler, assign SQLSTATE (or SQLCODE) to your SQL variable. Again, note that if you supply an SQLSTATE (or SQLCODE) variable, Datacom will reset it's value each time a statement is executed, hence the need for a separate SQL variable copy of any value you need to save.

For example, from a functional point of view code example 1 (following on this page) and code example 2 (see Code Example 2) are equivalent. In the example that follows, logic that is not shown (for space considerations) is indicated by three vertically arranged periods.