Functions in exceptions handling :
- Error_number:- This function returns error number.
- Error_message:- It returns the error message.
- Error_line:- It returns the line number.
- Error_severity:- It returns error severity level.
- Error_state:- It returns error state level.
Every error has got mainly 4 parts :-
- error_number
- error_message
- error_level
- error_state
select * from sys.messages
↓
This table maintains the list of errors.
System defined error is up to 50,000 errors and above 50,000 is user defined errors.
Security level :-
- The severity level between 1 to 25
- If severity level is between 20 to 25 those errors are called ”fatal error”, if this fatal error occurs then client connection will be terminated.
State level :-
- It is used in user defined errors.
Example :-
Create procedure div(@x int,@y int)
As
Begin
Begin try
Declare @z int
Set @z =1
Set @z=@x/@y
Print @z
End try
Begin catch
Print error_message()
End catch
End.
Example :- create procedure insert_rec (@e int,@n varchar, @s int)
As
Begin
Begin try
Insert into emp_temp values(@e,@n,@s)
End try
Begin catch
If error_number()=547
Print ‘check constraint vioated’
Else if error_number ()=2627
Print ‘primery key violated’
End catch
End
Exec insert_rec 1,’a’,1000