SQL Server Portal

Whenever we write a code, we need to do the error handling in it and as much as the handling is strong, it is as simple to trap the bug in the system. SQL Server 2012 provided a new and a flexible way of doing the error handling with the additional features to make it effective and efficient.

First, I will discuss how we were doing error handling in 2005/2008 and what was missing in RAISERROR and why we need this new functionality of THROW.

-- Error HANDLING in SQL Server 2005/2008
BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 1 / 0
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

Print ERROR_NUMBER() --Actuall Error Number
Print ERROR_LINE()   --Actuall ErrorLine

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

RAISERROR (@ErrorMessage, -- Message.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
--OUTPUT

8134  — Actual Error Number
4     — Actual Error Line Number
Msg 50000, Level 16, State 1, Line 19
Divide by zero error encountered.

If you look at the error detail, you get the feeling that RAISERROR detail is unable to handle the error properly. It displays the information message like  Divide by zero error encountered which is correct but the rest of the info like error number 50000 & line number 19 is incorrect because these are not the actual error & line numbers respectively.

Now, lets try the same in SQL SERVER 2012 with THROW and notice the accuracy.

-- Error HANDLING in SQL Server 2012
BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 1 / 0
END TRY
BEGIN CATCH
THROW
END CATCH;

--OUTPUT

Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

So, what is it that I did in SQL Server 2012 ? I just replaced all error handling with one keyword “THROW” and it returns more accurate result Like error number 8134 and line number 4  to the caller, than the RAISERROR. Also, it reduces the number of codes.