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 RAISERROR to 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)

In 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');

Results:

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):

The % 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;

Results:

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 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 potentially cause these issues.

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 % to %%, 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 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 PRINT command above to the following leads to much different results, essentially the sequence is ignored in all cases (except, again, CHAR(37)):

  + '[%' + @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 a Connect item, #687430 from Erland Sommarskog. He called for THROW to be completely removed, which is a little harsh, and the bug was rejected (and Connect has since died). 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, 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 1
  THROW
END CATCH

Result: no error message.

Explanation: THROW is not actually a command here; it has been interpreted as a column alias for the integer constant 1, totally valid and legal syntax, since it is not a reserved keyword. The following has the same meaning:

SELECT 1 AS [THROW]
-- or
SELECT THROW = 1

If you run the batch again, this time adding semi-colons as appropriate:

BEGIN TRY;
  SELECT 1/0;
END TRY
BEGIN CATCH;
  SELECT 1;
  THROW;
END CATCH;

Now SQL Server knows that THROW can't possibly be an alias, and the error message is thrown as expected. 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 THROW says:

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.)

Conclusion

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 RAISERROR to THROW or writing new error handling from scratch, please keep these things in mind.