How SQL UDFs Handle SQL Result Codes - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

The ANSI SQL:2011 standard defines a return code variable named SQLSTATE to accept status code messages. All condition messages are returned to this variable in a standard ASCII character string format.

All SQLSTATE messages are 5 characters in length. The first 2 characters define the message class and the last 3 characters define the message subclass.

For example, consider the SQLSTATE return code ‘22021’. The class of this message, 22, indicates a data exception condition. Its subclass, 021, indicates that a character not in the defined repertoire was encountered.

Be aware that SQL warnings do not abort a request, while SQL exceptions do abort a request.

You should ensure that your UDFs always return valid SQLSTATE codes. Vantage does not map SQLSTATE values returned by SQL UDFs to their equivalent SQLCODE values. All SQLSTATE codes generated by SQL UDFs are explicitly mapped to system messages as indicated by the following table:

IF the UDF returns this type of SQL return code … THEN the system maps it to this error message … AND displays it in this format …
Warning 7505 *** Warning 7505 in dbname.udfname: SQLSTATE 01Hxx:
Exception 7504 *** Error 7504 in dbname.udfname: SQLSTATE U0xxx:

See Teradata Vantage™ - SQL Fundamentals, B035-1141 for more information about SQL exception and warning codes.

See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148 for more information about SQLSTATE and SQLCODE result codes.