User Defined Errors SQL Server :-

  • These errors are defined by user.
  • These errors are raised by using the following statement

                     Raiserror (error number/error message, error level, error state).

Example  :-  Of user defined error : Create procedure div(@x  int,@y int)

                               As

                            Begin

                           Begin try

                          Declare   @z int

                      If @y=1

              Raiserror(‘divide by one error’,15,1)

                  Set @z=@x/@y

                   Print @z

                    End try

                   Begin catch

             Print error_message ( )

                   End catch

                     End

                        ↓

                     Exec  div  10,1

                          ↓

Output → divide by one error.

Create table emp_temp :-

            (eno int,ename varchar(20),sal smallmoney)

To create a procedure to insert the record into emp_temp.

Create procedure

                Insert  _rec(@e int,@n varchar(20),@s smallmoney)

                As

               Begin

              Begin try

          If exists (select * from emp_temp where eno=@e)

              Raiserror(‘employee already exists’,15,1)

               If @s<3000

             Raiserror(‘sal>3000’,15,1)

   Insert into emp_temp values (@c,@n,@s)

         End try

     Begin catch

   Print error_message()

End catch

End

   ↓

Exec  insert

Sp_addmessage  :-

Built-in procedure used to add our errors to sys.message table.

Example  :- sp_addmessage 50005,15,’employee already exists’

                                       ↓

            Select * from sys.messages.

Program :- Create procedure

                 Insert _rec(@e int,@n varchar(20) ,@s  smallmoney)

                     As

                Begin

               Begin try

If exists (select * from emp_temp

                               Where eno=@e)

  Raiserror(50005,15,1)

 If @s<3000

   Raiserror(‘sal>3000’,15,1)

Insert into emp_temp values (@e,@m,@s)

     End try

    Begin catch

    Print error_messge ( )

       End catch

    End.