INF: Error Handling in Transact-SQL

ID: Q44519

The information in this article applies to:

SUMMARY

This article discusses the different kinds of error handling in Transact-SQL batches and stored procedures. Three classes of errors are described, as well as additional factors concerning error handling in nested transactions and procedures.


MORE INFORMATION

Syntax Errors

Syntax errors in SQL batches or procedures are detected before execution begins; therefore, a single syntax error will cause the entire batch or procedure to be rejected. What constitutes a transaction and what is backed out are not issues in this case, because execution never begins. A batch with syntax errors will produce one or more error messages that will cause the user-defined MESSAGE handler to be called (once for each message). The dbsqlexec that sent the bad batch will return a status of FAIL, and a dbresults issued to receive the results of the batch will return a status of NO_MORE_RESULTS. The user-defined ERROR handler is used only to report internal DB-Library (DB-Lib) errors.

Execution-Time Errors

Execution-Time errors can be either fatal or nonfatal. Attempting to use a table or column that does not exist is fatal. Permission violations and attempting to insert a duplicate value in a unique index are nonfatal. (Attempting to use a nonexisting table won't get past the syntax check, unless the table is dropped after the procedure referencing it is created)

In either case, the user-defined MESSAGE handler will be called (once for each message). Messages with severity 16 and higher are fatal.

If the error is fatal, uncommitted updates are backed out. Statements not bracketed by BEGIN/COMMIT are not backed out because each one is committed individually if it is successful. Execution is not terminated unless the current procedure is bracketed by BEGIN/COMMIT.

If the error is nonfatal, uncommitted updates are not backed out unless an explicit ROLLBACK is performed. Execution is not terminated unless the batch or procedure is bracketed by BEGIN/COMMIT or a RETURN is executed.

The dbsqlexec that sent the batch will return FAIL if the error is fatal, and SUCCEED if the error is nonfatal. The next dbresults will return NO_MORE_RESULTS if the error is fatal, and will return FAIL if the error is nonfatal.


Application-Level Errors

Exceptions such as "0 rows affected" are not considered to be errors because the SQL Server has no way of knowing whether or not such an error is serious. Each of these kinds of conditions must be explicitly tested by the application. A RETURN statement must be used if the procedure is to be terminated. Otherwise, the remainder of the statements in the procedure or batch will be executed (even if a ROLLBACK is executed).

Exceptions produce no messages for the user-defined message handler unless RAISERROR was executed. dbsqlexec will return SUCCEED. dbresults will return SUCCEED unless RAISERROR was executed; if RAISERROR was executed, dbresults will return FAIL. dbresults will return SUCCEED even if a "not found" exception occurs.

ROLLBACK

The ROLLBACK statement does not terminate a batch or procedure; it backs out uncommitted updates. Subsequent statements in the batch or procedure are executed normally. This allows the application to perform complex contingency operations if an error occurs.

Nested Logical Units of Work

Statements bracketed by BEGIN/COMMIT may be nested inside another group of statements also bracketed by BEGIN/COMMIT. If a fatal error occurs within the inner or outer LUW, execution is terminated and all updates are backed out. If a nonfatal error occurs in the inner or outer LUW, execution continues and nothing is backed out.

Nested logical units of work are meaningful only in the context of nested stored procedures. The ability to nest logical units of work allows stored procedures that use COMMIT/ROLLBACK to be written without regard for whether or not they will be called by other stored procedures that use COMMIT/ROLLBACK.

Each BEGIN TRANSACTION statement causes a counter to be incremented, and each COMMIT statement causes the counter to be decremented. When the counter reaches 0, a true COMMIT is performed. The current value of the counter is in the system variable "@@TRANCOUNT".


Nested Transact-SQL Procedures

Fatal errors in nested Transact-SQL procedures are not fatal to the parent. This allows a parent procedure to execute a contingency plan to handle a fatal error in a subordinate procedure. It also requires that the parent procedure check the status of @@ERROR after returning from a subordinate. @@ERROR is set by most Transact-SQL statements; therefore, you must be careful to RETURN before @@ERROR is changed. The nesting of transactions resulting from nesting procedures can cause a fatal error or ROLLBACK in the inner procedure to back out updates performed by the parent procedure (in addition to backing out the inner procedure's updates).



To signal to a parent procedure that a nonfatal error has occurred, set @@ERROR in the subordinate with RAISERROR. Follow it closely with RETURN to preserve the value of @@ERROR. In the case of fatal errors, no RAISERROR is necessary and the RETURN is automatic.

In future releases, it will be possible to return status information from subordinate procedures via an extension to the RETURN statement.

Additional query words: Windows NT 


Keywords          : kbprg SSrvTran_SQL SSrvServer SSrvWinNT 
Version           : 4.2 | 4.2
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 6, 1999