Shreveport Gun and Target Club

Try and Catch Fun in Sql Server 2005

Introduction

Try and Catch is very popular among the developer community writing code in C#, C++, or other high level languages. The conventional term referring to Try-Catch blocks is Exception Handling. Exception Handling is simply a breach of an application’s predefined assumptions. It enables us to provide a reliable data/process validation mechanism in our applications. SQL Server did not have any close counterpart for it until now. Prior to SQL Server 2005, many of us relied on the variable @@ERROR. If there was any deviant behavior, then @@ERROR would capture a non-zero value to indicate the error code.

Requirement

Please make sure the following are available at hand:

• SQL Server 2005 (any version listed here).
• AdventureWorks database (can be downloaded from Microsoft).

Keep in mind that AdventureWorks does not come installed by default in the SQL Server Express edition. In short, AdventureWorks is a database for a fictitious company. Sample examples from Microsoft utilize this database as a way to provide proof of concept.

Implementation

Many of us may have seen something like the following as a way to inform errors:

Collapse

Declare @deadline int

set @deadline = 0

SELECT DaysToManufacture / @deadline

from AdventureWorks.Production.Product

WHERE ProductID = 921

if @@ERROR <> 0

begin

print ‘Error occurred’

end

This outputs:

Collapse

Msg 8134, Level 16, State 1, Line 1

Divide by zero error encountered.

Error Occurred

For the most part, the above works fine, but it’s not as robust as exception handling. It does not give us the flexibility that a try..catch block construct does. Let’s see how this would look in the current world:

Collapse

Declare @deadline int

set @deadline = 0

BEGIN TRY

SELECT DaysToManufacture / @deadline

from AdventureWorks.Production.Product

WHERE ProductID = 921

END TRY

BEGIN CATCH

print ‘Error Occurred’

END CATCH;

This outputs:

Collapse

(0 row(s) affected)

Error Occurred

Does this mean @@ERROR goes away? No, one can still get access to the error value contained in @@ERROR. However, SQL Server 2005 defines several functions whose value can be obtained only within the scope defined within Begin Catch…End Catch. They are ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(). BOL or Books Online has a helper procedure namely usp_GetErrorInfo which gets the error related information for us. Later examples from this article will utilize this procedure.

Collapse

CREATE PROCEDURE usp_GetErrorInfo

AS

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

GO

Let us modify the first script slightly:

Collapse

Declare @deadline int

set @deadline = 0

BEGIN TRY

SELECT DaysToManufacture / @deadline

from AdventureWorks.Production.Product

WHERE ProductID = 921

END TRY

BEGIN CATCH

EXECUTE usp_GetErrorInfo;

END CATCH;

This outputs:

Let’s modify the second script to check its behavior outside of the Catch block’s scope:

Collapse

Declare @deadline int

set @deadline = 0

SELECT DaysToManufacture / @deadline

from AdventureWorks.Production.Product

WHERE ProductID = 921

SELECT usp_GetErrorInfo;

This outputs:

So far, a variety of ways to handle exceptions has been covered. In SQL Server 2005, it is possible to work with nested Try..Catch blocks. This means that within the scope of a Catch block, one could check whether the logic to cover for predefined cases is breached again. Modifying the earlier query gets to make it look something like:

Collapse

Declare @deadline int

set @deadline = 0

BEGIN TRY

SELECT DaysToManufacture / @deadline

from AdventureWorks.Production.Product

WHERE ProductID = 921

END TRY

BEGIN CATCH

BEGIN TRY

execute usp_GetErrorInfo

select ‘Error occurred at: ‘ + GetDate() – format exception

END TRY

BEGIN CATCH

select ‘Error Occurred’

END CATCH;

END CATCH;

This outputs:

Conclusion

One would wonder why anyone would bother adding the extra bit of syntax. It seems too much structured work and overhead to existing practices. The examples above that used exception handling allowed the execution flow to run smoothly. If you compare the output between @@ERROR and Try..Catch, then it is possible to notice that the SQL Server manager didn’t abruptly go to the tab showing the warning. Instead, it showed the result set that one could easily pick up on the application side. Also, try to see it this way a wise man once said, “Brakes are put in cars so that one could drive freaking fast”. Exception handling in SQL Server 2005 is here to help.

About the Author

My personal/quasi-work blog:

http://www.rajib-bahar.com

Work related:

http://www.icdotnet.com

http://www.icsql.com

YouTube Vlogs:
http://www.youtube.com/icsql – sql tutorials in video
http://www.youtube.com/rajib2k5 – my random vlog on art and other stuff outside of the geek world…

DrTy ScOpErZ Modern Warfare 2 S&D Quick Scope Tri-tage

NEW NcSTAR AK RAIL TOP COVER SCOPE MOUNT RAIL MTAK MAK TACTICAL TRI RAIL 47 90 $20.00

Tactical Tri-rail flat top Scope Mount picatinny weaver $9.06

Tiberius Arms Tri-Rail Riser Scope Mount Picatinny Weaver Mount Clamp with Four $25.99

Mosin Nagant Tactical Tri-Rail Scope Mount $32.99

Walther Style 3-9×40 Red Green Mil-Dot Tri-Rail Rifle Scope fit 20mm & 11mm Rail $44.95

SmartMouth Alcohol-Free Mouthwash, Fresh Mint, 16-Ounce Box (Pack of 2) $19.80 2-Bottles, 2-Pump Mouthwash System8 fl oz per Solution (Net Wt 16 fl oz (480 ml) Total)Fresh Mint Flavor SmartMouth Mouthwash eliminates and prevents bad breath for 12 hours after every rinse. Rinse in the morning for fresh breath all day…rinse at night and wake up with fresh breath. – Alcohol-free – Safe for Diabetics – Will Not Stain Teeth, Fillings or Dentures – Powered by Zinc- Dentist …

Uncle Mikes 1^ Non Tri-Lock Swivel Md: 14022 . $6.95 NON TRI-LOCK Flawless fit and silent operation. Detach quickly. Better fit for molded stock with molded-in swivels. 1 “…

Sugoi Women’s K-Scope Tri Tank The look and feel of the K-Scope Tri Tank is hard to beat. The sublimated graphic will have heads turning, and the Ultra Aero and Vector Stretch fabric is superior for moisture management and aerodynamics. Available in three fun colors, the K-Scope Tri Tanks feature two elastic back pockets, a 10″ front zip, and a supportive ESP mesh inner bra. * SUGOI flat seam * Zone construction: mesh …

0035033 1/35 SF14z Periscope & RK31 Aiming Scope Set …

AR15 Flattop Weaver Tri-Rail Mount $18.99 .223 Tri-Rail Flattop Mount…