Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2005 Try and Catch Exception Handling


By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | More > Error Handling

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Thanks

 


Saturday, November 03, 2012 - 4:46:27 AM - Dilip Back To Top

Very nice explanation. Thanks a lot.

 

Regards,

Dilip


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

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

Excellent Tutorial for Begineers...


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

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
 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?

 

 


Learn more about SQL Server tools