Microsoft SQL Server 2005 introduced new error handling capabilities for scripts and stored procedures. This article describes the use of the try / catch block in Transact-SQL that permits errors to be captured and allows for graceful recovery. Error Handling
Prior to the introduction of SQL Server 2005, error handling in Transact-SQL (T-SQL) scripts was very restrictive. If error handling was included in a batch or stored procedure, it would usually be limited to reading the value of @@ERROR immediately after attempting to execute a statement. If @@ERROR was zero, all was probably well. However, this approach was far from ideal, particularly as the value would be cleared after every executed statement. Newer versions of SQL Server have improved error handling with the addition of the try / catch block, which is similar in operation to the try / catch block provided by C# and other languages. This code structure can be used in batches, stored procedures, triggers and other areas. It allows one or more statements to be executed within a try block. If an error occurs during processing, the block is exited immediately and control is passed to the catch block, where you can include statements that allow graceful recovery or report the error and leave the system in a stable state. Try / Catch Blocks
The try / catch structure contains two blocks of statements. These are the statements that are to be attempted and those that will execute if an error is raised. The syntax for these blocks is as follows: BEGIN TRY
[statements to try]
END TRY
BEGIN CATCH
[statements to run on error]
END CATCHCapturing Errors
The use of the try / catch block can be made more obvious with an example. In the script below the statement within the try block is attempting to divide one by zero before outputting a success message. This will always cause an error because the result of such a division cannot be represented in T-SQL. The catch block outputs a message if an error occurs. Read more: Black Wasp
Prior to the introduction of SQL Server 2005, error handling in Transact-SQL (T-SQL) scripts was very restrictive. If error handling was included in a batch or stored procedure, it would usually be limited to reading the value of @@ERROR immediately after attempting to execute a statement. If @@ERROR was zero, all was probably well. However, this approach was far from ideal, particularly as the value would be cleared after every executed statement. Newer versions of SQL Server have improved error handling with the addition of the try / catch block, which is similar in operation to the try / catch block provided by C# and other languages. This code structure can be used in batches, stored procedures, triggers and other areas. It allows one or more statements to be executed within a try block. If an error occurs during processing, the block is exited immediately and control is passed to the catch block, where you can include statements that allow graceful recovery or report the error and leave the system in a stable state. Try / Catch Blocks
The try / catch structure contains two blocks of statements. These are the statements that are to be attempted and those that will execute if an error is raised. The syntax for these blocks is as follows: BEGIN TRY
[statements to try]
END TRY
BEGIN CATCH
[statements to run on error]
END CATCHCapturing Errors
The use of the try / catch block can be made more obvious with an example. In the script below the statement within the try block is attempting to divide one by zero before outputting a success message. This will always cause an error because the result of such a division cannot be represented in T-SQL. The catch block outputs a message if an error occurs. Read more: Black Wasp
Posts
0 comments:
Post a Comment