Usage of @@ERROR in Transaction

Question

Usage of @@ERROR in Transaction

Viewing 12 posts - 16 through 26 (of 26 total)

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

craig 81366 SSC Eights! Points: 808 More actions · Answer 1

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

The handling of @@ERROR affects whether you commit or rollback the transaction.

* If you commit the transaction (as in this case) then the SELECT returns the value inserted.

* If you rollback the transaction then the SELECT returns 0 rows.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

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

Without the PRINT statement (that resets the @@ERROR value), the IF expression would evaluate as false, and the ELSE part would execute. That would cause the transaction to rollback instead of commit, and that in turn affects what is returned from #tblTrans.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

Yup, like I said, I was obviously having a dense day. Cheers.

Tyle SSC Rookie Points: 36 More actions · Answer 4

Tyle

SSC Rookie

Points: 36

November 16, 2012 at 8:56 am

#1559844

Great question. Learnt something new today.

Revenant SSC-Forever Points: 42467 More actions · Answer 5

Revenant

SSC-Forever

Points: 42467

November 16, 2012 at 9:34 am

#1559855

This is a really good one. Thanks!

SanDroid SSChampion Points: 10068 More actions · Answer 6

L' Eomot Inversé (11/16/2012)
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.

+10 :hehe: Awesome and correct point. However it is spelled practice not practise... 😎

lptech Hall of Fame Points: 3208 More actions · Answer 7

This is something that a DBA might actually be called on to debug.

Kick6Tiger SSCrazy Points: 2380 More actions · Answer 8

L' Eomot Inversé (11/16/2012)
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).

Now that would make an excellent QotD. I didn't realize Try...Catch was subject to the severity of the error.

Aigle de Guerre!

Ken SSCoach Points: 17191 More actions · Answer 9

Nice question and even better discussion. I wasn't aware of the TRY..CATCH limitation on severity either. Thanks for the info Tom!

kalyani.k478 Default port Points: 1400 More actions · Answer 10

kalyani.k478

Default port

Points: 1400

November 19, 2012 at 3:04 am

#1560242

+1:) good question!

asifkareem Ten Centuries Points: 1089 More actions · Answer 11

asifkareem

Ten Centuries

Points: 1089

November 28, 2012 at 10:22 am

#1563312

Good Question, Nice use Try and Catch

Thanks

manik_anu SSCrazy Points: 2377 More actions · Answer 12

nice question.....

Manik
You cannot get to the top by sitting on your bottom.