Error Handling and Exception handling in SQL Server:-
- An exception means “ Run time error”
- In SQL server if any statement causes run time error then SQL Server returns “error msg” and program execution is continued.
- When any error occurs to display user friendly message and to execute error processing statements and the error must be handled.
- To handle errors we need to provide a block called “try-catch block”.
Syntax :
Create procedure <name> (parameter)
As
Begin
Begin try
Statements → it is causes error
End try
Begin catch
Statements → it is handle error
End catch
End.
if any statement in try block causes exception then control is transfer to catch block and statements in the catch block are executed.
Example :-
Create procedure div (@x int ,@y int)
As
Begin
Begin try
Declare @z int
Set @z=@x/@y
Print @ z
End try
Begin catch
Print ‘ divide by zero error’
End catch
End
↓ ↓
Exec div 10,5 Exec div 10,0
↓ ↓
Output → 2 Output → divide by zero error.