In this article let us look into a very basic example for using TRY..CATCH block in Yukon (code name of Sql Server 2005).
Things to know before proceeding futher are listed below. FYI, the definitions are extracted from the MSDN for the benift of those who doesn't have it:
Set xact_abort
1. When Set xact_abort is on, if a TSQL statement raises a run-time error, the entire transaction is terminated and rolled back.
2. When OFF, only the TSQL statement that raised the error is rolled back and the transaction continues processing.
3. Compile errors, such as syntax errors, are not affected by Set xact_abort.
IGNORE_DUP_KEY
Specifies the error response to duplicate key values in a multiple-row INSERT transaction on a unique clustered or unique nonclustered index. When on and a row violates the unique index, a warning message is issued and only the rows violating the UNIQUE index fail. When OFF and a row violates the unique index, an error message is issued and the entire INSERT transaction is rolled back. When processing an UPDATE statement, IGNORE_DUP_KEY has no effect. The default is OFF.
Errors in TSQL code can be processed using a try...CATCH construct similar to the exception handling features of the C++ and C# languages. A try...CATCH construct consists of two parts ; a try block and a CATCH block. When an error condition is detected in the TSQL statements contained in a try block, control is passed to a CATCH block where it can be processed.
A try block starts with the begin try statement and ends with the end try statement. one ormore TSQL statements can be specified between the begin try and end try statements.
A try block must be followed immediately by a CATCH block. A CATCH block starts with the begin CATCH statement and ends with the end CATCH statement. In transactionsact-SQL, each try block is associated with only one CATCH block.
When an error occurs within a try block, control is transferred to the associated CATCH block. The CATCH block handles the exception, and the program control is then transferred to the first TSQL statement that follows the end CATCH statement. If the end CATCH statement is the last statement in a stored procedure, trigger, or user-defined function, control is returned to the code that invoked the procedure, trigger, or function.
Sample table structure
Create table TestErrorHandling
(
EmployeeNumber int,
FirstName varchar(50),
Salary money NULL,
constraint [pkEmpNum] primary key clustered (EmployeeNumber)
with (ignore_dup_key = off)
) on [primary]
Go
Source code of the Stored procedure with TRY..CATCH block
Create proc NewEmployee
@EmpNum [int],
@FirstName [varchar](50),
@Salary [money]
as
Set xact_abort on
Begin try
begin transaction
insert into TestErrorHandling (EmployeeNumber,FirstName,Salary)
values (@EmpNum, @FirstName, @Salary)
commit transaction
End try
Begin Catch
Rollback transaction
Print 'Error :: Emp No provided already exists. Try with another number.'
End Catch
Go
Testing the stored proc
Exec NewEmployee 11,'Vadivel',100
Try running the above execute statement twice and you could see the error message written in the CATCH block being fired.
Things to know before proceeding futher are listed below. FYI, the definitions are extracted from the MSDN for the benift of those who doesn't have it:
Set xact_abort
1. When Set xact_abort is on, if a TSQL statement raises a run-time error, the entire transaction is terminated and rolled back.
2. When OFF, only the TSQL statement that raised the error is rolled back and the transaction continues processing.
3. Compile errors, such as syntax errors, are not affected by Set xact_abort.
IGNORE_DUP_KEY
Specifies the error response to duplicate key values in a multiple-row INSERT transaction on a unique clustered or unique nonclustered index. When on and a row violates the unique index, a warning message is issued and only the rows violating the UNIQUE index fail. When OFF and a row violates the unique index, an error message is issued and the entire INSERT transaction is rolled back. When processing an UPDATE statement, IGNORE_DUP_KEY has no effect. The default is OFF.
Errors in TSQL code can be processed using a try...CATCH construct similar to the exception handling features of the C++ and C# languages. A try...CATCH construct consists of two parts ; a try block and a CATCH block. When an error condition is detected in the TSQL statements contained in a try block, control is passed to a CATCH block where it can be processed.
A try block starts with the begin try statement and ends with the end try statement. one ormore TSQL statements can be specified between the begin try and end try statements.
A try block must be followed immediately by a CATCH block. A CATCH block starts with the begin CATCH statement and ends with the end CATCH statement. In transactionsact-SQL, each try block is associated with only one CATCH block.
When an error occurs within a try block, control is transferred to the associated CATCH block. The CATCH block handles the exception, and the program control is then transferred to the first TSQL statement that follows the end CATCH statement. If the end CATCH statement is the last statement in a stored procedure, trigger, or user-defined function, control is returned to the code that invoked the procedure, trigger, or function.
Sample table structure
Create table TestErrorHandling
(
EmployeeNumber int,
FirstName varchar(50),
Salary money NULL,
constraint [pkEmpNum] primary key clustered (EmployeeNumber)
with (ignore_dup_key = off)
) on [primary]
Go
Source code of the Stored procedure with TRY..CATCH block
Create proc NewEmployee
@EmpNum [int],
@FirstName [varchar](50),
@Salary [money]
as
Set xact_abort on
Begin try
begin transaction
insert into TestErrorHandling (EmployeeNumber,FirstName,Salary)
values (@EmpNum, @FirstName, @Salary)
commit transaction
End try
Begin Catch
Rollback transaction
Print 'Error :: Emp No provided already exists. Try with another number.'
End Catch
Go
Testing the stored proc
Exec NewEmployee 11,'Vadivel',100
Try running the above execute statement twice and you could see the error message written in the CATCH block being fired.
Comments