Try Catch and Error Handling In SQL Server

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