This is what i did
EXECUTE sp_rename N'fakename', N'fakename2', 'OBJECT'
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
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!