Introduction

The new release of SQL Server 2005 has provided some new and interesting features for developers and administrators. No doubt that more preference is given to the tasks performed by the administrator, but there are various functionality enhancements added for the developers to make their SQL code more powerful and error resistant.

Being a developer, the most useful feature I found was the EXCEPTION handling technique. While programming in .NET or any other language, it is a curse if you are not writing your code in a TRY-CATCH block.

Similarly, when an error is thrown while executing a stored procedure through your application, you only get the information that some xyz error has occurred. This kind of error is OK when your application is in production. But while developing, it is not useful, as you have to find out what the error is, which line or procedure generated the error, etc.

Basic syntax

BEGIN TRY
     --sql_statements
END TRY
BEGIN CATCH
     --sql_statements
END CATCH
[ ; ]

Important Points

Here are some important things to know about TRY-CATCH

1. To catch all the errors which are of severity greater than 10 and which do not close the database connection.
2. Each TRY block is associated with only one CATCH block and must be followed immediately by an associated CATCH block.
3. The CATCH block is executed only when any error occurs. If no errors, then the next statement after the END CATCH is executed.
4. The TRY-CATCH block should be in the same batch i.e., A single TRY-CATCH block cannot span multiple batches. Similar for stored procedures or triggers.
5. The TRY-CATCH construct cannot be used within a user-defined function.
6. TRY-CATCH constructs can be nested.
7. On ERROR, the control passes to the first statement in the associated CATCH block.
8. Errors trapped by a CATCH block are not returned to the calling application. If any of the error information must be returned to the application, the code in the CATCH block must do so using mechanisms, such as SELECT result sets or the RAISERROR and PRINT statements.
9. If an ERROR occurs in the CATCH block and if the CATCH block contains a nested TRY-CATCH, it will be passed to the nested CATCH block. If there is no nested TRY-CATCH construct, the error is passed back to the caller.
10. The CATCH block does not handle compile errors, such as syntax errors, that prevent a batch from executing. Also, object name resolution errors are not handled by the CATCH block.

The following functions are used to get the ERROR details:

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

All the above functions can be used in CATCH block, and returns specific values related to the ERROR generated. If they are used outside CATCH block, then all of them will return NULL.

How to USE it?

STEP I: You can create a common procedure that gets you the details of the generated ERROR.

CREATE PROCEDURE ErrorDetails
AS
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
GO

This procedure can be executed in your CATCH block.

STEP II: Your actual code where all the processing is done.

BEGIN TRY
SELECT GETDATE()
      SELECT 1/0
END TRY
BEGIN CATCH
       EXEC ErrorDetails
END CATCH;

On execution of the code in STEP II, the result obtained is:

In SQL Server 2000, the techniques previously used to perform error checking was through @@ERROR and performing GOTO or RETURN. Using TRY-CATCH in your SQL code will help you to monitor your code and handle errors easily.