---

Return to the SQL Tips

---

Choosing SQLSTATE values for Procedures, Functions, and Triggers

When you write a stored procedure, user-defined function (UDF), or SQL trigger, you may need to set the five-character SqlState for the operation. Here are guidelines to help you pick the right value.

SqlState can be any five digits (0-9) or letters (A-Z).

The SqlState value has two parts:
  The first two characters are the class.
  The last three characters are the subclass.

The meaning of the class values are as follows:
  00            -- Success
  01            -- Warning
  02            -- No data
  03 through ZZ -- Error

Within classes that begin with 3-6 or A-H, subclasses that begin with 0-H are generally reserved for use by the database manager. There's one exception to this rule: SqlState values 01Hxx can be used in external stored procedures and user-defined functions (UDFs), as explained below. So 01001, 40105, and BZHZZ are examples of SqlState values reserved for the database manager, while 01H01, 40Z11, and J0000 are available for application use.

There are specific rules for external stored procedures and UDFs. Before you return from the HLL program or procedure that implements an external stored procedure or UDF, you should set the SqlState to one of the following:

00000 -- Success
01Hxx -- Warning, xx is any two digits or letters
38yxx -- Error, y is I-Z and xx is any two digits or letters

For SQL stored procedures, UDFs, and triggers, I recommend you start application-defined SqlState values for errors with a consistent letter, for example U to indicate "user-defined." You can establish a standard for the second letter to identify application-defined classes. For example, you might use UT to begin all SqlState error values set by triggers. Application-defined SqlState values for warnings should begin with 01 and a consistent third letter. For example, you might use 01Uxx as the form for warnings.

[report a broken link by clicking here]

---