Usage of @@ERROR in Transaction

Question

Usage of @@ERROR in Transaction

dfine

Ten Centuries

Points: 1251
More actions
November 15, 2012 at 9:52 pm

#265977

Comments posted to this topic are about the item Usage of @@ERROR in Transaction
[font="Calibri"]Raj[/font]
November 16, 2012 at 1:09 am

This was removed by the editor as SPAM

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

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Good Question!

Few years back I did the same mistake while writing a procedure. Seems my memory is in good condition as I am able to recall that 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

angad.singh88 Old Hand Points: 300 More actions · Answer 2

Really Nice, did not knew about reset of ERROR to 0 when using PRINT.

Thanks for the share.

--Angad Singh
If I Stop Learning, I Cease to Be A SIKH !

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

Great question to close the week, thanks.

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

Carlo Romagnano SSC-Insane Points: 22601 More actions · Answer 4

Best practise: save @@error and @@rowcount to test them later

declare @err int

,@rows int

select 1

SELECT @err = @@error, @rows = @@rowcount

if @err <> 0 or @rows = 0

...

Carlo Romagnano SSC-Insane Points: 22601 More actions · Answer 5

Stewart "Arturius" Campbell (11/16/2012)
Good question, thanks
Another motivation to use TRY CATCH...

+1

Stuart Davies SSCoach Points: 19505 More actions · Answer 6

Carlo Romagnano (11/16/2012)
Stewart "Arturius" Campbell (11/16/2012)
Good question, thanks
Another motivation to use TRY CATCH...
+1

+2

Good question - thanks

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Carlo Romagnano (11/16/2012)
Best practise: save @@error and @@rowcount to test them later
declare @err int
,@rows int
select 1
SELECT @err = @@error, @rows = @@rowcount
if @err <> 0 or @rows = 0
...

This is absolutely right. Today we have a good easy question with the right answer, but it is spoiled by the terrible error about best practise in the explanation. Best practise is what is what Carlo suggests, not what is in the explanation. If the code in the question were used in the test and QA environments the else branch of the if...else compound statement would never be tested, and removing the print statement on moving to production would make that else branch potentially live in production despite it being completely untested. If putting code which can easily be seen never to have been tested into production is best practise that's news to me! The referenced MSDN page even makes the point about copying @@error and using the copy in IF and PRINT statements. How that page can be referenced and the crazy statement about best practise be made in the same explanation is quite beyond me.

Tom

seankyleprice SSC Veteran Points: 214 More actions · Answer 8

Best Practice for 2008 onwards is surely to use TRY CATCH as suggested by Stewart. For 2005 and prior I agree that the method suggested by Carlos would be best practice.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 9

Excellent QotD! Learned something and that is always good.

vk-kirov SSCertifiable Points: 7686 More actions · Answer 10

seankyleprice (11/16/2012)
Best Practice for 2008 onwards is surely to use TRY CATCH as suggested by Stewart. For 2005 and prior I agree that the method suggested by Carlos would be best practice.

TRY CATCH first appeared in SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms175976(v=sql.90).aspx

TomThomson SSC Guru Points: 104773 More actions · Answer 11

seankyleprice (11/16/2012)
Best Practice for 2008 onwards is surely to use TRY CATCH as suggested by Stewart. For 2005 and prior I agree that the method suggested by Carlos would be best practice.

That depends on how low an error severity you want to handle - not all errors will trigger catch. Things with severity 1 to 10 are not caught by catch, so have to be dealt with through @@error if they are to be dealt with at all.

When one is only concerned with severe errors (severity 11 and higher) try-catch is certainly the best method. If one is concerned with both sorts of error, copying @@error inside the try block (and then testing the copy) will allow one to deal with the low severity errors (10 and lower) while the severe errors will be dealt with in the catch block (using error_severity(), not @@error).

Tom

Neil Thomas Mr or Mrs. 500 Points: 544 More actions · Answer 12

I'm obviously having a dense Friday.

How does the explanation (all about @@error) relate to the question: "What is returned from #tblTrans when you run the below code:"...?

I'm not saying that saving the value of @@error and knowing that the PRINT statement affects are useful things to know, I just don't get how it affects "SELECT * FROM #tblTrans".

Rich Mechaber SSChampion Points: 11130 More actions · Answer 13

Neil Thomas (11/16/2012)
I'm obviously having a dense Friday.
How does the explanation (all about @@error) relate to the question: "What is returned from #tblTrans when you run the below code:"...?
I'm not saying that saving the value of @@error and knowing that the PRINT statement affects are useful things to know, I just don't get how it affects "SELECT * FROM #tblTrans".

Neil, comment out the PRINT @@ERROR line and try running the script: now the @@ERROR is consumed only once, so the test for IF @@ERROR = 0 resolves to False and the ROLLBACK block executes. The table contains no rows.

Rich