First off full disclosure, I got this wrong as well as I had just forgotten since I don't prefix with sp_

OK, I think a bit of clarification on the SQL BOL thing is needed here. There should be several articles on this site even about it. If you use SP_ to prefix your procedure the issue was that it does in fact search for it in Master first (the search mechanism actually looks for any object named SP_ in master first, table, view, function, whatever is prefixed SP_ see example for more fun to demonstrate). This was to ensure procedures in Master were available to all databases without having to set database to master or even using the 3 part name. However, it does not execute until it has also searched the database you are currently in. If there is an object of the same name in the local database and you are not in master the code of the local procedure is executed. You however cannot create a local database version of a MS System Stored Procedure and override the behavior for that database. The procedure mentioned sys.sp_ms_marksystemobject to mark the object as a system stored proc can flag it as such but there is something else I forget right now in the flags that makes it the choice over anything else, may even be directly in the database engine but again I forget.

The real downside of using SP_ is that the process to find the object for use makes the extra trip across Master which means you take a hit on performance for the extra work, especially if used very often. Also, because SP_ is global from master in the search precedence it will as I recall cause a cache miss every time on the procedure in question, but I don't have an example to share to show that right off. So the big key is the hit to performance you will take by using SP_ not that it changes behavior.

/* Demostrates condition using a view prefixed SP_ */

use master

go

create view sp_one

as

select 'this' as Col1

go

use testDB

go

select * from sp_one --notice no reference to master

go

create view sp_one

as

select 'that' as Col1

go

select * from sp_one

go

drop view sp_one

go

drop view sp_one

go

/* Demostrates condition using a view prefixed SP_ in master then added proc in Local DB */

use master

go

create view sp_one

as

select 'this' as Col1

go

use testDB

go

select * from sp_one --notice no reference to master

go

create proc sp_one

as

select 'that' as Col1

go

select * from sp_one --now throws an error due to local object named sp_one

go

drop proc sp_one

go

drop view sp_one

go