solving sql server problems for millions of dbas and developers since 2006

Top SQL Server Tools

SQL Sentry Performance Advisor for SQL Server

SQL compliance manager

dbForge SQL Complete

SQL safe backup

SQL Monitor

SQL Server 2005 Try and Catch Exception 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

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'.

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

Related Tips: 1 | 2 | 3 | More | Become a paid author

Last Update: 7/26/2006

Share:

Free SQL Server Learning

Comments and Feedback:

Friday, March 26, 2010 - 1:53:57 PM - Sladjan

Read The Tip

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?

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

Read The Tip

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

Thursday, April 26, 2012 - 10:18:50 AM - Mohan Kumar	Read The Tip
Excellent Tutorial for Begineers...

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

Read The Tip

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

With Regards,

K.MOHAN KUMAR

Sponsor Information

Try the free performance monitoring tool from Idera!

SQL Monitor – For database professionals who need results on Day One. Try it online.

What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant.

Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Are you waiting on SQL Server? Learn about these DMV's.