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 Monday, October 3, 2005 8:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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.
Post #225536
Posted Monday, October 3, 2005 9:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 8:17 AM
Points: 297, Visits: 335

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
Post #225540
Posted Monday, October 3, 2005 9:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2013 8:00 AM
Points: 6, Visits: 17
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




Post #225548
Posted Monday, October 3, 2005 9:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:01 PM
Points: 295, Visits: 287

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.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #225561
Posted Monday, October 3, 2005 10:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 17, 2006 10:06 AM
Points: 28, Visits: 1

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.

Post #225568
Posted Monday, October 3, 2005 10:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:05 AM
Points: 1,070, Visits: 913

So whats so extreme about conventions for naming of 'system created constraints'?

It's damnably hard work to issue database upgrade scripts from Dev --> QA --> Live without having standard, explicit constraint names...




Post #225573
Posted Monday, October 3, 2005 11:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:16 AM
Points: 24, Visits: 51

Wangkhar,

With the correct scripting or migration tools, explicit constraint names aren't necessary.  I don't object to constraint naming conventions - but I find that for a team of developers and dbas, they are difficult to enforce.  I try to focus on tables, views, stored procedures, and other major object types instead.  If you have the environment control to keep constraint-level names standardized - I envy you.

 

 




Post #225597
Posted Monday, October 3, 2005 11:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 16, 2008 10:53 AM
Points: 10, Visits: 4

I know it's almost universal to have Verb-first, then the Object as a naming convention:

  • Upd_Users
  • Upd_Inventory
  • Upd_Vendors

But I use the reverse -- Object-first, then Verb -- for a specific reason

  • Users_Upd
  • Inventory_Upd
  • Vendors_Upd

The reason is that when they are listed alphabetically (as they are everywhere) I want to see all of the User-oriented sprocs together, then all of the Inventory-oriented sprocs together, etc.; rather than all of the Delete sprocs together, then all of the Insert sprocs together, etc.

 




Post #225606
Posted Monday, October 3, 2005 11:43 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
As Sushila said it may be obvious to some that it is sensible to move column names into variables of the same name prefixed by @ but I have seen yards of off-shore code which does not follow this convention. Also in the interest of laziness tables like Purchase_order_header (I wonder what that might be?) are called something like Purordhd. A good example of this is Sage, a leading UK accountancy package. It has table names like stockm uses tables as if they were flat files and does not have any constraints. There is also ignorance about the use of triggers, functions and transaction processing.


Post #225607
Posted Monday, October 3, 2005 12:17 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 12, 2007 6:32 AM
Points: 62, Visits: 3

The prefix is helpful, but only in certain conditions.

If you're using the Query Analyzer object viewer or using Enterprise Manager to browse for your stored procedures, then a prefix probably won't help you much.  I don't think it would make it more difficult either. 

Remember that the naming convention for stored procs is for more than just the DBA though.  Developers use these stored procs and in a complex script with many stored procs, tables, views, functions, and other objects, it takes out the ambiguity when looking at a script.  It's especially helpful if you're looking at the script via a text editor, Visual Studio, or something else where you don't have the full support of Query Analyzer or the ability to quickly run a SQL statement every time you see an object name and can't remember what it is.  It won't make or break you, it just makes development easier if you can limit the guesswork.

Good article though.  I'm a big supporter of naming conventions.  I have used several different ones at various client sites and the ones who have a standard and follow it are much easier to support than those who don't -- irregardless of whether they include or discard the prefix.

 

Post #225615
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse