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 :-

  1. error_number
  2. error_message
  3. error_level
  4. 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