Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure Naming Conventions


Stored Procedure Naming Conventions

Author
Message
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168
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.
BobAtDBS
BobAtDBS
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 357

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
Mark Sandy
Mark Sandy
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: 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
Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307

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
AndreQ1
AndreQ1
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
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.


RichB
RichB
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1102 Visits: 1015

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...





IsaacGoGo
IsaacGoGo
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
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.





jettand
jettand
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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.





ian.stone
ian.stone
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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.



JT Lovell
JT Lovell
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
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.


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