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.