Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Simple question:

THROW 50000, 'Because of this ->%<- is this message invisible.', 1;
  • you can't pass %s, %d parameters as in RAISERROR
  • you have to use FORMATMESSAGE
  • but THROW failed anyway if % is not doubled (correct displaying)

Tested on Microsoft SQL Server 2012 (SP1) 11.0.3381.0 (X64), 11.0.3000.0 (X64)


Real world example:

DECLARE @ProcParam VARCHAR(30) = 'Hehe%x!';
DECLARE @Message VARCHAR(500) = FORMATMESSAGE('Procedure input %s is wrong', @ProcParam);
PRINT 'Message: ' + @Message;
THROW 50000, @Message, 1;
share|improve this question
    
@swasheck Really? I have only "Msg 50000, Level 16, State 1, Line 1", without message. –  DiGi Dec 5 '13 at 17:11
    
nope. sorry. i was incorrect. i was looking at two different things :S –  swasheck Dec 5 '13 at 17:11
    
It's interesting to note that if you put a space char(32) after the % it will display the message (sans %) –  billinkc Dec 5 '13 at 17:15
add comment

2 Answers

It appears that they left the printf functionality (or at least part of it) in the THROW command but did not include a way to pass in a parameter to it. I tried each of the characters listed in BOL for RAISEERROR as possible "types" %(d, i, o, s, x, X, or u) and each of them gave back a blank. Any other character after the % and you get back a valid string. My guess is that they are replacing the value with a NULL which of course causes any string concatenation to return a NULL and when printed looks blank.

Unfortunately without the code behind it (or at least a MS Developer) to confirm there is no way to be certain.

share|improve this answer
1  
Not quite - %n for example will be treated as \n (try it out). I'm getting confirmation from Microsoft folks and will post a more thorough explanation when I get one. –  Aaron Bertrand Dec 5 '13 at 22:25
    
@AaronBertrand You are right. I went back and checked and got a blank line for a-z (except n) and 1-9. I'm looking forward to seeing what you find out. –  Kenneth Fisher Dec 6 '13 at 1:33
    
This is so sad. They gave us working Exceptions (finally!) but instantly broke it. Message parameters are gone and they even managed to keep some parameter-related code, just for fun. –  DiGi Dec 6 '13 at 16:58
add comment

I agree with Kenneth Fisher ... the value is replaced with NULL ...

DECLARE @ProcParam VARCHAR(30) = 'Hehe%x!';
DECLARE @Message VARCHAR(500) = FORMATMESSAGE('Procedure input ' + @ProcParam + ' is wrong');
THROW 50000, @Message, 1;

Msg 50000, Level 16, State 1, Line 3
Procedure input Hehe(null)! is wrong
share|improve this answer
    
It is not true. You pass %s as FORMATMESSAGE parameter (because you join strings using plus) but you didn't specify any true parameters for replacing. You are calling: FORMATMESSAGE('Error value: %s', NULL, NULL, ... ...) –  DiGi Jan 27 at 14:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.