Try Catching These Errors
The TRY...CATCH syntax offers more error-catching elegance than GOTO statements.
- By Eric Johnson
- 03/22/2010
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.