SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure Naming Conventions


Stored Procedure Naming Conventions

Author
Message
IsaacGoGo
IsaacGoGo
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 51
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jsack/storedprocedurenamingconventions.asp



Aldex
Aldex
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 148

Whilst I agree with the general tenor of the article I have found that the suggested naming convention can become unwieldy when there are a large number of stored procedures in a database (say more than a couple of hundred).

In this situation it becomes difficult to locate particular stored procedures, especially if they are doing something out of the ordinary. Hence for larger databases we now use a naming convention with a prefix which defines the area that the stored procedure relates to (for example Stk... for a stock control section, Del... for a delivery section, Enq... for enquiries, etc.). The remainder of the stored procedure's name can then either continue as suggested in the article or as per some other convention.

Regards,

David Saville
Aldex Software Ltd.
www.aldex.co.uk





ian.stone
ian.stone
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 5
Prefixing stored procedure names adds clutter and makes finding thim in Query analiser difficult. After all SELECT * FROM sysobjects WHERE xtype='P' get them all!



Tatsu
Tatsu
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1162 Visits: 307

If you need to divide the procedures into different functional areas and you're using SQL 2005 then you might want to look at using schemas; conventional best practice prior to 2005 says that all objects should be owned by dbo so you probably don't want to head in that direction.

I agree with the concept of naming procedures with the predominant table or view being accessed and describing the action being performed but I would reverse the order of the two. Having the table/view name first makes it easier to identify the procedures that need to change when the table/view is changed since they will all sort together.

As Ian said, I would also throw out the "usp_" prefix; it's just noise and doesn't pose a problem unless you have a table or view called "sp" (unlikely).

This was a good, simple presentation of a common topic. It would be refreshing to have someone write an article that looked at several naming schemes and compared them. I am partial to mine but would be open to other suggestions. I have promised an article or two already and not delivered so I'm not going too far out on a limb here



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
sushila
sushila
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6017 Visits: 639
Nice article Joe...while this may seem very basic to some, there are developers out there who don't work within the confines of defined standards...

I find it easy to organise my database objects if I can order them by name and I have my own naming convention (whatever that may be)...the important thing is to have a convention and consistently stick to it!

The other protocol I strictly adhere to is naming my variables the same as the column names in use - so if I have a column called BudgetPK my variable is @BudgetPK - while this may seem "doh!!! Talk about stating the obvious!!!" to some, I've seen heaps of procedures where the developers don't follow this "rule"!







**ASCII stupid question, get a stupid ANSI !!!**
Aldex
Aldex
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 148

I agree with Ian and Bryant regarding using a prefix such as usp_ to define a stored procedure. I always use object type prefixes where there is potential for ambiguity, so for example I would still differentiate Tables and Views (eg tblCustomers or vwCustomers). However a Stored Procedure cannot readily be confused with any other object so the rationale for an object type prefix dissapears.

I also note that Microsoft are moving away from object type prefixes in, for example, the later versions of Visual Studio. But maybe this is because there are now so many objects that a simple prefix is not enough to disambiguate them (with thanks to Ken Getz who introduced me to this 'word' during one of his forays to the UK) :

Regards,

David Saville
Aldex Software Ltd.
www.aldex.co.uk





IsaacGoGo
IsaacGoGo
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 51

Hi,

I figured this article would get some discussion! I like the usp_ convention - but I know of those who do not. 100 DBAs = 100 Naming conventions. That was why I was a bit afraid to write this article.

In any case, walking into a new client's site - I'm just happy if they are actually following any convention in the first place (and if they are sticking to it). Usually I find that this is almost never the case.

Best Regards,

Joe





P Jones
P Jones
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2080 Visits: 1522
It's easy to have a convention and stick to it yourself (closely anyway). What's really difficult is getting others to use it!
sushila
sushila
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6017 Visits: 639
Couldn't agree more...the more hands used, the further things get away from conventions...after many hands and much time nobody'd ever guess that it started out with any standards at all..."too many cooks..." and each with their own recipes..







**ASCII stupid question, get a stupid ANSI !!!**
Roger Cogswell
Roger Cogswell
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 3
Without going out on a limb, I concur with Bryant's appeal for an article comparing various naming conventions. We have a team that is currently in the throws of a naming conventions discussion and we could certainly use an outside arbiter!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search