Skip to main content

Try/Catch block in Sql Server 2005

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.

Comments

Anonymous said…
If you already have SET XACT_ABORT ON then why do you also rollback the transaction in the catch?

Popular posts from this blog

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul

AWS fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request

While using AWS and trying to copy a file from a S3 bucket to my EC2 instance ended up with this error message. Command Used: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ Error: fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request The error goes off if we add the region information to the command statement. I am using Asia Pacific (Mumbai) so used ap-south-1 as the region name. Modified Command: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ --region ap-south-1

[Non Tech] Want to know the recipe for Omelette :)

Fed up with Bread - Jam and Curd Rice, today i wanted to eat Omelette. Interesting part is I wanted to cook it myself :) So in the first picture you see all the items which are needed for preparing an Omelette. When I had a closer look at the eggs I see that almost all the eggs are broken. But believe me when I bought it couple of days back it was in perfect condition! I was wondering whether the eggs have become rotten or pretty old to consume! I tried taking an egg and break it but couldn't break it at all :) Since I have kept in the freezer all the eggs have frozen and looked like a iron ball :) After trying for few minutes of trying i removed the shell of the egg and then kept that iron ball :) into a bowl and placed it within Oven. I heated it for 1 minute and checked. It melted only to a limit. So i just set it for another 2 minutes and checked it later. It has melted but the part of the egg white has become a Omelette :( I didn't leave it there. I took the bowl out of