Feeds:
Posts
Comments

Posts Tagged ‘SQL Server’

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.

~Mayank

Read Full Post »