In the SQL Server before 2005 error handling was not easy we used to work with @@Error Global Variable in order to handle error but SQL Server 2005 onward we have a easy method "Try And Catch" to handle errors.
Syntax :
Begin Try
--Try Statements :
End Try
Begin Catch
--Handle Error Thrown By Try Statement(s)
End Catch
Lets See With the Following Example How Try Catch Block Helps in Handling Error In your Code.
Create Following Table and Store Procedure
Create Table CheckHandling
(
Id Int Identity(1,1),
EmpName Varchar(40),
Salary Decimal(10,2) Not null
)
Go
Create Procedure Usp_CheckHandling
As
Insert Into CheckHandling(EmpName,Salary)
Values('Sanjay Joshi',null)
Go
As You Can See In Above Table “Salary” Column Is Defined as “Not Null” Means Salary Column Doesn’t Allow Null Values. But To Produce Error I Am Trying To Insert Null Into Salary Column Inside The Procedure.
CASE – 1 : Without Using Try Catch Block
Query Window
Exec Usp_CheckHandling
On Executing above Store Procedure the SQL throws following error
Msg 515, Level 16, State 2, Procedure Usp_CheckHandling, Line 3
Cannot insert the value NULL into column 'Salary', table 'TEST80.dbo.CheckHandling'; column does not allow nulls. INSERT fails.
The statement has been terminated.
So Its Clear that if your application executes the above stored procedure then application may encounter the above error resulting in Application Crash.
CASE – 1 : Using Try Catch Block
Now let’s create another Stored Procedure which handles the Exceptions
Create Procedure Usp_HandleException
As
Begin Try
Insert Into CheckHandling(EmpName,Salary)
Values('Sanjay Joshi',null)
End Try
Begin Catch
Print 'Exception Handled'
End Catch
Go
Query Window
Exec Usp_HandleException
On Executing The Procedure You Get Following Message
(0 row(s) affected)
Exception Handled
But you Stored Procedure Executes Safely Without exception.
So Under Catch Block You Can Capture the Error Number and Error Message using Following Function
- ERROR_NUMBER()
- ERROR_MESSAGE()
You Can Create A Log Table And Insert Value Of Above Functions In Catch Block Into the Table To Track Errors. See Following For Example
Create Procedure Usp_HandleException
As
Begin Try
Insert Into CheckHandling(EmpName,Salary)
Values('Sanjay Joshi',null)
End Try
Begin Catch
Insert Into Error_Log(ErrorNumber,ErrorMessage,ErrorDateTime)
Values (ERROR_NUMBER(),ERROR_MESSAGE(),GETDATE())
End Catch
Go
