THROW formatting

Question

Post reply

THROW formatting

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
October 9, 2014 at 10:25 pm

#290825

Comments posted to this topic are about the item THROW formatting
October 10, 2014 at 2:26 am

This was removed by the editor as SPAM
October 10, 2014 at 4:42 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Hmmmm, I had to guess the intent of the question.

As stated in the MSDN page the explanation linked to:

Because the THROW statement does not allow for substitution parameters in the message parameter in the way that RAISERROR does, the FORMATMESSAGE function is used to pass the three parameter values expected by error message 60000.

So directly it is not possible, but it is possible with a workaround.

Needs more caffeine...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Neil Burton SSC-Insane Points: 22905 More actions · Answer 2

Needs more caffeine...

+1

I misread the BOL page and didn't see the bit that told me what I was reading about. I just saw a particular line and thought I saw the correct answer.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 3

We're still on 2008 R2 so I've never used THROW. But I was aware that it was a new (ish) feature, so my reasoning was - well, why wouldn't it accept formatting? So I answered Yes.

Huh! Over-application of logic, or just wishful thinking .... ?

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 4

Bob Cullen-434885 (10/10/2014)
We're still on 2008 R2 so I've never used THROW. But I was aware that it was a new (ish) feature, so my reasoning was - well, why wouldn't it accept formatting? So I answered Yes.
Huh! Over-application of logic, or just wishful thinking .... ?

Mostly wishful thinking 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 5

Thank you for the post, Steve, good one.

(but confusing... for me. I like to see an example of printf style and then the same written in throw line. I thought of usage of %s %d... that kind of stuff, which we can add via sp_addmessage and then using formatmessage and the error number we can use it. like the below

DECLARE @Message NVARCHAR(2048);

SELECT @Message = FORMATMESSAGE(1127);

THROW 50001, @Message, 1;

or did you mean the usage of printf style in the THROW's @message parameter and considering the plain text and using the %s %d... stuff directly in the string?

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Hany Helmy SSChampion Points: 13921 More actions · Answer 6

BWFC (10/10/2014)
Needs more caffeine...
+1
I misread the BOL page and didn't see the bit that told me what I was reading about. I just saw a particular line and thought I saw the correct answer.

-1 :w00t:

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

SqlMel SSCrazy Points: 2901 More actions · Answer 7

SqlMel

SSCrazy

Points: 2901

October 10, 2014 at 6:01 am

#1751565

+1.

Thanks, Steve.

---------------
Mel. 😎

Ed Wagner SSC Guru Points: 287024 More actions · Answer 8

I'm also on 2008, so I've never gotten to play with THROW. However, given that RAISERROR gives me the flexibility I want, why would I switch to THROW? MS will probably deprecate RAISERROR at some point, so I'm sure I'll have to move eventually, but the only benefit I see is that THROW is more in line with error handling in other languages. I don't really care that it is because each language is its own, but I guess MS does.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Xavon SSCrazy Points: 2280 More actions · Answer 9

I'm not sure if this was a double trap or I simply over thought it. I was going to say no, I remembered reading it could not, and had a short list of practical reason I thought it shouldn't. But I convinced myself it must be yes, because otherwise why ask the question.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 10

Stewart "Arturius" Campbell (10/10/2014)
...

Thank you, Stewart.

So he meant usage of placeholders directly in the THROW's message parameter. (because the question asked was "can we use...?" and obviously we can, but not directly, but we can. "Can I use printf style formatting in the messages that I send to the THROW statement directly?" this would have been easier (at least for me).

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 11

Xavon (10/10/2014)
I'm not sure if this was a double trap or I simply over thought it. I was going to say no, I remembered reading it could not, and had a short list of practical reason I thought it shouldn't. But I convinced myself it must be yes, because otherwise why ask the question.

<joke> and you thought having coffee was enough..? knowledge is more then coffee... 😛 😀 </joke>

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 12

Ed Wagner (10/10/2014)
I'm also on 2008, so I've never gotten to play with THROW. However, given that RAISERROR gives me the flexibility I want, why would I switch to THROW? MS will probably deprecate RAISERROR at some point, so I'm sure I'll have to move eventually, but the only benefit I see is that THROW is more in line with error handling in other languages. I don't really care that it is because each language is its own, but I guess MS does.

I think that's it. The more I look at it, the less reason I see to change, but because of deprecation, I'd use THROW for the future.