October 15, 2006
Tuning stored procedures: Structured exception handling in SQL Server 2005Exception handling 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
- Introducing TRY...CATCH
- Structured vs. unstructured exception handing
- SQL Server 2000 exception handling
- SQL Server 2005 exception handling
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.
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:
RAISERROR ('Houston, we have a problem', 16,1)
SELECT ERROR_NUMBER() as ERROR_NUMBER, ERROR_SEVERITY() as ERROR_SEVERITY, ERROR_STATE() as ERROR_STATE, ERROR_MESSAGE() as ERROR_MESSAGE
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 handing
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
CommissionRating int constraint CHK_ValidateCommissionRating
Check (CommissionRating between 0 and 100))
create table dbo.Application_Error_Log
SQL Server 2000 exception handling
As you will see, this stored procedure contains the unstructured error handling we've used prior to the arrival to SQL Server 2005.
SQL Server 2005 exception handlingYou'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
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.
ConclusionsThe 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.