Microsoft KB Archive/44519

= INF: Error Handling in Transact-SQL Blocks and Stored Procedures =

Article ID: 44519

Article Last Modified on 10/30/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q44519



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 &quot;More Information&quot; section of this article.



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.

Additional query words: Windows NT

Keywords: kbinfo kbsqlprog kbtsql kbprogramming KB44519

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.