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 |
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 |
|
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 |
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 |
|
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 |
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 |
|
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 |
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 |
|
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 |
THROW cannot contain printf formatting styles. Example : THROW 80000 ,N'@@@%*.*s @@@This is a test message' , 1 ; –OUTPUT |
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.
SQL Server Portal 
excellent article imran really 🙂
[…] that give us more control over the debugging. I also implemented it with some clients and found it much more effective than raiserror. Given below are the guidelines that must be followed to avoid any […]