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


@@Error and sp_rename


@@Error and sp_rename

Author
Message
john.moreno
john.moreno
Right there with Babe
Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)

Group: General Forum Members
Points: 747 Visits: 1115
Comments posted to this topic are about the item @@Error and sp_rename
Adrian Ionita
Adrian Ionita
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 22
The correct answer is 11.

EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'

"Msg 15248, Level 11, State 1, Procedure sp_rename, Line 321
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong."
Adrian Ionita
Adrian Ionita
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 22
sorry, the value of @@error is 0, my mistake
ChiragNS
ChiragNS
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11287 Visits: 1865
I found the question confusing. It asks for value of @@Error which is 15248. I put the statement in a TRY/CATCH and got the @@Error as 15248.

"Keep Trying"
sknox
sknox
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5601 Visits: 3032
Chirag (9/3/2009)
I found the question confusing. It asks for value of @@Error which is 15248. I put the statement in a TRY/CATCH and got the @@Error as 15248.


The question is "What is the value of @@Error after executing this statement:".
A TRY/CATCH will catch errors which happen during execution.

If you run this code:


EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'
SELECT @@Error



you will see the error 15248 message, but @@Error will be 0.
ChiragNS
ChiragNS
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11287 Visits: 1865
This is what i did
begin try
EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'
end try
begin catch
select @@ERROR
end catch



"Keep Trying"
sknox
sknox
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5601 Visits: 3032
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!
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7251 Visits: 1619
Though I selected " 0 " as answer but still not sure about @@error.

SQL DBA.
john.moreno
john.moreno
Right there with Babe
Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)

Group: General Forum Members
Points: 747 Visits: 1115
SanjayAttray (9/3/2009)
Though I selected " 0 " as answer but still not sure about @@error.


What is not clear?

John Moreno
Chad Crawford
 Chad Crawford
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6023 Visits: 18732
I'm going to guess that this happens because sp_rename has a RETURN 1 right after the RAISERROR to break out of the procedure and the RETURN succeeds, resetting the value of @@ERROR before the procedure completes.
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