Tune stored procedures with exception handling in SQL Server 2005

 
Throughout this series of tips, contributor Joe Toscano discusses how to tune and improve your SQL Server 2005 stored procedure code.

Exception handling

    Requires Free Membership to View

    Login

was widely thought to be one of the weakest aspects of T-SQL script writing. Fortunately, this has changed in SQL Server 2005, which supports structured error handling. This tip focuses first on the basics of the new TRY…CATCH constructs and then looks at some sample SQL Server 2000 and 2005 T-SQL that produces constraint violations using transactional code. Future tips will continue along this theme.

TABLE OF CONTENTS
   Exception handling before SQL Server 2005
   Introducing SQL Server 2005 TRY...CATCH
   Structured vs. unstructured exception handing
   Exception handling under SQL Server 2000
   Exception handling under SQL Server 2005
   Conclusions

 
Exception handling before

In previous versions of SQL Server you would handle exceptions by checking the @@error global variable immediately after an INSERT, UPDATE or DELETE, and then perform some corrective action if @@error did not equal zero. Oftentimes, developers would duplicate this unstructured code, which resulted in repetitive blocks of code, and combine it with GOTOs and RETURNs.
 
Introducing TRY...CATCH

Structured exception handing provides a powerful mechanism for controlling complex programs that have many dynamic runtime characteristics. It is a tried and true practice currently supported by many popular programming languages such as Microsoft Visual Basic .Net and Microsoft Visual C#. You will see in the examples below that utilizing this robust method will make your code more readable and maintainable. The TRY block contains transactional code that could potentially fail, while the CATCH block contains code that executes if an error occurs in the TRY block. If any errors occur in the TRY block, execution is diverted to the CATCH block and the error can be handled while error functions can be used to provide the detailed error information. TRY…CATCH has the following abbreviated syntax:

 BEGIN TRY RAISERROR ('Houston, we have a problem', 16,1) END TRY BEGIN CATCH SELECT ERROR_NUMBER() as ERROR_NUMBER, ERROR_SEVERITY() as ERROR_SEVERITY, ERROR_STATE() as ERROR_STATE, ERROR_MESSAGE() as ERROR_MESSAGE END CATCH

Notice the use of functions in the script above that we are able to use in place of local and/or global variables. These functions should only be used in a CATCH BLOCK and are explained below:

  • ERROR_NUMBER() returns the number of the error.
  • ERROR_SEVERITY() returns the severity.
  • ERROR_STATE() returns the error state number.
  • ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_LINE() returns the line number inside the routine that caused the error.
  • ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names or times.

I'll first demonstrate a simple example with SQL Server 2000, followed by an example with SQL Server 2005 exception handling.
 
Structured vs. unstructured exception handling

Below is a simple example stored procedure to code using SQL Server 2000 and then 2005. Both procedures start with simple tables that do contain constraints our insert will violate. Here is the table schema:

 create table dbo.Titles (TitleID int Primary Key identity, TitleName nvarchar(128) NOT NULL, Price money NULL constraint CHK_Price check (Price > 0)) create table dbo.Authors (Authors_ID int primary key identity, au_fname nvarchar(32) NULL, au_lname nvarchar(64) NULL, TitleID int constraint FK_TitleID foreign key references Titles(TitleID), CommissionRating int constraint CHK_ValidateCommissionRating Check (CommissionRating between 0 and 100)) create table dbo.Application_Error_Log (tablename sysname, userName sysname, errorNumber int, errorSeverity int, errorState int, errorMessage varchar(4000))

 
SQL Server 2000 exception handling

Click here for the source code to the P_Insert_New_BookTitle_2K stored procedure. As you will see, this stored procedure contains the unstructured error handling we've used prior to the arrival to SQL Server 2005.

You've seen the code used in P_Insert_New_BookTitle_2K before. The best you can say is, "At least I have exception handling." The statement below executes the SQL Server 2000 stored procedure.

 exec P_Insert_New_BookTitle_2K 'Red Storm Rising',16.99, 'Tom','Clancy', 200

When we execute the stored procedure with the provided parameters, the insert into the Authors table fails because of an invalid Commission Rating value. Our check constraint flags this invalid value and we see the following error:

Msg 547, Level 16, State 0, Procedure P_Insert_New_BookTitle, Line 23 The INSERT statement conflicted with the CHECK constraint "CHK_ValidateCommissionRating". The conflict occurred in database "Adventureworks2005", table "dbo.Authors", column 'CommissionRating'. The statement has been terminated.

The problem is that we could not stop this message from being sent to the client. So the burden of deciding what went wrong will be placed on the client. Sadly, in some cases, this may be enough for some applications to not use constraints.

Let's try this again but this time we'll use the TRY...CATCH .
 
SQL Server 2005 exception handling

Click here for the source code to the P_Insert_New_BookTitle_2K5 stored procedure. In this new and improved procedure we see the TRY...CATCH block and structured error handling:

Notice the SQL Server 2005 exception handling code is much more streamlined and, therefore, more readable and maintainable. There's no cutting and pasting code of exception handling code and no GOTOs. You'll see the results below when executing this stored procedure:

 exec P_Insert_New_BookTitle_2K5 'Red Storm Rising',16.99, 'Tom','Clancy', 200

When we execute the stored procedure with the provided parameters, the insert into the Authors table fails because of an invalid Commission Rating value. When this happens, execution is diverted to the CATCH block, which rolls back our transaction and inserts a row into our Application_Error_Log using the SQL Server 2005 supplied functions.
 
Conclusions

The new TRY…CATCH blocks certainly make safe coding easier for handling errors, including stopping error messages from ever making it to the client. While it may require a mind shift for many T-SQL programmers, it's one feature that was desperately needed. Keep in mind that by migrating your SQL Server 2000 code to 2005, you may have to change your application if was already designed to handle errors that are shipped to the client. I believe this potential reengineering effort will be worth it in the long run.

ABOUT THE AUTHOR
Joe Toscano is a SQL Server instructor, speaker and database consultant for Micro Endeavors, Inc. He has worked extensively with SQL Server since version 6.5, as well as Sybase and Informix prior to that. Toscano's areas of expertise include Data Transformation Services (DTS), Integration Services (SSIS), Transact-SQL (T-SQL) programming, data warehousing, performance tuning, replication, high availability (mirroring), security, disaster recovery, upgrading, migration projects and lights-out DBA tasks.

This was first published in April 2006

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