Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER 2012 – Differences Between RAISERROR and THROW’

A few months ago, I wrote an article about THROW a new technique to raise an exception and transfer execution to a CATCH block shipped with SQL Server 2012. I also compared throw with Raiserror in that article partially. Later on I found a lot of differences and found that THROW is much better than RAISERROR. Let me create a sample error message to demonstrate the difference.

EXEC sys.sp_addmessage
     @msgnum   = 80000
    ,@severity = 10
    ,@msgtext  = N'This is a test message.'
    ,@lang = 'us_english'; 
GO

Let me explain the differences:

No

RAISERROR

THROW

1


If you pass any message_id to RAISERROR, the message_id must be available in sys.messages.

Example :

RAISERROR (51000, -- Message id.
 10, -- Severity,
 1, -- State,
 N'This is a test message');

–OUTPUT
Msg 18054, Level 16, State 1, Line 1
Error 51000, severity 10, state 1 was raised, but no message
with that error number was found in sys.messages.
If error is larger than 50000, make sure the user-defined
message is added using sp_addmessage.


If you pass any message_id to THROW, the message_id is not necessary to be available in sys.messages.
Example :

THROW 51000
, 'This is a test message'
, 1
;

–OUTPUT
Msg 51000, Level 16, State 1, Line 1
This is a test message

2


If you pass any message_id to RAISERROR, the message_id range must be between 13000 and 2147483647 and it cannot be 50000.
Example :

RAISERROR (49913, -- Message id.
           10, -- Severity,
           1, -- State,
           N'This is a test message');

–OUTPUT
The server could not load DCOM. Software Usage Metrics cannot be started without DCOM.


If you pass any message_id to THROW, the message_id range must be between 50000 and 2147483647.
Example :

THROW 49913
, N'This is a test message'
, 1
;

–OUTPUT
Msg 35100, Level 16
State 10, Line 1
Error number 49913 in
the THROW statement
is outside the valid range.
Specify an error number
in the valid range of
50000 to 2147483647.

3


If you pass any existing message_id to RAISERROR, it will act upon the same Severity level.

Example :

RAISERROR (80000, -- Message id.
           10, -- Severity,
           1, -- State,
           N'This is a test message'); 

–OUTPUT
This is a test message.


If you pass any message_id to THROW, whatever the Severity level is, it will always show Severity level 16.
Example :

THROW 80000
, N'This is a test message'
, 1
;

–OUTPUT
Msg 80000, Level 16
State 16, Line 1
This is a test message

4


If you use RAISERROR to trap the bug, it will raise the error but with the incorrect error number.
Example :

BEGIN TRY 
    DECLARE @VALUE INT
    SET @VALUE = 1 / 0 
END TRY 
BEGIN CATCH 

DECLARE @ErrorMessage NVARCHAR(4000); 
DECLARE @ErrorSeverity INT; 
DECLARE @ErrorState INT; 

SELECT
@ErrorMessage = ERROR_MESSAGE(), 
@ErrorSeverity = ERROR_SEVERITY(), 
@ErrorState = ERROR_STATE(); 
  
RAISERROR (
@ErrorMessage, -- Message. 
@ErrorSeverity, -- Severity. 
@ErrorState -- State. 
          ); 
END CATCH; 

–OUTPUT
Msg 50000, Level 16
State 1, Line 15
Divide by zero error encountered.


If you try to use THROW to raise the error, it will give you the exact error number.
Example :

BEGIN TRY 
    DECLARE @VALUE INT
    SET @VALUE = 1 / 0 
END TRY 

BEGIN CATCH
  THROW;
  END 
CATCH











;

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

5


RAISERROR can contain printf formatting styles
Example :

RAISERROR (
N'@@@%*.*s@@@',
10,
1,
0,
30,
N'This is a test message 
For SQL Server 2012');
GO

–OUTPUT
@@@This is a test message For SQL@@@


THROW cannot contain printf formatting styles.
Example :

THROW 80000
,N'@@@%*.*s
@@@This is a test message'
, 1




;

–OUTPUT
Msg 80000, Level 16
State 1, Line 1

Conclusion :
As per the above differences THROW function leads over RAISERROR and in my opinion also THROW is better to raise an exception and transfer execution to a CATCH block but again it depends upon the requirements.

Advertisements

Read Full Post »