Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by John Moreno » @@Error and sp_rename
Add to briefcase
17 posts, Page 2 of 2
««12

@@Error and sp_rename Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
ChiragNS
Posted Thursday, September 03, 2009 11:01 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825
sknox (9/3/2009)
Chirag (9/3/2009)
This is what i did
begin try
EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'
end try
begin catch
select @@ERROR
end catch



Yes. That will catch the error as it happens. So you determined the value of @ERROR when an error arises during execution. But the question asked about the value of @@ERROR AFTER execution.

Because the error is not severe enough to stop execution, and the sp executes at least one successful statement after the error (at least the statement to set the return value), the value of @@ERROR after execution is 0.

This is especially important to consider when working with different versions of SQL Server. While you can use a TRY/CATCH in new versions of SQL Server, a lot of systems still run on 2000, which does not support TRY/CATCH. As a consequence, there is a lot of code which relies on selecting @@ERROR after execution; also, a lot of programmers still think this way, even if they're programming on SQL 2005+. Old habits can be hard to break!


OK i seem to get it. So in this case will @@Error always be 0.

"Keep Trying"
Post #782724
RBarryYoung
Posted Friday, September 11, 2009 11:25 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
sknox (9/3/2009)
Chirag (9/3/2009)
This is what i did
begin try
EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'
end try
begin catch
select @@ERROR
end catch



Yes. That will catch the error as it happens. So you determined the value of @ERROR when an error arises during execution. But the question asked about the value of @@ERROR AFTER execution.

Because the error is not severe enough to stop execution, and the sp executes at least one successful statement after the error (at least the statement to set the return value), the value of @@ERROR after execution is 0.

This is especially important to consider when working with different versions of SQL Server. While you can use a TRY/CATCH in new versions of SQL Server, a lot of systems still run on 2000, which does not support TRY/CATCH. As a consequence, there is a lot of code which relies on selecting @@ERROR after execution; also, a lot of programmers still think this way, even if they're programming on SQL 2005+. Old habits can be hard to break!


No, sorry, this explanation is incorrect. As is the answer, at least for SQL Server 2005 and 2008. The only correct answer that I have been able to extract is the one that chirag already demonstrated: 15248.

And for the record: TRY/CATCH does catch the @@error value *after* the stored procedure completes: executing and failing is still a form of execution. The value of @@ERROR after execution is 15248. If you think differently, then please provide evidence.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Post #786825
The Dixie Flatline
Posted Monday, September 14, 2009 7:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 3,788, Visits: 5,538
I apologize to Barry for agreeing with him.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #787442
john.moreno
Posted Monday, September 14, 2009 11:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 10:31 PM
Points: 339, Visits: 950
RBarryYoung (9/11/2009)
sknox (9/3/2009)
Chirag (9/3/2009)
This is what i did
begin try
EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'
end try
begin catch
select @@ERROR
end catch



Yes. That will catch the error as it happens. So you determined the value of @ERROR when an error arises during execution. But the question asked about the value of @@ERROR AFTER execution.

Because the error is not severe enough to stop execution, and the sp executes at least one successful statement after the error (at least the statement to set the return value), the value of @@ERROR after execution is 0.

This is especially important to consider when working with different versions of SQL Server. While you can use a TRY/CATCH in new versions of SQL Server, a lot of systems still run on 2000, which does not support TRY/CATCH. As a consequence, there is a lot of code which relies on selecting @@ERROR after execution; also, a lot of programmers still think this way, even if they're programming on SQL 2005+. Old habits can be hard to break!


No, sorry, this explanation is incorrect. As is the answer, at least for SQL Server 2005 and 2008. The only correct answer that I have been able to extract is the one that chirag already demonstrated: 15248.

And for the record: TRY/CATCH does catch the @@error value *after* the stored procedure completes: executing and failing is still a form of execution. The value of @@ERROR after execution is 15248. If you think differently, then please provide evidence.


What kind of evidence?

If I execute:

EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'
select @@error

on a SQL 2008 instance, in a database with the compatibility level set to 100, I get results of 0 (No column name) and the message:
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 321
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

(1 row(s) affected)

On a SQL 2005 instance in the master database, I get the same resultset and this message:
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 315
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

(1 row(s) affected)

I get these resutls when using SSMS 2005 and 2008 and Query Analyzer from SQL 2000.

Only from within the CATCH block do I see the 15248 answer.
Post #787641
Eric Mamet
Posted Thursday, September 17, 2009 7:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:41 AM
Points: 1,129, Visits: 685
Looks like the moral is that we should test the return code from that procedure...

DECLARE @Return INT

EXECUTE @Return = sp_rename N'fakename', N'fakename2', 'OBJECT'

SELECT @Return

Forget @@Error!
Post #789616
The Dixie Flatline
Posted Thursday, September 17, 2009 9:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 3,788, Visits: 5,538
... unless you are adding additional logic within the procedure itself.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #789772
Eric Mamet
Posted Thursday, September 17, 2009 9:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:41 AM
Points: 1,129, Visits: 685
indeed
Post #789778
« Prev Topic | Next Topic »

Add to briefcase
17 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.