Description
This article provides information about the TRY...CATCH keyword that is added to T-SQL in the latest release of SQL Server called Yukon beta 1.
Introduction
Yukon has added many new keywords to the T-SQL vocabulary. These additions to the T-SQL increase the expressive power of your queries. This article would be primarily discussing this new construct that has been added to T-SQL, which is TRY匔ATCH.
In the earlier version of Yukon, which is SQL Server 2000, error handling was primarily done by check the @@ERROR value in the SQL procedures. Also, there was no automated transaction abort handling functionality.
Yukon provides a new construct that enables the handling of transaction abort. Developers with the working knowledge of .NET would be aware of the use of the TRY? CATCH block in their C# of VB.NET code. In a CLR compliant language a TRY CATCH block is used to handle the exceptions that are raised during the program execution. Similarly TRY匔ATCH is used to handle the transaction abort in T-SQL procedures, triggers, and functions.
The example below explains the use of the TRY匔ATCH block.
For the purpose of this illustration consider two tables. 1. A table in a bank抯 database called AccountInfo. This table contains the information about the account and the available balance. 2. A table called ErrInfo. This table would contain all the error messages that occur in the database. The following listing shows the SQL queries that are used to create the tables.
|
/* AccountInfo Table script*/ CREATE TABLE [dbo].[AccountInfo]( [AccountNumber] [int] NOT NULL, [AccountHolderName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [AvaliableBalance] [money] NULL, [Modified_datetime] [datetime] NULL, CONSTRAINT [PK__AccountInfo] PRIMARY KEY CLUSTERED ( [AccountNumber] ) WITH (IGNORE_DUP_KEY = OFF) ) ON [PRIMARY] /* ErrInfo Table script*/ CREATE TABLE [dbo].[ErrInfo]( |
Listing 1 (Table creation script for AccountInfo and ErrInfo)
Consider that a stored procedure called dbo.AddNewAccount exists in the database. This stored procedure is responsible for adding a new account in to the accountinfo table. The following listing shows the script that is used to create the stored procedure.
|
/* AddNewAccount Procedure script*/ CREATE PROCEDURE [dbo].[AddNewAccount]
|
Listing 2 (dbo.AddNewAccount procedure script)
The Try block begins with a BEGIN TRY keyword. The TRY block must be immediately followed by the CATCH block. Catch block is the block which would contain the exception condition handling code. In the example when an error like violation of a primary key occurs a row would be inserted in to the ErrInfo table. The control is passed to the catch block only when a transaction abort happens.
There are a few caveats in using the TRY...CATCH block. All the statements in the try...catch block will have to be in a single batch of statements. In other words you cannot use the go keyword anywhere in the block. Any error with a severity equaling 21 or more would not be handled in the catch block. As for such severities the connection is terminated by the server.
Summary
The addition of this new construct would help writing robust code. This is surely one of the keywords that would get used the most.