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...
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:
CollapseDeclare @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:
CollapseMsg 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:
CollapseDeclare @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.
CollapseCREATE 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:
CollapseDeclare @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:
CollapseDeclare @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:
CollapseDeclare @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.
- Related Videos
- Related Articles
- Ask / Related Q&A
Finding Long-Lasting Cell Phone Skins For Your Blackberry Model
By: BhratBrij | 01/01/2010If you are going on the Internet to look for places where you can get cell phone skins , which are made-to-measure, you can get customized Blackberry Tour skins, Blackberry Pearl skins, and Blackberry Curve skins quite easily there.
Know more about compact flash 16gb
By: philipwots | 31/12/2009It is almost a known fact that compact flash 16gb is a mass storage device format which is used in portable electronic devices in the market.
Pass4side 000-111 exam questions
By: Adela1987 | 30/12/2009Pass4side 000-111 Exam Resources include Questions and Answers, Practice Testing Software, Stud Guides, Audio Learning and Preparation Labs. These IBM 000-111 Exam Preparation Materials will make you provide the accurate answers of real 000-111 exam questions. With our Exam Resources you can test your knowledge and readiness for exam, assess your performance in a given time, get scores and highlighted weaknesses with suggestions to improve the weak areas.
8 GB USB DRIVE
By: Phillip Roth | 29/12/2009USB flash drive consists of a small printed circuit board carrying the circuit elements and a USB connector insulated electrically and protected inside a plastic, metal, or rubberized case which can be carried in a pocket.
Support Information About AA Online
By: Ed Philips | 26/12/2009Discover how to quit drinking as revealed in Ed Philips "Alcoholics Anonymous Online" support guide, which offers tried and tested alcoholic addiction methods to quit drinking within 21 days.
Know the information USB sticks
By: John Milton | 24/12/2009Now in the market there is high demand of usb sticks among its users. Seeing this the producers of usb sticks now focussing more on its reliability.
A Photography Course For All The Family
By: Dan Feildman | 23/12/2009Excellent Digital Photography Courses Introducing Free Digital Photography Guide called "How To Take Beautiful Digital Pics Made Simple". The latest tips on how to use a digital camera like a pro.
Reinforce Database Management with Remote DBA
By: Kashif Mukhtar | 23/12/2009Databases are the foundation of the digital economy. They store the information that run businesses and organize the vast amount of data that is created on a regular basis. Even though e-business infrastructures are requiring stronger database support to sustain their 24x7 operations, shrinking IT budgets are forcing companies to reduce the size of their DBA staff. With databases growing in size and complexity, the role of the already overburdened DBA is becoming increasingly difficult.
Try and Catch Fun in Sql Server 2005
By: rajib bahar | 07/12/2008 | DatabasesTry 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. This article covers the tsql aspect of exception handling.
Authentication Against Active Directory and Edirectory Via Ldap
By: rajib bahar | 30/11/2008 | ProgrammingThis article shows how to programmatically authenticate against Active Directory and Edirectory via LDAP.