TSQL TRY…CATCH

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. It has in the past also lacked error handling syntax leaving you with the need to write GOTO statements and labels to control the flow. Well, 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 that in the object-oriented languages, it’s a good start and its 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 as shown below.

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 our 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.

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 that executed the code. If your TRY block completes without error, then control will be passed to the first line after your CATCH block.

Read more: Eric Johnson

Posted via email from .NET Info