Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

@@Error and sp_rename Expand / Collapse
Author
Message
Posted Thursday, September 03, 2009 11:01 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
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
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: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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
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: Monday, April 14, 2014 7:45 AM
Points: 3,949, Visits: 5,944
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
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: Thursday, April 10, 2014 5:20 PM
Points: 341, Visits: 1,031
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
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: Tuesday, April 08, 2014 1:45 AM
Points: 1,165, Visits: 742
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
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: Monday, April 14, 2014 7:45 AM
Points: 3,949, Visits: 5,944
... 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
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: Tuesday, April 08, 2014 1:45 AM
Points: 1,165, Visits: 742
indeed
Post #789778
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse