Exception Handling In SQL Server

Similar to other programming languages SQL server also supports exception handling i.e handles the exceptions, provide a proper message to the user regarding exception and avoid abnormal termination of the program.

Up to SQL sever 2000, a system variable called @@Error is use to get information about the exception and handle the exception but from SQL Server 2005 onwards exception handling is done by using try catch.

Within SQL server,  a try block can have only one catch block.

Syntax:

         begin try

          <statements>

         end try

          begin catch

          <statements>

         end catch

Error Functions

To get information about raised exception a set of functions are provided, which are collectively called as Error Functions are new in SQL 2005.

• Error_Number() : Returns the unique number of raised error.

• Error_Message() : Returns the message associated with the current  error raised.

• Error_Severity() : Returns the severity of the current error raised.

• Error_State() : Returns the state of current error raised.

• Error_Procedure() : Returns name of the procedure in which exception occurs.

• Error_Line() : Returns the line number within the procedure where error occurs.

Geting List of SQL Server error messages:
select * from sys.messages

You can add your own error messages to the error messages list of SQL server using the stored procedure sp_add message.

Raising Errors Manually:
There are situations where system will not raise an exception for your requirement and you want to raise exception manually for this use the riase error function that has following syntax :

raiserror(msgid/msgtext,severity,state[,arguments])