Re: SP_ in Stored Procedure Name

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Prabhat (not_a_mail_at_hotmail.com)
Date: 05/20/04 

Date: Thu, 20 May 2004 12:33:37 +0530

Hi Hari & Wayne,

Thanks.
Prabhat

"Wayne Snyder" <wsnyder@computeredservices.com> wrote in message
news:uJTXmYZPEHA.3300@TK2MSFTNGP09.phx.gbl...
> As Hari says, you are correct. We should never name OUR procs sp_, because
> master will be searched first, and the sp will not be found, then it will
> look in our db.... This forces a failed SP lookup for each call which is
> unnecessary.
>
> Additionally it is confusing, because as a convention (most) all system
> procs are named like that and those who intend to maintain them might also
> look in master first... You might name your SPs something like p_
>
> or anything else.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
>
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
>
> "Prabhat" <not_a_mail@hotmail.com> wrote in message
> news:OptyhhYPEHA.1512@TK2MSFTNGP10.phx.gbl...
> > Resending the Post Again as After one day of Posting It did not appear
in
> > newsgroup.
> >
> > Hi All,
> >
> > I have one doubt. If I have given my Stored Procedure name begin with
the
> > name "SP_" then when I Try to execute the Stored Procedure then Will the
> SQL
> > Server will serach for that Stored Procedure first in MASTER database
and
> > then the Current Database?
> >
> > If That is the case. Then Why SQl Server search for the Stored procedure
> in
> > Master first?
> >
> > Thanks
> > Prabhat
> >
> >
> >
>
>

Relevant Pages

  • Re: SP_ in Stored Procedure Name
    ... master will be searched first, and the sp will not be found, then it will ... look in master first... ... Wayne Snyder, MCDBA, SQL Server MVP ... If I have given my Stored Procedure name begin with the ...
    (microsoft.public.sqlserver.programming)
  • Re: Please answer my queries for fresh Installation
    ... You can restore MSDB as well as master. ... > SQL Server, It will create Master, MSDB databases. ...
    (microsoft.public.sqlserver.server)
  • Re: unable to start SQL Service ... error code 3417
    ... If the Master database is corrupt, how would you advise I maybe replace with ... "SQL Server cannot start the master database. ... MVP - Windows Live Platform ...
    (microsoft.public.sqlserver.server)
  • Re: Master Pages not displaying ... ?
    ... If you have the above line in your child page then it won't display ... from the master page because there is nothing to display. ... Microsoft SQL Server Analysis Services Designer ... Microsoft SQL Server Integration Services Designer ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: unable to start SQL Service ... error code 3417
    ... How about just create a new instance and restore databases to the new instance? ... I wouldn't go with restoring master from another instance. ... The supported route is to rebuild your master database, start SQL Server in single user mode, ...
    (microsoft.public.sqlserver.server)