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 | 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 EXECUTE usp_ExampleProc 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
