Error Handling in Sql server 2005

Hi,

Error handling is Process of Trapping the Error in any application. In SQL server until 2005 it is very difficult to get the source error and shat causes the exception. To abort the Exception We can use the Below statement

SET XACT_ABORT ON to abort the Error and continue with Next statement

To error the Error Description of the Error we can many statement in SQL SERVER 2005.

To check the Severity of the Error message and its categories you can call the below System Table

There you can see the Pre-defined Error message and its Severity

select * from sys.messages

Severity will be Logged in the System Event Log.

If the Severity is more then it will be displayed as Red in color

We can also give our own error message..

Error_Number() will return the Error number for the Error message

Error_Message() will return the Error message that is occured

Error_Severity() will return the severity of the Error.

Error_procedure() will return the name of the stored procedure or trigger where an error occurred

Error_State() will return the state number of the error that caused

Below is the Syntax of the Exception handling in SQL SERVER 2005

BEGIN TRY

   BEGIN TRANS

    Some Statements

   COMMIT TRANS

END TRY

BEGIN CATCH

PRINT ERROR_NUMBER()

END CATCH