SQL Server 2005 Try and Catch Exception Handling

Introduction

Handling errors in SQL Server became easy with the number of different ways. SQL Server 2005 has introduced the new option that helps to handle the errors effectively. Sometimes we cannot capture the errors which occurred in the end user. Even if we want to know the errors which occurred in the end user, we need to write the code to send it to us. It creates an additional overhead for the server.

SQL Server 2005 introduced TRY...CATCH statement which helps us to handle the errors effectively in the back end. This handling of the exception can provide additional information about the errors.

TRY...CATCH

The TRY...CATCH statement works the same as in the programming languages. First it gets executed in the SQL statement which we have written in the TRY block and if any error occurs, then it will get executed the CATCHblock.

 Collapse | Copy Code

BEGIN TRY
// SQL Statements
END TRY
BEGIN CATCH
//Handle the exception details
END CATCH

There are a number of error handling property statements like the following:

 Collapse | Copy Code

 ERROR_NUMBER()  
 ERROR_STATE()  
 ERROR_SEVERITY()  
 ERROR_LINE()  
 ERROR_PROCEDURE()  
 ERROR_MESSAGE()   

Normally, SQL Server stores the default error messages which occurred in the execution in the following system table:

 Collapse | Copy Code

select * from sys.messages

But we can create our own error message details with the help of this 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
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