SUMMARY
This article describes how Microsoft SQL Server handles
errors in Transact-SQL blocks and stored procedures. The article also explains
error handling in nested Transact-SQL and nested stored
procedures.
Here is the list of errors that occur in Transact-SQL
blocks and stored procedures:
- Syntax Errors
- Execution-Time Errors
- Application-Level Errors
For more details about each type of error, see the "More
Information" section of this article.
MORE INFORMATION
Syntax Errors
Syntax errors occur when Microsoft SQL Server cannot parse the
SQL blocks. These errors occur before the beginning of the execution process of
the Transact-SQL block or the stored procedure. Because of syntax errors, the
whole Transact-SQL block or the stored procedure stops executing. Here, you do
not have to explicitly handle the error to maintain the atomicity of the
database transaction. You do not have to explicitly handle the error because
the execution of the Transact-SQL block or the stored procedure has not yet
started. The applications can handle these errors by using user-defined message
handlers or the exception handling mechanism.
Execution-Time Errors
There are two types of execution-time errors:
- Fatal errors
- Nonfatal errors
More information about each follows.
Fatal Errors
Fatal errors are system problems. This means the execution process
of the Transact-SQL block or the stored procedure that performs the task
specified in the statement is no longer running. The process stops immediately.
For example, trying to use a table or a column that does not exist
causes a fatal error to occur. When a fatal error occurs, uncommitted
Transact-SQL statements in the BEGIN...COMMIT or BEGIN...ROLLBACK statements of
the Transact-SQL block will roll back. The statements that are out of the
BEGIN...COMMIT or BEGIN...ROLLBACK statements are not rolled back because each
Transact-SQL statement is committed individually if it runs successfully.
Nonfatal Errors
Nonfatal errors are internal software problems. Even if the errors
occur, the execution process of the Transact-SQL block or the stored procedure
that performs the task specified in the statement completes
successfully.
For example, both trying to insert a duplicate value in
a unique index or permission violations cause a nonfatal error. When a nonfatal
error occurs, uncommitted data does not roll back unless the ROLLBACK statement
is called explicitly in the Transact-SQL Block or the stored procedure. The
execution process continues unless the RETURN function with the BEGIN...COMMIT
or BEGIN...ROLLBACK statements interrupt the Transact-SQL block.
Application-Level Errors
Application-level errors are errors or messages that are specific
to business rule implementations. SQL Server considers the application-level
errors as messages, not as errors. An application must handle these messages
explicitly. A RETURN function with the BEGIN...COMMIT or BEGIN...ROLLBACK
statements is used to stop the Transact-SQL block or the stored procedure.
Otherwise, the execution process continues with the remaining statements in the
Transact-SQL block or the stored procedure.
Error Handling Behavior in Nested Transact-SQL Blocks
Microsoft SQL Server stops the execution of the process and rolls
back the transaction if a fatal error occurs in any of the outer or inner
Transact-SQL blocks.
For example, one Transact-SQL block, named T2, is
nested inside another Transact-SQL block, named T1. The process ends if a fatal
error occurs in the inner block of T2 or the outer block of T1, and all data
update statements are rolled back. If a nonfatal error occurs and the process
ends, the rest of the process continues successfully.
Error Handling Behavior in Nested Stored Procedures
The error handling behavior of Microsoft SQL Server in nested
procedures is different from that of the nested Transact-SQL
blocks.
For example, a stored procedure, named PROC1, is called inside
another stored procedure, named PROC2. The execution process of stored
procedure PROC2 does not end if a fatal error occurs in PROC1. However, the
execution of a stored procedure in PROC2 will end.
If any fatal error occurs in PROC1, PROC2 continues the
execution.