THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

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.

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 like you would in the other programming languages. It’s certainly not a perfect solution, but it is a start.

Published Tuesday, October 12, 2010 12:46 PM by ejohnson2010
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter said:

Thanks for the blog Eric

October 12, 2010 3:24 PM
 

BlackWasp said:

You mention a lack of "throw". It is true that this is limited but you can use RAISERROR for similar results.

October 13, 2010 4:59 AM
 

Mohammad Elsheimy said:

It's worth mentioning that you can make your T-SQL code transactional by, e.g., adding BEGIN TRANSACTION to the first line of code and adding the ROLLBACK TRANSACTION statement in the CATCH block, and don't forget to commit the transaction using the COMMIT TRANSACTION statement just before the END TRY.

December 25, 2010 12:44 PM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement