Catching Concepts
Catching Concepts
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...
 |
 OSP Asura SP 925 Suspend Minnow Lure H 04  US $19.00
|
 Catching Concept Hand Crafted Cedar Wood Bass Crankbait Lure A22 US $.99
|
 Catching Concept Hand Crafted Cedar Wood Bass Crankbait Lure A24 US $1.29
|
 deps Spiral Minnow Topwater Propbait Jet Black US $31.99
|
 deps Spiral Minnow Topwater Propbait Blue Moon Galaxy US $31.99
|
 deps Spiral Minnow Topwater Propbait Black Gold US $31.99
|
 7 Blue Fox Foxee Fish Wobble Lures 4 Walleye Salmon2 US $18.99
|
 OSP Rudra 130 SF Floating Saltwater Minnow Lure HF 76 US $27.00
|
 OSP Rudra 130 SF Floating Saltwater Minnow Lure G 35 US $27.00
|
 Megabass Bait X Concept Mat CrawBottom Tapping US $17.85
|
 OSP Asura SP 925 Suspend Minnow Lure P 64 US $19.00
|
 7 Quickfish Flatfish Trolling Lures Walleye Salmon3C US $18.99
|
 Catching Concepts DC2 DEEP DIVING handmade FIBERBOARD crankbait BASS lure 12 16 US $10.99
|
 deps Spiral Minnow Topwater Propbait Clear Wakasagi US $31.99
|
 OSP Rudra 130 SF Floating Saltwater Minnow Lure TG 03 US $27.00
|
 OSP Rudra 130 SF Floating Saltwater Minnow Lure P 23 US $27.00
|
 Megabass Vision 110 Slow Floating Jerkbait No11 Ito Natural US $29.49
|
 4 NEW BLUE FOX FOXEE FISH ELMOS ZIPFISH WOBBLER LURES US $7.95
|
 Megabass Vision 110 Slow Floating Jerkbait No59 Secret Grape US $29.49
|
 Megabass Vision 110 Jerkbait GW Stealth Flash US $29.49
|
 Catching Concepts KC1 small shallow BASS crankbait HANDMADE custom lure 1 4 foot US $9.99
|
 Megabass Vision 110 Slow Floating Jerkbait No 42 GG Flashing Wakasagi US $29.49
|
 7 Pike Musky Salmon Bass fishing trolling lures 1C US $18.99
|
 7 pc Quickfish Flatfish Wobble Lures 4 Walleye Salmon 2 US $18.99
|
 Catching Concepts DC2 deep handmade custom BASS crankbait CEDAR lure 12 18 ft US $9.99
|
 Megabass Vision 110 Jerkbait GP Pro Green US $29.49
|
 deps Spiral Minnow Topwater Propbait Horizon Shad US $31.99
|
 deps Spiral Minnow Topwater Propbait Real Bluegill US $31.99
|
 Megabass Vision 110 Slow Floating Jerkbait GG Wakasagi US $29.49
|
 7pc Blue Fox Foxee Fish Wobble Lures 4 Walleye Salmon2 US $18.99
|
 OSP Asura SP 925 Suspend Minnow Lure M 06 US $19.00
|
 OSP Asura SP 925 Suspend Minnow Lure G 01 US $19.00
|
 deps Spiral Minnow Topwater Propbait Ghost Ayu US $31.99
|
 7 Pike Musky Salmon Bass fishing trolling lures A2 US $18.99
|
 Catching Concepts FPC1 fiber BASS custom HANDMADE crankbait SHALLOW lure 1 4 ft US $10.99
|
 7pc Blue Fox Foxee Fish Wobble Lures Walleye Salmon3C US $18.99
|
 Ima Flit 120 Jerkbait Pro Blue US $17.99
|
 Megabass Vision 110 Slow Floating Jerkbait No54 SF Moroko US $29.49
|
 Megabass Vision 110 Slow Floating Jerkbait No58 Stealth Wakasagi US $29.49
|
 Catching Concepts SC2 handmade REDWOOD custom BASS crankbait lure 2 4 ft deep US $9.99
|
 Catching Concepts KC3 handmade custom BASS crankbait CEDAR lure MID 4 8 FT US $14.99
|
 Megabass Vision 110 Jerkbait Wagin Hasu II US $29.49
|
 Catching Concepts XDC3 deep handmade custom BASS crankbait CEDAR lure 16 20 ft US $14.99
|
 Catching Concepts MC2 handmade REDWOOD custom BASS crankbait lure 8 12 ft deep US $9.99
|
 Catching Concepts PC2 BASS custom HANDMADE crankbait SHALLOW lure 1 4 ft SWEET US $10.99
|
 Megabass Vision 110 Slow Floating Jerkbait No 57 Sexy Ayu US $29.49
|
 Megabass Vision 110 Slow Floating Jerkbait No 4 GG Deadly Black Shad OB US $29.49
|
 Ima Flit 120 Jerkbait American Shad US $17.99
|
 7 Pike Musky Salmon Bass Walleye trolling lures set 4 US $18.99
|
 7pc Blue Fox Foxee Fish Wobble Lures Walleye Salmon 1A US $18.99
|
 Catching Concepts MC2 handmade custom BASS crankbait lure FIBERBOARD LIP 4 8 FT US $10.99
|
 Megabass Vision 110 Jerkbait M Cosmic Shad US $29.49
|
 Megabass Vision 110 Jerkbait HT Ito Tennessee Shad US $29.49
|
 Megabass Vision 110 Jerkbait GP Skeleton Tennessee US $29.49
|
 Catching Concepts XDC3 deep handmade custom BASS crankbait CEDAR lure 16 20 ft US $14.99
|
 Catching Concepts FPC2 fiber BASS custom HANDMADE crankbait SHALLOW lure 1 4 ft US $10.99
|
 OSP Rudra 130 SF Floating Saltwater Minnow Lure TSL 76 US $27.00
|
 OSP Rudra 130 SF Floating Saltwater Minnow Lure TGL 01 US $27.00
|
 OSP Rudra 130 SF Floating Saltwater Minnow Lure S 76 US $27.00
|
 OSP Rudra 130 SF Floating Saltwater Minnow Lure H 68 US $27.00
|
 |
Melissa & Doug Sunny Patch Clicker Crab Toss & Grip List Price: $12.99 Sale Price: $5.50  |
|
Catch some fun at the beach with the Melissa and Doug Sunny Patch Clicker Crab Toss and Grip! This fun tossing and catching game includes 2 soft, self-stick mitts, and 2 balls. Two players simply stand a distance apart, toss the self-stick ball, and try to catch it on their self-stick, Clicker the Crab mitt... |
 |
Casio Men's AW49H-7BV Ana-Digi Dual Time Watch List Price: $18.95 Sale Price: $13.99 |
|
The large, easy-to-read face found on the Casio Men's Ana-Digi Dual Time Watch make it a favorite everyday accessory. This eye-catching timepiece is constructed with a resin case, a stationary black resin-and-stainless-steel bezel, and a black resin wristband with an adjustable buckle clasp for a personalized fit... |
 |
Casio Men's AW48HE-9AV Ana-Digi Dual Time Watch List Price: $34.95 Sale Price: $14.99 |
|
The large, luminous face found on the Casio Men's Ana-Digi Dual Time Watch make it a favorite everyday accessory. This eye-catching timepiece is constructed with a resin case, a black stationary resin-and-stainless-steel bezel, and a black resin wristband with an adjustable buckle clasp for a personalized fit... |
 |
Casio Women's BG5601-4DR Baby-G Square Luminous Color Pink Digital Watch List Price: $79.00 Sale Price: $65.00 |
|
Behold the power of pink. Casio's Baby-G Collection includes watches, like this model #BG5601-4DR that showcase a real wow factor. With a hot pink case, bezel, and strap, this eye-catching timepiece is sure to turn heads... |
 |
Casio Men's MW600E-1AV 10-Year Battery Analog Resin Watch List Price: $23.95 Sale Price: $17.88 |
|
A bold black dial and silver-tone luminous hands lend this Casio Watch #MW600E-1AV its sporty edge. White numbers and indexes on an outer dial, and a sturdy black resin band deliver an eye-catching, stripped-down look that's ideal for everyday wear... |
 |
3H Men's DPS3AB Deep-Pro Stainless Steel Orange Dial Automatic Interchangeable Band Watch List Price: $1,375.00 Sale Price: $1,100.00 |
|
Invigorating and refreshingly full of energy this men’s 3H Deep Pro Ocean diver Sport Watch bestows its wearer with Adventure and Confidence. The oversized round face, Magnified date display, and bold blue numbers ensure quick easy reading for the on-the-go individual... |
 |
3H Men's ODS5RN Oceandiver Stainless Steel Automatic Jumbo Dial Interchangeable Band Watch List Price: $1,450.00 Sale Price: $507.50 |
|
Invigorating and refreshingly full of energy this men’s 3H Ocean diver Sport Watch bestows its wearer with adventure and confidence. Its steel case, protective mineral crystal, and automatic self-wind movement guarantee the strength, durability and precision of the 3H Italia legacy... |
 |
3H Men's ODS2B Oceandiver Stainless Steel Automatic Jumbo Dial Interchangeable Band Watch List Price: $1,450.00 Sale Price: $507.50 |
|
Invigorating and refreshingly full of energy this men’s 3H Ocean diver Sport Watch bestows its wearer with adventure and confidence. Its steel case, protective mineral crystal, and automatic self-wind movement guarantee the strength, durability and precision of the 3H Italia legacy... |
 |
3H Men's GMTS6 GMT52 Globe Dial Automatic Interchangeable Band Watch List Price: $1,450.00 Sale Price: $507.50 |
|
Boasting with unique style and quality this men’s 3H GMT 56 Ocean diver Sport Watch bestows its wearer with adventure and confidence. Its stainless steel case and unidirectional rotating bezel, and protective mineral crystal window guarantee the renowned strength and durability of the 3H Italia legacy... |
 |
Casio Men's MW600E-4AV 10-Year Battery Analog Resin Watch List Price: $23.95 Sale Price: $16.97 |
|
A luminous red dial and silver-tone luminous hands lend this Casio Watch #MW600E-4AV its sporty edge. White numbers and indexes on an outer dial, and a sturdy black resin band deliver an eye-catching, stripped-down look that's ideal for everyday wear... |