SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


@@Error and sp_rename


@@Error and sp_rename

Author
Message
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6857 Visits: 1865
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"
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35970 Visits: 9518
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."
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13242 Visits: 6903
I apologize to Barry for agreeing with him.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
john.moreno
john.moreno
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 1115
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.
Eric Mamet
Eric  Mamet
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3112 Visits: 925
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!
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13242 Visits: 6903
... 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? Everybody look what's going down. -- Stephen Stills
Eric Mamet
Eric  Mamet
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3112 Visits: 925
indeed
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search