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.
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])