Fun with THROW: Avoid % and use semi-colons!
Published On: February 13, 2015
Categories: T SQL 1
Back in 2010, when SQL Server 2012 was still in a very early beta phase, I played around a little with the "fix" to error handling in SQL Server:
THROW. I blogged about my experiences, of course, since I came across several things I didn't expect.
Recently I've come across a couple of others and I thought I would share; as time has gone on, I feel there are more people moving to SQL Server 2012, SQL Server 2014, and SQL Database, and thus more people may be converting their error handling code from
THROW. I covered most of the differences in my old post, and those haven't really changed since SQL Server 2012, but there are a couple of subtleties I'd like to cover that will help ease your transition.
THROW ignores "printf" formatting (kind of)
RAISERROR, you can use printf formatting to pass values (literals or variables) into your error messages:
DECLARE @i INT = 55; RAISERROR(N'Hello %d, %s!', 16, 1, @i, N'blat');
Msg 50000, Level 16, State 1, Line 6 Hello 55, blat!
Printf-style substitution is not supported directly in
THROW. The documentation says that this isn't accepted:
The message parameter does not accept printf style formatting.
There are workarounds, of course, if you are intentionally trying to achieve this type of substitution - if you add a message to
sys.messages then you can use
FORMATMESSAGE(), as I described in my previous post.
What I'm seeing is people getting bitten by
THROW when they are not trying to use substitution - they just happen to have
% symbols in their error messages, and while not explicitly accepted as printf-style substitutions, some funny things can still happen:
THROW 50000, N'Do not use LIKE with a leading %.', 1;
Results (from this point on I'm going to leave out the
Msg 50000 bit):
Do not use LIKE with a leading .
% symbol has been stripped out of the error message, making the message somewhat confusing. What if we remove the trailing period?
THROW 50000, N'Do not use LIKE with a leading %', 1;
This actually leads to an empty error message. Some other character sequences lead to unexpected results, too:
THROW 50000, N'm%n is a modulo operation.', 1;
m n is a modulo operation.
In this case,
%n is actually interpreted as
\n (newline / line feed).
So, it is clear that, internally, SQL Server is performing some type of substitution when it comes across the
% symbol, even though the documentation may lead you to believe that it should just be ignored. I sent a message in to two of my trusty SQL Server contacts, and the official word is basically that
THROW shares some code paths with
RAISERROR, and so just about any occurrence of a single
% symbol will cause one of the issues below.
I ran a quick test to see the effects of various characters, and almost everything in the 1-125 ASCII range led to a blank message. Here is the code I ran to generate a set of
THROW commands for all of these characters:
DECLARE @c VARCHAR(3), @ci VARCHAR(3), @i INT = 1, @cr CHAR(2) = CHAR(13) + CHAR(10); WHILE @i <= 125 BEGIN SELECT @ci = CONVERT(VARCHAR(12), @i), @c = CHAR(@i) + CASE @i WHEN 39 THEN '''' ELSE '' END; PRINT 'PRINT ''CHAR(' + @ci + ') (' + @c + N'):'';' + @cr + 'THROW 50000, ''CHAR(' + @ci + ') = ' + '%' + @c + ' .'',1;'+ @cr + 'GO'; SET @i += 1; END
I copied that output to another query window and ran it. The results here were that the error message was blank in all but a few cases:
- % ignored and stripped from message: 32 (space), 33 (!), 46 (.), 48 (0)
- Newline injected into message (% still stripped): 110 (n)
- % escaped (present in message): 37 (%)
- Entire message converted to blank string: All other characters (1-125)
If you want a
% symbol in your
THROW messages, you'll need to escape them by doubling them up. All of the examples work as expected when I changed every instance of
%%, except for the isolated case of
CHAR(37) - which ended up placing
%%% in the message and ultimately still falling to the handling that blanked out the error message. So even when escaping
% there is an edge case where you may still end up with an empty error.
Interestingly, a slightly different approach you could take is to put square brackets around the
%_ sequence. For example, changing the third line of the
+ '[%' + @c + '].'',1;'+ @cr + 'GO';
All in all, this is probably a bug -
THROW should simply ignore
% and treat it like any other character. I was surprised to not find a bug submitted on Connect - the closest one I could find was this rejected item from Erland, who called for
THROW to be completely removed. I would file a bug, but Microsoft has already committed to updating the documentation to warn users of this "problem" - a good indication that the behavior is here to stay. Here is what the documentation will say:
% is a reserved character in the message text of a THROW statement and must be escaped. Double the % character to return % as part of the message text, for example 'The increase exceeded 15%% of the original value.'
THROW provides another argument to always use semi-colons
Since SQL Server 2008, Microsoft has listed in the deprecated features documentation and the T-SQL syntax conventions guide the following (the messaging has appeared in various forms):
- Not ending Transact-SQL statements with a semicolon. - Not using a statement terminator for Transact-SQL statements. - Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.
While I find it extremely unlikely that Microsoft will carry through with this in my lifetime, just because it will break literally billions of lines of code, I do still think it's a good idea for other reasons (see "Ladies and gentlemen, start your semi-colons!").
THROW introduces one more of these "opportunities" that strengthens the argument for always using semi-colons - consider this code:
BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT 'What?' THROW END CATCH
Result: no error message.
THROW is not actually a command here; it has been interpreted as a column alias, totally valid and legal syntax, since it is not a reserved keyword. The following has the same meaning:
SELECT 'What?' AS [THROW]
If you run the batch again, this time adding semi-colons as appropriate:
BEGIN TRY; SELECT 1/0; END TRY BEGIN CATCH; SELECT 'What?'; THROW; END CATCH;
Now the parser knows that
THROW can't possibly be an alias, and the error message is thrown. A similar thing can happen with other commands, for example:
BEGIN TRANSACTION BEGIN TRY SELECT 1/0 COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION THROW END CATCH GO SELECT @@TRANCOUNT -- 1!
You get an error message about not being able to find a transaction with that name. You may not even notice this in your application code because it may not inspect the actual error number and message returned. Much more importantly, you still have an open transaction! If you add semi-colons, this can no longer happen:
BEGIN TRANSACTION; BEGIN TRY; SELECT 1/0; COMMIT TRANSACTION; END TRY BEGIN CATCH; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; THROW; END CATCH; GO SELECT @@TRANCOUNT; -- 0!
While this has been reported as a bug (multiple times), this is exactly why the documentation for
The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
(And yes, the missing semi-colon on
END TRY is intentional. A story for another day.)
THROW adds some interesting capabilities over
RAISERROR as well as better
TRY/CATCH handling, but it does take some things away and also introduces a few new challenges. If you're converting from
THROW or writing new error handling from scratch, please keep these things in mind.
Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com. Aaron's blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.