Exception Handling in SQL Server 2005

Exception Handling in 2000:

In SQL Server 2000 Exception handling was implmented with the help of @@ERROR statement. Each and every statement you have to check the value of Error number. Practically it is not easiest things to do.

Exception Handling in 2005:

In .NET programming exception are handled with help of TRY - CATCH block. Similarly TRY - CATCH block is implmented in SQL Server 2005. When you are exceuting the procedure if error occured then catch block statements are exceuted. This technique is applicable only for the run time errors.

The following functions are used to get the error details

ERROR_NUMBER()
ERROR_SEVERITY()
ERROR_STATE()
ERROR_PROCEDURE()
ERROR_LINE()
ERROR_MESSAGE()

Example:

CREATE PROCEDURE PROCEXCEPHANDLING

AS

BEGIN

BEGIN TRY

SELECT 100/0

END TRY

BEGIN CATCH

Select

ERROR_NUMBER() AS ErrNo,

ERROR_SEVERITY() AS ErrSev,

ERROR_STATE() AS ErrState,

ERROR_PROCEDURE() AS ErrProc,

ERROR_LINE() AS ErrLine,

ERROR_MESSAGE() AS ErrMsg

END CATCH

END

Executing the above procedure

Exec PROCEXCEPHANDLING

Output of above statement is