1) Master DB,
2) The stored procedure based on the fully qualified name provided,
3) The stored procedure using dbo as the owner, if one is not specified.
So, when you have the SP with the prefix "sp_" in the DB other than master, the Master DB is always checked first, and if the user-created SP has the same name as a system stored proc, the user-created stored procedure will never be executed.
For example, Let's say that by mistake you have named one of your user defined stored procedure as "sp_help" within the database!
Create proc sp_help
Select * from dbo.empdetails
Now when you try executing the stored procedure using the below script you would realize that it has actually called the sp_help system stored proc of Master DB and not your SP.
Hope this helps!!
Technorati tags: SQL, Databases, SQL Server, SQL Server 2005