Click here to Skip to main content
13,864,419 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


56 bookmarked
Posted 1 Aug 2009
Licenced CPOL

Overview of Error Handling in SQL Server 2005

, 1 Aug 2009
Rate this:
Please Sign up or sign in to vote.
Overview of Error and Exception Handling in SQL Server 2005 using @@Error and Try-Catch

Table of Contents


During development of any application, one of the most common things we need to take care of is Exception and Error handling. Similarly we need to take care of handling error and exception while designing our database like inside stored procedure. In SQL Server 2005, there are some beautiful features available using which we can handle the error.

When We Need To Handle Error in SQL Server

Generally a developer tries to handle all kinds of exception from the code itself. But sometimes we need to handle the same from the DB site itself. There are some scenarios like, we are expecting some rows should come when we will execute the store procedure, but unfortunately SP returns none of them. Below points can be some possible scenarios where we can use error handling:

  • While executing some DML Statement like INSERT, DELETE, UPDATE we can handle the error for checking proper output
  • If transaction fails, then we need to rollback - This can be done by error handling
  • While using Cursor in SQL Server

Error Handling Mechanism

The two most common mechanisms for error handling in SQL Server 2005 are:

  • @@ERROR
  • TRY-CATCH Block

Let's have a look at how we can implement both @@Error and Try-Catch block to handle the error in SQL Server 2005.

Using @@ERROR

We can consider @@ERROR as one of the basic error handling mechanisms in SQL Server. @@Error is a Global Variable in SQL Server. This variable automatically populates the error message when a certain error occurred in any statement. But we have to trace it within just after the next line where the actual error occurred, otherwise, it will reset to 0.

General Syntax

General syntax for @@ERROR is as follows:

Select @@ERROR

Return Type


It returns the Error Number.

Sample Example

I have a table named StudentDetails with columns, Roll (int), Name (varchar) and Address (varchar). Now I am intentionally trying to insert a char in Roll field:

insert into StudentDetails (roll,[Name],Address)
 values ('a','Abhijit','India')

This will throw the following Error :

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'a' to data type int.

Check out the Message and number, it is 245. Now, I am executing the @@Error statement just after this statement and check out the output:

Select  @@Error 

The output is:


So, @@Error returns the same error as return by insert command. As I have already said, @@Error returns the error number for the last Transact-SQL statement executed, so if we execute any @@Error statement, we will get output 0.

When We Should Use @@Error 

There are some scenarios where we should use @@ERROR:

  • With Insert, Delete, Update, Select Into Statement
  • While using Cursor in SQL Server (Open, Fetch Cursor)
  • While executing any Stored Procedure

Using Try...Catch Block

This is available from SQL Server 2005 Onwards. This is generally used where want to trap or catch error for multiple SQL statements like or a SQL Block of statement. TRY...CATCH blocks are the standard approach to exception handling in modern programming languages. Use and syntax are likely the same as normal programming language. Like Exception Handling in Programming Language, we can use nested Try-Catch block in SQL Server also.

Try block will catch the error and will throw it in the Catch block. Catch block then handles the scenario.


General Syntax

Below is the general syntax for Try-Catch block:

-- SQL Statement
-- SQL Statement
   -- SQL Statement or Block
   -- SQL Statement or Block
-- SQL Statement

Whenever there are some errors in TRY Block, execution will moved to CATCH block.

Sample Example

As I have already discussed about the studentDetails table, I am now going to insert one record in the table with Roll='a'.

     INSERT INTO StudentDetails(Roll, [Name])
     VALUES('a', 'Abhijit')
   SELECT 'There was an error while  Inserting records in DB '

As Roll is an int type but I am trying to insert a char type data which will violate the type conversion rule, an error will be thrown. So the execution pointer will jump to Catch block. And below is the output:

There was an error while  Inserting records in DB

Now, to get the details of the error SQL Server provides the following System function that we can use inside our Catch-block for retrieving the details of the error. Please check the below table:

Function Name Description
ERROR_MESSAGE() Returns the complete description of the error message
ERROR_NUMBER() Returns the number of the error
ERROR_SEVERITY() Returns the number of the Severity
ERROR_STATE() Returns the error state number
ERROR_PROCEDURE() Returns the name of the stored procedure where the error occurred
ERROR_LINE() Returns the line number that caused the error

Here is one simple example of using System Function:

   INSERT INTO StudentDetails(Roll, [Name])
    VALUES('a', 'Abhijit')
   SELECT  ' Error Message: '  + ERROR_MESSAGE() as ErrorDescription

I have executed the same code block here but rather than showing custom message, I am showing the internal Error message by Calling ERROR_MESSAGE() System function. Below is the output:


Nested TRY-CATCH Block 

Like other programming languages, we can use Nested Try catch block in SQL Server 2005.

  print 'At Outer Try Block'
      print 'At Inner Try Block'
      print 'At Inner catch Block'
   print 'At Outer catch block'

If we execute this, the output will look like:

At Outer Try Block
At Inner Try Block

Now, Inner catch blocks throw an error:

  print 'At Outer Try Block'
      print 'At Inner Try Block'
      INSERT INTO StudentDetails(Roll, [Name])   _
		VALUES('a', 'Abhijit')  -- Throwing Exception
      print 'At Inner catch Block'
   print 'At Outer catch block'

Which gives the following output:

At Outer Try Block
At Inner Try Block
At Inner catch Block

Try-Catch Block For Transaction Roll Back 

Here I am going to explain one real life scenario of using TRY-CATCH block. One of the common scenarios is using Transaction. In a Transaction, we can have multiple operations. If all operations executed successfully, then database will commit otherwise we need to ROLLBACK.

  I want to delete a Particular Records from Both Student
  Details and Library. Database will only commit, iff both 
  delete statement execute successfully, If fails it will Roll
  back. Intentionally  I have passed a wrong roll ( Which causes)
  the exception and transaction will rollback.
   -- Start A Transaction

   -- Delete Student From StudenDetails Table
   DELETE FROM StudentDetails WHERE Roll = '1'
   Print 'Delete Record from Student Details Table'
   -- Delete The Same Student Records From Library Table also
   DELETE FROM Library  WHERE Roll = 'a'
   Print 'Delete Record from Library Table'
   -- Commit if Both Success
   -- Update Log Details
   Insert into LogDetails(ID,Details) values ('1','Transaction Successful');
 Print 'Transaction Failed - Will Rollback'
  -- Any Error Occurred during Transaction. Rollback
    ROLLBACK  -- Roll back

Below is the output:

Delete Record from Student Details Table
Transaction Failed - Will Rollback

Points of Interest

I have written and explained each and every thing very easily and with a practical example. Hope this will help you.

Please give your feedback and suggestions.

Future Study

Here is one of the good links for future reference for SQL Server 2005 Error Handling:


  • Initial post : 1st August, 2009


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Abhijit Jana
Technical Lead
India India
.NET Consultant | Former Microsoft MVP - ASP.NET | CodeProject MVP, Mentor, Insiders| Technology Evangelist | Author | Speaker | Geek | Blogger | Husband

Blog :
Web Site :
Twitter : @AbhijitJana
My Kinect Book : Kinect for Windows SDK Programming Guide

You may also be interested in...

Comments and Discussions

QuestionWhen does it go to At Outer catch block Pin
UptownIT5-Jun-17 16:56
memberUptownIT5-Jun-17 16:56 
PraiseGreat Article. Pin
Ashish Aim23-May-16 22:49
memberAshish Aim23-May-16 22:49 
QuestionGood Pin
Sibeesh Venu28-Jul-14 17:14
professionalSibeesh Venu28-Jul-14 17:14 
Questionperformace issue Pin
ajaykumarsinghkush29-May-13 23:19
groupajaykumarsinghkush29-May-13 23:19 
GeneralMy vote of 5 Pin
priya naidu118-Dec-12 19:51
memberpriya naidu118-Dec-12 19:51 
GeneralThanks Pin
Mr pawan28-Nov-12 20:00
memberMr pawan28-Nov-12 20:00 
Questiongood work Pin
Neelesh Shukla21-Oct-12 22:07
memberNeelesh Shukla21-Oct-12 22:07 
AnswerGreat One!! Pin
naga.cherry24-Sep-12 5:12
professionalnaga.cherry24-Sep-12 5:12 
QuestionKeep writing..!!!!!!!! Pin
Deepak15309627-Apr-12 2:29
memberDeepak15309627-Apr-12 2:29 
GeneralMy vote of 5 Pin
Srikar Kumar5-Mar-12 4:06
memberSrikar Kumar5-Mar-12 4:06 
QuestionInteresting. Pin
Luc Pattyn4-Sep-11 4:07
professionalLuc Pattyn4-Sep-11 4:07 
GeneralError Handling Pin
Ashishmau2-Mar-11 22:45
memberAshishmau2-Mar-11 22:45 
GeneralPlease keep write this kind of articles Pin
thatraja15-Jan-10 22:31
protectorthatraja15-Jan-10 22:31 
GeneralRe: Please keep write this kind of articles Pin
Abhijit Jana16-Jan-10 7:44
mentorAbhijit Jana16-Jan-10 7:44 
GeneralHandle this errror Pin
Andrei Ion Rînea7-Sep-09 8:41
memberAndrei Ion Rînea7-Sep-09 8:41 
GeneralRe: Handle this errror Pin
Abhijit Jana21-Oct-09 18:43
mentorAbhijit Jana21-Oct-09 18:43 
GeneralGood one definitely...4 from my side.. Pin
Arindam Sinha2-Aug-09 1:44
memberArindam Sinha2-Aug-09 1:44 
GeneralRe: Good one definitely...4 from my side.. Pin
Abhijit Jana2-Aug-09 1:47
mentorAbhijit Jana2-Aug-09 1:47 
GeneralExcellent Pin
Abhishek Sur1-Aug-09 22:10
professionalAbhishek Sur1-Aug-09 22:10 
GeneralRe: Excellent Pin
Abhijit Jana1-Aug-09 22:33
mentorAbhijit Jana1-Aug-09 22:33 
GeneralMy vote of 3 Pin
Hristo-Bojilov1-Aug-09 11:09
memberHristo-Bojilov1-Aug-09 11:09 
GeneralRe: My vote of 3 Pin
Abhijit Jana1-Aug-09 11:24
mentorAbhijit Jana1-Aug-09 11:24 
GeneralRe: My vote of 3 Pin
Hristo-Bojilov1-Aug-09 11:53
memberHristo-Bojilov1-Aug-09 11:53 
GeneralRe: My vote of 3 Pin
Abhijit Jana1-Aug-09 12:54
mentorAbhijit Jana1-Aug-09 12:54 
GeneralExcellent Pin
Md. Marufuzzaman1-Aug-09 8:18
mentorMd. Marufuzzaman1-Aug-09 8:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.190214.1 | Last Updated 1 Aug 2009
Article Copyright 2009 by Abhijit Jana
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid