Sunday, 18 June 2017

RAISERROR vs THROW

RAISERROR
THROW
Use
RAISERROR generates an error message and initiates error processing for the session.
RAISERROR can either reference a user-defined message stored in the SYS.messages catalog view or build a message dynamically.
The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct. 
THROW raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server 2012 and above versions.
Version Support
RAISERROR introduced in SQL Server 7.0. It supports up to SQL Server 2012.
RAISERROR can’t be used in the SQL Server 2014’s Natively compiled Stored Procedures.
THROW introduced in SQL Server version 2012.
THROW can be used in the SQL Server 2014 and above versions.
Microsoft suggests using THROW instead of using RAISERROR.
Syntax
RAISERROR ( { msg_id | msg_str | @local_variable } 
    { ,severity ,state } 
    [ ,argument [ ,...n ] ] ) 
    [ WITH option [ ,...n ] ] 

NOTE:
RAISERROR WITH NOWAIT statement can also be used to flushes all the buffered PRINT/SELECT Statement Messages within a batch.


THROW [ { error_number | @local_variable },
        { message | @local_variable },
        { state | @local_variable } ]
[ ; ]
NOTE:
The SQL statement before the THROW statement must be followed by the semicolon (;) statement terminator but the throw statement may or may not have end with (;).

Can catch original
Exception details?
NO. RAISERROR always generates new exception and results in the loss of the original exception details specify with appropriate parameters.
EXAMPLE:
DECLARE @result INT
BEGIN TRY
   SET @result = 1/0 -- divide-by-zero error
END TRY
BEGIN CATCH
 DECLARE
  @ErrorNumber INT,
  @ErrorSeverity INT,
  @ErrorState INT,
  @ErrorProcedure NVARCHAR(2048),
  @ErrorMessage NVARCHAR(2048)
 
 SELECT  --get the error details
  @ErrorNumber = ERROR_NUMBER(),
  @ErrorSeverity = ERROR_SEVERITY(),
  @ErrorState = ERROR_STATE(),
  @ErrorProcedure=ERROR_PROCEDURE(),
  @ErrorMessage = ERROR_MESSAGE()

 PRINT 'BEFORE RAISERROR';

 RAISERROR (@ErrorMessage,     @ErrorSeverity, @ErrorState)
--WITH NOWAIT        

 PRINT 'AFTER RAISERROR';
            
END CATCH

PRINT 'AFTER CATCH';

OUTPUT:
BEFORE RAISERROR
Msg 50000, Level 16, State 1, Line 22
Divide by zero error encountered.
AFTER RAISERROR
AFTER CATCH

NOTE:
The actual line number of the code which generated Divided By Zero error here is 3, but the exception message returned by RAISERROR is showing it as 22. Also the error number corresponding to Divide by zero error is 8134 in the SYS.Messages table, but the one returned by RAISERROR is 50000.

YES. THROW the original exception caught in the TRY block; we can just specify the THROW statement without any parameters in the CATCH block.
EXAMPLE:
BEGIN TRY
  DECLARE @RESULT INT = 55/0   
END TRY
BEGIN CATCH
  PRINT 'BEFORE THROW';
  THROW;
  PRINT 'AFTER THROW'
END CATCH
  PRINT 'AFTER CATCH'

OUTPUT:
BEFORE THROW
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

NOTE:
THROW returns correct error number and line number and also the PRINT statement before the THROW statement must be followed by the semicolon (;).

Execution Termination
In the above example output you can see the PRINT statement executed after the RAISERROR statement.
In the above example output you can see the PRINT statement not executed after the THROW statement.
Setting
SEVERITY LEVEL
The severity parameter must set to RAISERROR the severity of the exception.
There is no severity parameter set to THROW. The exception severity is always set to 16. (unless re-throwing in a CATCH block)
RAISE SYSTEM ERROR MESSAGE
With RAISERROR we can raise the system error messages which stored in SYS.Messages table.

SELECT * FROM sys.messages

WHERE
message_id <=50000 - System Messages

message_id >50000 - User Defined Messages

EXAMPLE:
RAISERROR (17051,16,1)

OUTPUT:
Msg 17051, Level 16, State 1, Line 1
SQL Server evaluation period has expired.



With THROW we can’t raise the System Exception.

EXAMPLE:
THROW 17051, 'SQL Server evaluation period has expired.', 1;

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

sp_add message
sp_addmessage is used for adding user defined message to SYS.Messages table.

EXAMPLE:

-- Create a user-defined message in U.S. English 
EXEC sp_addmessage  
    @msgnum = 60000, 
    @severity = 16, 
    @msgtext = N'The item named %s already exists in %s.',  
    @lang = 'us_english';


-- Create a user-defined message in French   
EXEC sp_addmessage  
    @msgnum = 60000, 
    @severity = 16, 
    @msgtext = N'L''élément nommé %1! existe déjà dans %2!', 
    @lang = 'French'; 
GO 

SELECT @@LANGUAGE  -- Get to know the system language   


SET LANGUAGE 'us_english' –- Change language to us_english   
SET LANGUAGE 'French'   -– Change language to French   
SET LANGUAGE 'us_english'–- Change language back to us_english   


sp_drop message
sp_dropmessage is used for deleting user defined messages from SYS.Messages table.

EXAMPLE:
-- This statement will fail as long as the localized version 
-- of the message exists. 
EXEC sp_dropmessage 60000; 

OUTPUT:
Msg 15280, Level 16, State 1, Procedure sp_dropmessage, Line 64
All localized versions of this message must be dropped before the us_english version can be dropped.


-- This statement will drop the message. 
EXEC sp_dropmessage 
    @msgnum = 60000, 
    @lang = 'all'; 

OUTPUT:
Command(s) completed successfully.

-- This statement will remove only the localized version of the  
-- message. 
EXEC sp_dropmessage 
    @msgnum = 60000, 
    @lang = 'French'; 

OUTPUT:
Command(s) completed successfully.


RAISE USER DEFINED  ERROR MESSAGE
With RAISERROR we can raise the user defined error messages which stored in SYS.Messages table.

SELECT * FROM sys.messages

WHERE
message_id <=50000 - System Messages
message_id >50000 - User Defined Messages

EXAMPLE:
RAISERROR (60000,16,1, 'salary', 'emp table')

OUTPUT:
Msg 60000, Level 16, State 1, Line 4
The item named salary already exists in emp table.

NOTE:
The msg_str parameter can contain printf formatting styles.
With THROW we can raise the user defined error messages which stored in SYS.Messages table.
EXAMPLE:
DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(60000, N'salary', N'emp table');
THROW 60000, @msg, 1;

OUTPUT:
Msg 60000, Level 16, State 1, Line 15
The item named salary already exists in emp table.

NOTE:
The message parameter can’t support printf formatting styles.
Alternative way is that manipulates the message string using FORMATMESSAGE as shown above example and pass this message string to THROW statement as a parameter.
Summary
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.
The msg_str parameter can contain printf formatting styles.
The severity parameter specifies the severity of the exception.
The error_number parameter does not have to be defined in sys.messages.
The message parameter does not accept printf style formatting.
There is no severity parameter. The exception severity is always set to 16.


No comments:

Post a Comment