Geeks With Blogs
Madhawa Learns To Blog : C#, Java .net, c#, java,sql, OOAD and more mad memory dumps...

One of the main things you should consider if you believe in good coding is error or exception handling. When it come to programming languages, now all popular object oriented languages have try, catch method to handle an exception.

We can put our code (which might gives errors or exceptions) inside the try block and error handling code in the catch block. This has become the de-facto of error or exception handling now.
But when it comes to T-SQL we hadn’t got that luxury. Error handling in T-SQL was always tedious and tricky job. Most of the time we used not to handle the error at the T-SQL level but handling it in the upper most level. (data access layer or business layer, handling database errors in business layer is a totally wrong practice)

But now in SQL 2005, 2008 you have proper error handling mechanism just like in modern OO languages. You can use try catch in the stored procedures and functions. Actually I knew that I can use try catch in stored procedures but only today I got to know about a more interesting method we can use.

After catching an error what we can to do was a question for me. If it’s in C# or Java we can log the error and may be throw a customized exception to the next layer. (There are so many options in handling errors)
Can you remember throw exception in C#? Of cause you know it. :) Well… we can do the same in T-SQL using RAISERROR function.

Using the RAISERROR is as follows,

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

Pretty good ha…
You can follow up this more in
MSDN.

 

 

Posted on Monday, August 25, 2008 12:35 PM .net , Java , misc | Back to top


Comments on this post: Effective Error handling in SQL Server 2005

# re: Effective Error handling in SQL Server 2005
Requesting Gravatar...
Good One!
Left by Harsh on Sep 19, 2008 7:31 PM

# re: Effective Error handling in SQL Server 2005
Requesting Gravatar...
Good job. But i wonder if we could present absolutely user friendly error messages when one occurs in database layer or business layer of data access layer. There always is the need of such neat and clean machination.

http://dotnetspidor.blogspot.com
Left by sangam on Oct 15, 2008 10:29 AM

# re: Effective Error handling in SQL Server 2005
Requesting Gravatar...
Thx Sangam.

Well... Here we can always replace the error message with sth more user friendly. Is that what you meant?
Left by Madhawa on Oct 15, 2008 5:01 PM

# re: Effective Error handling in SQL Server 2005
Requesting Gravatar...
Verry Good One,
Thanks for Poasting This Kind of Articles.Very Clear and It saves Lot of Time
Thanks
Nishantha
Left by Nishantha Hevavitharana on Mar 03, 2009 8:50 AM

# re: Effective Error handling in SQL Server 2005
Requesting Gravatar...
... "data access layer or business layer, handling database errors in business layer is a totally wrong practice"...

Some would argue that you should allow the error to bubble up to where handling it is of use. Rather than just throwing another exception.

Microsoft even say in http://msdn.microsoft.com/en-us/library/ms229005.aspx that you should not over use exceptions, and that they should be allowed to proporgate up the call stack.
Left by James on Jul 08, 2009 9:29 AM

# re: Effective Error handling in SQL Server 2005
Requesting Gravatar...
Very Good, Clear, short
Left by Karimipour on Jun 26, 2010 1:29 AM

# re: Effective Error handling in SQL Server 2005
Requesting Gravatar...
Thanks Madhava. Nice post. I saved my time with your post.
Left by Ashok kumar on Aug 24, 2011 1:04 AM

# re: Effective Error handling in SQL Server 2005
Requesting Gravatar...
String or binary data would be truncated.
The statement has been terminated.
Left by deepak on Nov 07, 2011 12:27 AM

# re: Effective Error handling in SQL Server 2005
Requesting Gravatar...
Very informative post. Its really helpful for me and beginner too. Check out this link too its also having a nice post related to this post over the internet which also explained very well...

http://mindstick.com/Articles/8da50627-0abd-448d-a100-abe206bf7f66/?Exception%20handling%20in%20SQL%20Server

Thanks
Left by Ajay Singh on Dec 26, 2011 8:54 AM

Your comment:
 (will show your gravatar)


Copyright © Madhawa Karunaratne | Powered by: GeeksWithBlogs.net