sproc best pratices

[jcisco2]

I have a question about SPROC best practices. I have read that you should never name your SPROCS with a sp_ as stated:

"Do not prefix your stored procedure names with "sp_". The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. So you can really save time in locating the stored procedure by avoiding the "sp_" prefix."

now then I have tried to experiment with this statement by opening up my sql server enterpise manager, and creating two identical Sprocs. One located in my northwind database the other in master database. Also located in both databases i have a test table called test that has one col that takes in a int called testC1

SPROC code:
CREATE procedure sp_TEST
as
insert into test (testC1) values (1)
exec sp_test2
GO

create procedure sp_TEST2
as
insert into test (testC1) values (2)
GO

now then.. in my query analyzer i went to the northwind database and fired my sproc with the following code: exec sp_Test
Now then my question is in order for the above statement to ring true this statement should set off the sproc not in my northwind database but my master table.

which it does not..

It does fire the correct sproc and it writes the results to the correct test table in my northwind, not my master test table. I thought that this might have been caused by me pointing at the northwind in my query analyzer thats why i have the second sproc exec inside of the sp_Test. The second sproc also fires the sproc located in my northwind not my master (when using my northwind connection). am I not seening the logic in the above statement that using sp_ is a bad process.

Last question is it a better pratice to write all sql queries in the following manner:
select CompanyName from Northwind..Customers where customerID = 'ALFKI'

cheers.

 

[checkai]

We always name our stored procedures with the application name first....something like this..it makes it very easy to keep track of things that your using throughout your development environment.


ClientAcceptance_SPAddClient

report_SPBillingSummary

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[NoCoolHandle]

In answer to the first q of... if you have an sp_ in the master and a local db both having the same name, which will fire first.

Always the Local - Never believe everthing you read, always test everything.

Regarding name conventions... here we tend to do

procWhatEverItIsDoing