Recently, I wrote (a rather long) post on Exception handling in T-SQL/TRY…CATCH – Underappreciated features of Microsoft SQL Server. As part of my research, I encountered a few interesting points about the current (and future) exception handling mechanisms in Microsoft SQL Server. I thought of sharing them with you via this post.
The days of RAISERROR are numbered
Almost for time immemorial now, RAISERROR has been a handy command for database developers and administrators alike. However, RAISERROR has a very major drawback in the fact that by default, it was unable to re-raise the original error and also unable to maintain the originating line number of an error. (You can re-raise error numbers from 13000 through 2147483647, but not 50000.)
However, despite of this, RAISERROR is a very useful, and widely used command – and I would go out on a limb here and say that no production application exists that does not use RAISERROR. Unfortunately, if we look at the preview documentation of SQL 11 (“Denali”), we are in for a surprise:
“This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. New applications should use THROW instead.” (Reference: http://msdn.microsoft.com/en-us/library/ms178592%28SQL.110%29.aspx)
The days of RAISERROR are numbered.
THROW – A comparison with RAISERROR
THROW is a command introduced in SQL 11 (“Denali”) to overcome the limitations of RAISERROR. You can read more about this from Books On Line at: http://msdn.microsoft.com/en-us/library/ee677615(SQL.110).aspx. The THROW command allows us to re-raise an error message, thus allowing us to raise the exception throw a series of nested programming objects. However, today, let us explore briefly a comparison between the RAISERROR and THROW commands, as mentioned in the “Denali” preview documentation.
Raising system and custom messages
RAISERROR had the ability to raise system messages, and also custom messages. Let us see if THROW can do the same.
--Select a system message which is less than 50000
SELECT TOP 1 *
FROM sys.sysmessages
WHERE msglangid = 1033 and error < 50000
ORDER BY error DESC
--Attempt to RAISERROR and THROW this system message
RAISERROR (49903, 16, 1);
THROW 49903, 'RAM related message.', 1
Following is the output indicating that THROW cannot throw a system error message. When explicitly specified, the value of the error_number parameter in THROW must be greater than or equal to 50000 and less than or equal to 2147483647.
Msg 49903, Level 16, State 1, Line 8
Detected (null) MB of RAM. This is an informational message; no user action is required.
Msg 35100, Level 16, State 10, Line 9
Error number 49903 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647.
Now, let us try to raise a non-system message, that is not defined in sys.sysmessages
--Attempt to RAISERROR and THROW a non-system message, not defined in sys.sysmessages
RAISERROR (51000, 16, 1);
THROW 51000, 'This is a custom error!', 1
Following is the output indicating that RAISERROR failed because it failed to find the corresponding error message in the sys.sysmessages table. THROW however, succeeded in raising the error.
Msg 18054, Level 16, State 1, Line 2
Error 51000, severity 16, 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.
Msg 51000, Level 16, State 1, Line 3
This is a custom error!
Now, let us define a custom error message is sys.sysmessages and attempt to raise a non-system message:
--Add a new custom error message
EXEC sys.sp_addmessage @msgnum = 51000, @severity = 16, @msgtext = N'This is a custom error! Error parameters are: %d.';
--Attempt to RAISERROR and THROW a non-system message
RAISERROR (51000, 16, 1);
THROW 51000, 'This is a custom error!', 1
Here’s the output. Both RAISERROR and THROW worked fine.
Msg 51000, Level 16, State 1, Line 5
This is a custom error! Error parameters are: (null).
Msg 51000, Level 16, State 1, Line 6
This is a custom error!
Handling message formatting
RAISERROR allows for substitution of parameters in the error message.
--Attempt to RAISERROR the non-system message created earlier
RAISERROR (51000, 16, 1, 999);
Here’s the output:
Msg 51000, Level 16, State 1, Line 2
This is a custom error! Error parameters are: 999.
Let’s see if THROW allows us to do this:
--Attempt to THROW the non-system message created earlier
THROW 51000, 'This is a custom error! Error parameter: %d', 1, 999
Here’s the output
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘,’.
THROW can therefore, not handle parameterization of message in the way that RAISERROR does. There is however, one other way. Here’s how you can do this:
--Attempt to THROW the non-system message created earlier
DECLARE @ErrorMsg VARCHAR(100) = FORMATMESSAGE(51000,999);
THROW 51000, @ErrorMsg, 1
Here’s the output:
Msg 51000, Level 16, State 1, Line 3
This is a custom error! Error parameters are: 999.
In this aspect, THROW and RAISERROR provide similar functionality, and RAISERROR can be replaced with minimal changes to the code. Luckily, FORMATMESSAGE is not being deprecated.
Changing Severity levels
RAISERROR allows us to specify severity levels greater than 18 if we use the WITH LOG option:
--Attempt to RAISERROR the non-system message created earlier - this time with a severity of 24
RAISERROR (51000, 24, 1, 999) WITH LOG;
Here’s the output:
Msg 2745, Level 16, State 2, Line 2
Process ID 55 has raised user error 51000, severity 24. SQL Server is terminating this process.
Msg 2745, Level 16, State 2, Line 2
Process ID 51 has raised user error 51000, severity 24. SQL Server is terminating this process.
Msg 51000, Level 24, State 1, Line 2
This is a custom error! Error parameters are: 999.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Unfortunately, THROW does not come with any way to set the severity level, when used outside of a TRY…CATCH construct. Within a TRY…CATCH construct, THROW obeys the severity level of the exception being raised.
Please NOTE:
When used outside of a TRY…CATCH construct, THROW results in termination of the session.
Some other differences:
The following are some differences that I noticed:
- Because we cannot change the severity level, there is no way to THROW a non-severe error
- It is required that the statement before a THROW must end in a semi-colon (;)
Conclusion
Whether THROW is good or better than RAISERROR, I will leave the it up to the reader to decide. According to me, THROW has it’s advantages over RAISERROR, but needs to mature a little more. Any new code that I write for SQL 11 (“Denali”) will have THROW in them – it is better to start the migration from RAISERROR now itself rather than wait for the release of SQL Server after “Denali” when RAISERROR will simply fail to work.
Do you know of any other differences between RAISERROR and THROW? If yes, share them here, or write a blog post, and link back to this post.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 