Exception handling in Sql Server

Exception handling is a programming language construct or computer hardware mechanism designed to handle the occurrence of some condition that changes the normal flow of execution.

Error handling plays a vital role when writing stored procedures or scripts. Sql server has matured with years and here is how the error handling is being address

SQL Server Versions before 2005

SQL Server versions before 2005 offered only one simple way to work with exceptions: the @@ERROR function. This function can be used to determine if an error occurred in the last statement that was executed before evaluating @@ERROR.

For example:

SELECT 1/0

SELECT @@ERROR
-----------

Msg 8134, Level 16, State 1, Line 1

Divide by zero error encountered.
-----------
(1 row(s) affected)

In this case @@ERROR returns 8134, which is the error number for a divide-by-zero error.
Using @@ERROR, you can detect errors and control them to some degree. However, proper use of this function requires that you check it after every statement; otherwise it will reset, as shown in the following example:

SELECT 1/0

IF @@ERROR <> 0

BEGIN

SELECT @@ERROR

END
-----------

Msg 8134, Level 16, State 1, Line 1

Divide by zero error encountered.
-----------0

(1 row(s) affected)

Trying to catch the error in this case actually ends up resetting it; the @@ERROR in the SELECT returns 0 rather than 8134 because the IF statement did not throw an exception.

In addition to the fact that the exception resets after each statement, @@ERROR does not actually handle the exception -- it only reports it. The exception is still sent back to the caller, meaning that even if you do something to fix the exception in your T-SQL code, the application layer will still receive a report that it occurred. This can mean additional complexity when creating application code because you need to handle exceptions that may needlessly bubble up from stored procedures.

SQL Server 2005

In SQL Server 2005, exceptions can now be handled with a new T-SQL feature: TRY/CATCH blocks. This feature emulates the exception handling paradigm that exists in many languages derived from the C family, including C/C++, C#, Java and JavaScript. Code that may throw an exception is put into a try block. Should an exception occur anywhere in the code within the try block, code execution will immediately switch to the catch block, where the exception can be handled.

The term "catch" is of special importance here. When TRY/CATCH is used, the exception is not returned to the client. It is "caught" within the scope of the T-SQL that caused it to be thrown.
For an example of TRY/CATCH, consider a divide-by-zero error:

BEGIN TRY

SELECT 1/0

END TRY

BEGIN CATCH

SELECT 'Error Caught'

END CATCH
-----------
(0 row(s) affected)
------------

Error Caught
(1 row(s) affected)

When this batch is run, no exception is reported. Instead, the message "Error Caught" is selected back. Of course, your T-SQL code does not have to send back any kind of specific message in the CATCH block. Any valid T-SQL can be used, so you can log the exception or take action to remedy the situation programmatically, all without reporting it back to the caller.

While merely being able to catch an exception is a great enhancement, T-SQL is also enhanced with new informational functions that can be used within the CATCH block. These functions are: ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_LINE(), ERROR_SEVERITY(), ERROR_STATE() and ERROR_PROCEDURE(). Unlike @@ERROR, the values returned by these functions will not reset after each statement and, as a result, the functions will return consistent values over the entire time a CATCH block is executed.

For instance:
BEGIN TRY

SELECT 1/0

END TRY

BEGIN CATCH

SELECT 'Error Caught'

SELECT ERROR_MESSAGE(), ERROR_NUMBER()

END CATCH
-----------
(0 row(s) affected)
------------Error Caught
(1 row(s) affected)
-------------------------------------------- ---------------

Divide by zero error encountered. 8134
(1 row(s) affected)
In this example, the ERROR_MESSAGE() and ERROR_NUMBER() functions return the correct values, even though a SELECT occurred between the exception and evaluation of the functions -- quite an improvement over @@ERROR!

References

Exception handling best practices in SQL Server 2005

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1189087,00.html