Click here to Skip to main content
13,094,983 members (79,799 online)
Click here to Skip to main content
Add your own
alternative version

Stats

55.1K views
40 bookmarked
Posted 17 May 2006

Try and Catch Fun in SQL Server 2005

, 17 May 2006
Rate this:
Please Sign up or sign in to vote.
This article is about exception handling improvements 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:

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:

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:

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:

 (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.

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:

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:

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:

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Rajib Bahar
Architect
United States United States
Over the course of his 16+year career, Rajib Bahar has been a creative problem solver, finding innovative solutions to clients’ data questions, issues and challenges. He works primarily in Business Intelligence, and Data analytics practice with experience in BigData, DataScience, & Cloud computing environments. His career initially started in the unix world submitting perl and fortran jobs to super-computers back in 2000. This was before Big Data and distributed computing got Big. Later on, he moved on to .NET application development roles, and worked with variety of Database systems such as MS Sql Server, MySQL, PostgresSQL, DB2, & Oracle. Around 2008, he started working in Business Intelligence and/or Datawarehousing practice utilizing both Ralph Kimball and Inmon methodologies. Currently, he is working in Big Data platforms and connecting it with SQL Server 2016, R, Python, and building POCs on Data Science for BI projects. He also enjoys working with visualization tools such as Power BI, Tablue, and QlikView. His recent venture in the Data world includes a podcast on BigData, Data Science, DBMS, analytics, and cloud computing. The podcast is also syndicated across iTunes and YouTube. The link to podcast is http://www.soundcloud.com/data-podcast.

He has also served as a Board of Members of directors at KFAI radio, PASSMN, and various other non-profits. His expertise in data have led to savings at KFAI radio on expensive software license costs, reduced paper expense via online voting. Currently, he spends time contributing to the Data Visualization challenge the station faces.

Feel free to connect with Rajib @rajib2k5 or linkedin.com/in/rajibb

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralIf you need to reach me see http://www.rajib-bahar.com Pin
Rajib Bahar2-Dec-08 9:46
memberRajib Bahar2-Dec-08 9:46 
Generalhi 2 Pin
ahmed eldeghedy31-Mar-08 4:42
memberahmed eldeghedy31-Mar-08 4:42 
Generalhi Pin
ahmed eldeghedy31-Mar-08 4:38
memberahmed eldeghedy31-Mar-08 4:38 
QuestionTry and Catch exception Pin
Jophy Wilson Maliakal30-May-06 0:32
memberJophy Wilson Maliakal30-May-06 0:32 
AnswerRe: Try and Catch exception Pin
Abdul (Rajib) Bahar15-Jun-06 15:44
memberAbdul (Rajib) Bahar15-Jun-06 15:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170813.1 | Last Updated 17 May 2006
Article Copyright 2006 by Rajib Bahar
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid