In earlier versions of Microsoft SQL Server, it is really a taunting job to handle errors. Even after all that efforts we feel like something is missing in our error handling technology, a kind of imperfect feeling. The happy news for those who are in to the SQL Server 2005 is that, it’s shipped with much more elegant techniques of error handling. Today we discuss about such an error handling method which came inbuilt in SQL Server 2005. As like in the .NET languages TRY/CATCH blocks are introduced in SQL Server 2005. In previous versions what we had to track an error was @@ERROR. But the bad thing about this was we need to constantly check for whether an error occurred or not. Here by using the TRY/CATCH blocks it will catch the errors automatically whenever it occurs.

The syntax looks like this:
SQL
BEGIN TRY

“Your code goes here”

END TRY
BEGIN CATCH

“Your error condition code goes here”

END CATCH


What happens when TRY/CATCH block implemented. Let’s go into the details. Whatever code you are writing inside the try block will get executed by default. When an error occurred for the code which is inside the try block it will stop execution and jump to catch block and run the code which you had written for handling the error, just like the traditional method of error handling. One thing we need to notice here is that there are different error level types. 0 - 10 are Warnings, 11 - 19 are Trappable Errors and 20 - 25 are Terminal Errors.

SQL Server 2005 had dished out some additional functions also to make our job easy. Let’s check out some of the important functions among them and what they returns.
  • ERROR_NUMBER() – Show you the error number. You can find all error numbers and descriptions in sysmessages table.
  • ERROR_MESSAGE() – Shows the readable description of the error.
  • ERROR_LINE() – Returns the line number from where error thrown.
  • ERROR_SEVERITY() – Will show up error levels.
  • ERROR_STATE() – Shows what kind of error it’s. Example 1 for system errors
  • ERROR_PROCEDURE() – This gives you the procedure name which causes the error.

With all these I hope error handling got much easier. Enjoy implementing these.