Error handling in Transact-SQL procedures

Default procedure error handling is different in the Watcom-SQL and Transact-SQL dialects. By default, Watcom-SQL dialect procedures exit when they encounter an error, returning SQLSTATE and SQLCODE values to the calling environment.

Explicit error handling can be built into Watcom-SQL stored procedures using the EXCEPTION statement, or you can instruct the procedure to continue execution at the next statement when it encounters an error, using the ON EXCEPTION RESUME statement.

When a Transact-SQL dialect procedure encounters an error, execution continues at the following statement. The global variable @@error holds the error status of the most recently executed statement. You can check this variable following a statement to force return from a procedure. For example, the following statement causes an exit if an error occurs.

IF @@error != 0 RETURN

When the procedure completes execution, a return value indicates the success or failure of the procedure. This return status is an integer, and can be accessed as follows:

DECLARE @Status INT
EXECUTE @Status = proc_sample
IF @Status = 0
   PRINT 'procedure succeeded'
ELSE
   PRINT 'procedure failed'

The following table describes the built-in procedure return values and their meanings:

Value Definition SQL Anywhere SQLSTATE
0 Procedure executed without error
-1 Missing object 42W33, 52W02, 52003, 52W07, 42W05
-2 Data type error 53018
-3 Process was chosen as deadlock victim 40001, 40W06
-4 Permission error 42501
-5 Syntax error 42W04
-6 Miscellaneous user error
-7 Resource error, such as out of space 08W26
-10 Fatal internal inconsistency 40W01
-11 Fatal internal inconsistency 40000
-13 Database is corrupt WI004
-14 Hardware error 08W17, 40W03, 40W04

When a SQL Anywhere SQLSTATE is not applicable, the default value -6 is returned.

The RETURN statement can be used to return other integers, with their own user-defined meanings.


Using the RAISERROR statement in procedures
Transact-SQL-like error handling in the Watcom-SQL dialect