Exception Handling in SQL Server 2000 and 2005

Till the era of SQL Server 2000, there was no standard and structured way of exception handling in procedures return in SQL. Developers have to use the standard error variable “@@ERROR” to check if any error has occurred and take the necessary action.

A sample code in SQL Server 2000 will look like this:-

BEGIN TRANSACTION

--Write your statements here like “insert into some table”

set @Error = @@ERROR

--if there is some error

if @Error <> 0
Begin
ROLLBACK TRANSACTION
Else
COMMIT TRANSACTION
End
SQL Server 2005 have come with Try-Catch blocks, very similar to those used in frontline programming languages like C# and Java. Hence the same code above can be written in a much structured and efficient way in SQL Server 2005 as follows :-

BeginTry
BEGIN TRANSACTION
-- Do some operations here like “insert into some table”
COMMIT TRANSACTION
EndTry
BeginCatch
ROLLBACK TRANSACTION
EndCatch
Notice the important keywords here – “BeginTry”, “EndTry”, “BeginCatch” and “EndCatch”. These are equivalent of “Try-Catch” blocks used in C# or Java.