Try Catching These Errors

The TRY...CATCH syntax offers more error-catching elegance than GOTO statements.

Transact-SQL is a great language for data manipulation, but it has its weaknesses. Unlike real programming languages, T-SQL is confined to procedural code. Sure, you can build modules by using stored procedures and functions, but for the most part, all of the work will be procedural.

T-SQL in the past also has lacked error handling syntax, which meant were stuck with having to write GOTO statements and labels to control the flow. If you hadn't noticed, SQL Server 2005 introduced TRY...CATCH blocks to T-SQL. While the implementation in T-SQL is not as robust as what you'd find in object-oriented languages, it's a good start and it's better than GOTO statements.

Let's take a look at how TRY...CATCH works. Basically, you wrap some portion of your T-SQL code in a TRY block and handle any errors that occur in a CATCH block, like this:

BEGIN TRY
   SELECT * FROM dbo.SALES

   SELECT 1/0
END TRY
BEGIN CATCH
   PRINT ERROR_MESSAGE()
END CATCH

In the example, we have two select statements in our TRY block, and if either of these encounter an error, control will be passed to the CATCH block. In this case, the SELECT 1/0 should cause a divide-by-zero error and pass control to our CATCH block. Once in the CATCH block, you have access to several functions which will provide details of the error, which are self-explanatory:

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

These functions can be used to log details of the error that occurred or simply return information to the user who executed the code. If your TRY block completes without error, then control will be passed to the first line after your CATCH block.

That's pretty much it. There is some nesting ability with TRY...CATCH, but you won't see any other control flow options like RESUME or THROW, which you'd see in other programming languages. It's certainly not a perfect solution, but it's a start.

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.