T-SQL error handling with Try...Catch blocks

T-SQL error handling with Try...Catch blocks

SQL Server error handling, or exception handling, has always been a tricky part of T-SQL development. It used to be that you had to manually check for errors after running each statement and handle them on a case-by-case basis. SQL Server transaction management was also a chore because you had to figure out the state of your transactions and roll them back or commit them as appropriate.

With SQL Server 2005, Microsoft gives us newer, more robust error handling capabilities by adding Try...Catch blocks to T-SQL. In this tip, we will look at how Try...Catch blocks work and how you can put them to use in your code. We will also look at some comparisons between the old ways and the new ways so you'll have a better understanding of how using Try...Catch can bring your error

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in July 2007

handling into the 21st century.

Try...Catch defined

In order to understand Try…Catch blocks, you have to understand exception handling. Exception handling is simple really: When something unplanned occurs during

More on T-SQL and SQL Server performance tuning:

 the execution of your code, an exception, you need to have a routine that can handle that exception. How you handle the exception is up to you as the developer.

In order to add exception handling to your T-SQL code, you use Try…Catch blocks. If you have used Visual Studio recently, you are probably familiar with the concept of Try…Catch blocks. Their implementation in T-SQL is similar but actually has fewer options than the full-blown implementation you would encounter in Visual Studio. To use Try…Catch, put the T-SQL code you want to run inside of a Try block. If an error with a severity of 10 or higher occurs during the execution of the Try code, execution is passed to exception handling code in a Catch block. The basic construct is shown below:

 BEGIN TRY [T-SQL Code Goes Here] END TRY BEGIN CATCH

 [Exception Handling Code Goes Here] END CATCH

When using Try…Catch in T-SQL, here are a few crucial points to keep in mind:

  • Try blocks must be followed directly by a Catch block, anything else will cause an error.
  • Try…Catch cannot span batches.
  • If the Try code runs without fail, execution passes to the first line following the end of the Catch block.
  • When Catch code completes, execution passes to the first line following the end of the Catch block.

Handling errors

When an error occurs, you as the developer need to decide how to deal with it. Since you cannot pass control back to code that caused the error, like you can in .NET languages, you will likely log the problem and roll back any transactions that may be in-flight. To help with logging, there are several system functions that can provide more information about an error. The available system functions are detailed below:

  • ERROR_NUMBER() - Returns the error number.
  • ERROR_SEVERITY() - Returns the error's severity.
  • ERROR_STATE() - Returns the error's state.
  • ERROR_PROCEDURE() - Returns the name of the stored procedure or trigger in which the error occurred.
  • ERROR_LINE() - Returns the line number on which the error occurred.
  • ERROR_MESSAGE() returns the actual text of the error message.

Using these functions, you can record the details of the error and return that information to the caller of the procedure or log it for troubleshooting purposes. The functions will only work when called inside a Catch block; trying to call them otherwise would return NULL. They will, however, work within the scope of the code running in the Catch block. This means that you can call a stored procedure to handle the error and that stored procedure can access the error functions. Here is an example of just such a stored procedure:

 CREATE PROCEDURE spLogError AS --Return error details to calling

 application SELECT ERROR_NUMBER() AS ErrNum, ERROR_SEVERITY() AS ErrSev,

 ERROR_STATE() as ErrState, ERROR_PROCEDURE() as ErrProc, ERROR_LINE()

 as ErrLine ERROR_MESSAGE() as ErrMsg --Log error to error databases

 INSERT INTO SQLErrors.dbo.ErrorLog VALUES(ERROR_NUMBER(), ERROR_SEVERITY(),

 ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(),ERROR_MESSAGE())

This procedure can then be called in your Catch block to return and log the error details.

 BEGIN TRY --INSERT CODE HERE: When an error occurs, control will be

 passed to the Catch block END TRY BEGIN CATCH --This proc will log the

 error and send the details back to the calling application

 EXEC spLogError END CATCH

Using the XACT_STATE() function

There is one more error function that we have not discussed, the XACT_STATE() function. This function can be called in your Catch blocks to return the exact transaction state of your procedure. The return code from XACT_STATE() will have one of the following values:

  • 1 : There is an active transaction that could be either committed or rolled back.
  • 0 : There is no active transaction.
  • -1 : There is an active transaction, but errors have occurred and the transaction cannot be committed.

Based on the return of this function, you can handle your in-flight transaction. If you receive a 1, you can roll back or commit as normal. If you receive a 0, there are no open transactions, and attempting a commit would generate an error. The special case is -1. That means there was a transaction, but it is not committable. You are also unable to roll back to a save point when you receive a -1 code; the entire transaction must be rolled back.

Error and exception handling is crucial to any good coding standards. Now with SQL Server 2005 you can implement advanced error handling using Try…Catch blocks.

ABOUT THE AUTHOR
Eric Johnson (MCSE, MCITP: Database Administrator, MCSD) is co-founder of Consortio Services and the primary Database Technologies consultant. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Eric has presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. He is president of the Colorado Springs SQL Server Users Group.

 

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top