SQL Server 2005 Try and Catch Exception Handling

By:   |   Updated: 2006-07-26   |   Comments (6)   |   Related: 1 | 2 | 3 | > Error Handling


Problem

Error handling in previous versions of SQL Server was not the easiest thing to do.  You could test the value of @@ERROR or check @@ROWCOUNT, but if the error was a fatal error you did not have a lot of options.

Solution

With SQL Server 2005, new error handling has been introduced with the TRY...CATCH processing.  Basically what happens is when an error occurs the processing in the TRY block stops and processing is then picked up in the CATCH block.  The following illustrates a simple example of how this is done:

Step Code Output
1 CREATE PROCEDURE usp_ExampleProc
AS
     SELECT * FROM NonexistentTable;
GO		 Command(s) completed successfully.
2 EXECUTE usp_ExampleProc Msg 208, Level 16, State 1, Procedure usp_ExampleProc, Line 3
Invalid object name 'NonexistentTable'.

(Note: Processing Stops)

3 BEGIN TRY
     EXECUTE usp_ExampleProc
END TRY

BEGIN CATCH
     SELECT
          ERROR_NUMBER() as ErrorNumber,
          ERROR_MESSAGE() as ErrorMessage;
END CATCH;		 208 Invalid object name 'NonexistentTable'.

(Note: Processing Continues)

4 BEGIN TRY
     EXECUTE usp_ExampleProc
END TRY

BEGIN CATCH

END CATCH;		 Command(s) completed successfully.

(Note: Processing Continues)

  • As you can see from the above code and output that when we create the stored procedure for a non-existent table in Step 1 the procedure creates without a problem. 
  • If we run the stored procedure using the code in Step 2, we get an error message that the object does not exist.
  • If we run the stored procedure using the code in Step 3, the error is sent to the CATCH block and an error message is returned. At this point processing can continue without a problem.
  • To further illustrate this in Step 4 the stored procedure is run, the error is caught in the CATCH block, but we are not doing anything to process the error.  Normally you would have something happen, but this shows that you don't have to have any code in the CATCH block.

The values that can be retrieved from the error are also much more detailed, then what you could get with previous versions of SQL Server.  Below is a list of the data that can be retrieved when an error occurs.

  • ERROR_NUMBER() - returns the number of the error.
  • ERROR_SEVERITY() - returns the severity.
  • ERROR_STATE() - returns the error state number.
  • ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_LINE() - returns the line number inside the routine that caused the error.
  • ERROR_MESSAGE() - returns the complete text of the error message.

Running the same query above, but returning all of the error information is displayed below.

ERROR_NUMBER ERROR_SEVERITY ERROR_STATE ERROR_PROCEDURE ERROR_LINE ERROR_MESSAGE
208 16 1 usp_ExampleProc 3 Invalid object name 'NonexistentTable'.

Another nice thing about the TRY...CATCH processing is that you can nest or have multiple TRY...CATCH blocks in your code.  The following although not very practical illustrates how the error is caught and then processing continues and the error is caught again and processing continues again.

Step Code Output
1 BEGIN TRY
     BEGIN TRY
          EXECUTE usp_ExampleProc
     END TRY

     BEGIN CATCH
          SELECT
               ERROR_NUMBER() AS ErrorNumber,
               ERROR_SEVERITY() AS ErrorSeverity;
     END CATCH;

     EXECUTE usp_ExampleProc
END TRY

BEGIN CATCH
     SELECT
          ERROR_NUMBER() AS ErrorNumber,
          ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;

 208 Invalid object name 'NonexistentTable'.

 

208 Invalid object name 'NonexistentTable'.

Next Steps
  • Take a look at how the TRY...CATCH processing can boost your SQL Server error handling
  • Look for ways how you are currently catching and processing errors and how this new option can significantly improve your application and user experience
  • If you still don't have a version of SQL Server 2005, download a trial version so you can see this new feature in action


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2006-07-26

Comments For This Article




Monday, February 18, 2013 - 5:54:31 AM - Tutul Back To Top (22224)

Thanks

 

Saturday, November 3, 2012 - 4:46:27 AM - Dilip Back To Top (20202)

Very nice explanation. Thanks a lot.

 

Regards,

Dilip

Thursday, April 26, 2012 - 10:20:14 AM - Mohan Kumar Back To Top (17130)

Excellent Tutorial for Begineers... Keep the excellent work maintained.. Please upload tutorials related to SSIS, SSRS.

 

With Regards,

K.MOHAN KUMAR

Thursday, April 26, 2012 - 10:18:50 AM - Mohan Kumar Back To Top (17129)

Excellent Tutorial for Begineers...

Friday, March 26, 2010 - 2:25:37 PM - admin Back To Top (5126)

I just tried the examples on SQL 2005 SP2 and they worked fine.

The only way to get this to work is to have one procedure call a sub-procedure, otherwise it does not catch the failure.  Not sure why they did it this way, but that is how it works.  So in essence it is probably not much help unless you call all procedures from a high level calling procedure.

If you do the following this does not work.

CREATE PROC test
AS
BEGIN TRY
  SELECT * FROM NonexistentTable
END TRY

BEGIN CATCH
   -- some code
END CATCH

 

The only way this works is if you have one stored procedure call another stored procedure such as this:

CREATE PROC Test
AS
SELECT * FROM NonexistentTable
GO

CREATE PROC test2
AS
BEGIN TRY
  EXECUTE Test
END TRY

BEGIN CATCH
   -- some code
END CATCH
GO

Friday, March 26, 2010 - 1:53:57 PM - Sladjan Back To Top (5125)
 Not sure is there some configuration issue on all our servers, but even the most basic example which returns Error 208 (like the one above: selecting from non existent table) is not handled by TRY...CATCH statement. It catches error fine for missing stored procedure.

By looking into following link on the Code Project, it looks like it is not only our issue:
http://www.codeproject.com/KB/database/try_catch.aspx

I simple can not believe that writers of this article completely missed the point, so it may be bug in SQL Server (tested on both 2005 and 2008) or there is another server configuration parameter which lowers error level the threshold for TRY...CATCH block???

Any idea?

 

 














get free sql tips
agree to terms