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.

Featured

  • Microsoft Warns IT Pros on Windows Netlogon Fix Coming Next Month

    Microsoft on Thursday issued a reminder to organizations to ensure that their systems are properly patched for a "Critical"-rated Windows Netlogon vulnerability before next month's "update Tuesday" patch distribution arrives.

  • Microsoft Nudging Skype for Business Users to Teams

    Microsoft on Thursday announced some perks and prods for Skype for Business unified communications users, with the aim of moving them to the Microsoft Teams collaboration service instead.

  • How To Improve Windows 10's Sound and Video Quality

    Windows 10 comes with built-in tools that can help users get the most out of their sound and video hardware.

  • Microsoft Offers More 'Solorigate' Advice Using Microsoft 365 Defender Tools

    Microsoft issued yet another article with advice on how to use its Microsoft 365 Defender suite of tools to protect against "Solorigate" advanced persistent threat types of attacks in a Thursday announcement.

comments powered by Disqus