Stored Procedure Naming Conventions
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jsack/storedprocedurenamingconventions.asp
-
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.
-
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!
-
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
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
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 !!!** -
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.
-
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
-
It's easy to have a convention and stick to it yourself (closely anyway). What's really difficult is getting others to use it!
-
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 !!!** -
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!
-
If you want to "appeal to authority", check out Celko's SQL Style book. He gives a well-rounded discussion of his interpretation of ISO naming convention standards.
-
I tend to dislike the prefix concept. Stored Procedures are all grouped together anyway. We all know not to use SP_, so the lack of that prefix is a standard in itself.
I like the rest of the idea and carry it a bit further. I want the name of the SP to tell me not only what table(s) it's accessing but what it's purpose is. That gives me clues as to whether I can extend it or use it elsewhere.
A SP named GetSalesReportInfoBySalesRep tells me a lot more than usp_Sel_Sales. In the second case, can I use the SP elsewhere in my program without expecting it to get modified later? Can I add columns to the report without breaking things?
I also comment the SP pretty clearly on the inside. I put it's complete purpose as designed and even a date and where used, although I know that will be out of date and incomplete eventually.
I guess I name SPs the way I name variables. Long, descriptive and as specific as possible. In VB, where my front ends are almost always written, long doesn't matter since IntelliSense always fills in the blanks anyway. (Well, not for SPs but for a lot of things.)
Student of SQL and Golf, Master of Neither -
I use a slightly different approach (despite of the usp_)
>> Module_Object_Action
I can divide my stored procedures to operate in different parts of the DB (CRM, Statistics, Reporting etc.). That's a module (call it whatever you want)
Then, I have objects. This includes all tables and views I'm operating on, but can also be something abstract like sales, inventory or so.
Finally, I would have actions operation on the objects. I would use 'Edit','Delete','Insert','Generate','Calculate' or whatever.
Now, if I sort the SP in the Enterprise mangager, I have them nicely grouped by module, object and action. procedures which operate on the same object in the context of a module are listed together. That's exactly what I need.
Examples are:
CRM_Customer_Append
CRM_Customer_Delete
CRM_Customer_Modify
Stats_Sales_Report
Stats_Customer_Listing
Stats_UnfinishedOrders_Listing
-
I can divide my stored procedures to operate in different parts of the DB (CRM, Statistics, Reporting etc.). That's a module (call it whatever you want) - Mark Sandy
This is where some of the confusion comes in with naming conventions. Mark's standard is great for him as an application developer since he is concerned with the individual modules and the stored procedures that go with them. What about the database developer though? Or even another application working on a different module that needs to make a change to the Customer table? It's much more difficult for these folks to identify the procedures they need to touch to make sure the impact of their change is minimized.
Is there a naming convention that covers all situations? Probably not.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
We use Mark Sandy's approach as well. Many of our stored procs deal with one table, and so the Module_Table_Action has worked EXTREMELY well. If you want to see which procs are acting on a table, its very easy to find.
Even better, when training someone else on the architecture, it takes all of 5 minutes for them to comprehend - the ultimate in sustainability.
Since I also code on the client side, its made that part simpler as well.
The last thing I'll mention is that the unique identifier for a table I always make the table name + "ID", e.g., CustomerID for the Customer table. Makes running queries by string concatenation really useful. Once I know my table name, I know that the proc to retrieve information is RPO_[TableName]_GET, based off of ID [TableName]ID.
I can't say enough good things about this technique.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply