Throughout this series of tips, contributor Joe Toscano discusses how to tune and improve your SQL Server 2005 stored procedure code.
Exception 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
[IMAGE] Exception handling before SQL Server 2005
[IMAGE] Introducing SQL Server 2005 TRY...CATCH
[IMAGE] Structured vs. unstructured exception handing
[IMAGE] Exception handling under SQL Server 2000
[IMAGE] Exception handling under SQL Server 2005
[IMAGE] Conclusions
[IMAGE][IMAGE] Exception handling before[IMAGE] Return to Table of Contents
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.
[IMAGE][IMAGE] Introducing TRY...CATCH[IMAGE] ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
;Return to Table of Contents
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:
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.
[IMAGE][IMAGE] Structured vs. unstructured exception handing[IMAGE] Return to Table of Contents
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:
[IMAGE][IMAGE] SQL Server 2000 exception handling[IMAGE] Return to Table of Contents
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.
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 .
[IMAGE][IMAGE] SQL Server 2005 exception handling[IMAGE] Return to Table of Contents
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:
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.
[IMAGE][IMAGE] Conclusions[IMAGE] Return to Table of Contents
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.
