Madhawa Learns To Blog : C#, Java

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.