Re: using 'sp_' as a naming convention for stored procedures

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 03/14/04 

Date: Sun, 14 Mar 2004 18:35:46 -0500

>From the BOL "Creating a Stored Procedure":

System Stored Procedures
Many of your administrative activities in Microsoft® SQL ServerT 2000 are
performed through a special kind of procedure known as a system stored
procedure. System stored procedures are created and stored in the master
database and have the sp_ prefix. System stored procedures can be executed
from any database without having to qualify the stored procedure name fully
using the database name master.

It is strongly recommended that you do not create any stored procedures
using sp_ as a prefix. SQL Server always looks for a stored procedure
beginning with sp_ in this order:

  1.. The stored procedure in the master database.

  2.. The stored procedure based on any qualifiers provided (database name
or owner).

  3.. The stored procedure using dbo as the owner, if one is not specified.
Therefore, although the user-created stored procedure prefixed with sp_ may
exist in the current database, the master database is always checked first,
even if the stored procedure is qualified with the database name.

Important If any user-created stored procedure has the same name as a
system stored procedure, the user-created stored procedure will never be
executed. 

-- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com/sql
.
"Scott" <anonymous@discussions.microsoft.com> wrote in message
news:cda301c40a19$103893e0$a301280a@phx.gbl...
I know that using 'sp_' as a naming convention for stored
procedures is not a good idea because MS uses that naming
for system stored procedures.   I am working on a system
where the original developer had all of the stored
procedures named "sp_" then the name.   I am being told
that this is causing a problem with the server because of
it.   The DBA group told me that when using "sp_" as a
name for a stored procedure it automatically causes the
call to go to the master DB to look it up.  I have never
heard of this before.
Has anyone else?
Additional information:
In my connection string I specify the DB name.
I do not reference any tables outside of the DB the sproc
resides in.
Any feedback would be appreciated.
thanks
Scott
http://support.microsoft.com/default.aspx?scid=kb;en-
us;159901
The "Transact-SQL Reference," under the User-defined
System Stored Procedures heading, states:
When executed, a stored procedure is typically found
within the current database. If the procedure name begins
with sp_ and is not found in the current database, SQL
Server will look in the master database.
This statement incorrectly implies that you can create
user-defined system stored procedures outside of the
master database. The statement in Example H of the CREATE
PROCEDURE Examples section is correct: "user-defined
system stored procedures...must be created by the system
administrator in the master database."

important.gif

Relevant Pages

  • Re: Unable to view system stored procedures from .NET IDE and unable to debug SQL
    ... But what is the reason for the debug problem I mentioned below? ... try to step into a stored procedure in the Northwind database, ... Run SQL server setup or contact database ... I can't see any system stored procedures in master. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Identical database w/ identical stored procedures?
    ... > creating the maintence nightmare of updating the same stored procedure ... First of all, put your source code, tables, stored procedures and all ... This can be achieved with a help table in the database. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Does nesting stored procedures make sense to increase performance?
    ... Reducing client or middle-tier round trips to the database is a good idea in that it will reduce network traffic. ... In SQL Server 2000, because recompiles of query plans are done on a per-procedure basis, it's helpful some execute one SQL statement per procedure as well. ... If you're going to combine many calls into a single call, you'll need to add robust error handling to the "driver" procedure so as not to call procedures 2,3, and 4 if procedure 1 fails, etc. You'd need to replicate your existing client/middle-tier code's concept of success/failure results quit the batch of procedures on a failure and the return the results/return code that the client/middle-tier sees in the original. ... I've recently became aware of nesting stored procedures in SQL Server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Debugging in VS.NET
    ... > "Cannot debug stored procedures because the SQL Server database is not ...
    (microsoft.public.sqlserver.msde)
  • Re: Query Analyzer Connect Option
    ... the database, but the users only have SELECT access. ... through stored procedures. ... As Mary pointed out, giving users full access to the database, and ... Books Online for SQL Server SP3 at ...
    (microsoft.public.sqlserver.tools)