Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Stored Procedure Naming Conventions Expand / Collapse
Author
Message
Posted Thursday, August 25, 2005 2:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:16 AM
Points: 24, Visits: 51
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jsack/storedprocedurenamingconventions.asp


Post #214097
Posted Monday, October 03, 2005 3:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 4:18 AM
Points: 80, Visits: 132

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




Post #225431
Posted Monday, October 03, 2005 6:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 20, 2008 3:40 AM
Points: 5, 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!


Post #225443
Posted Monday, October 03, 2005 6:25 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 292, Visits: 258

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
Post #225447
Posted Monday, October 03, 2005 6:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:05 AM
Points: 2,553, Visits: 559
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 !!!**
Post #225451
Posted Monday, October 03, 2005 6:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 4:18 AM
Points: 80, Visits: 132

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

 




Post #225453
Posted Monday, October 03, 2005 6:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:16 AM
Points: 24, 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




Post #225458
Posted Monday, October 03, 2005 7:52 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:20 AM
Points: 547, Visits: 1,126
It's easy to have a convention and stick to it yourself (closely anyway). What's really difficult is getting others to use it!
Post #225474
Posted Monday, October 03, 2005 7:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:05 AM
Points: 2,553, Visits: 559
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 !!!**
Post #225475
Posted Monday, October 03, 2005 8:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 20, 2011 9:17 AM
Points: 4, 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! 
Post #225512
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse