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

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :

Screen scraping using XmlHttp and Vbscript ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > &l

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