Naming Convention of Stored Procedures

Question

Naming Convention of Stored Procedures

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

I don't pre or postfix tables as a rule, however I do like to label views for the same reasons Steve said above. As for procedures I usually try to stick to something akin to the scheme below:

p_object_action

The object is mapped to a screen, physical object or concept within the application... so you might end up with something like p_Order_Insert, p_CustomerRecord_Display or p_DailySalesData_Pivot, for example. The object in the middle can have modifiers attached to it when it needs more specifics, (i.e. p_Quota_Distributors_Validate, p_Quota_SalesReps_Validate, etc...).

It's a bit of a pain, but when you're maintaining your code later and need to add some new functionality you can quickly identify all the procedures which are utilized by sorting them alphabetically. When we get a ticket to modify a particular screen or function within an application we can usually pretty quickly identify what procedures need to be addressed even if nobody has touched that area in months.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 2

Hmmm... and what do you do for sprocs that are used by multiple applications?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110206 More actions · Answer 3

Jeff Moden (12/1/2007)
Hmmm... and what do you do for sprocs that are used by multiple applications?

Well, procedure is doing the same thing to the same object (or, better, "object").

If this object gas another name in another application then you need just create kind of "mapping document" for object names.

I just don't see any point of that prefix "p_".

If you want to see that an object is a procedure from it's name then action part in the name will tell you that.

_____________
Code for TallyGenerator

Steve Jones - SSC Editor SSC Guru Points: 734522 More actions · Answer 4

Actually I tend to postfix so I can group similar functions together. I want the

uspSalesGetforQuarter

right next to the

uspSalesGetAllforSalesman

As opposed to

uspGetAllSalesforSalesman

uspGetAllProductsforCustomer

...

uspGetSalesforQuarter

I've found that it's pretty easy to identify the major object/schema that you're accessing and make that the first item so all related procedures are near each other.

I admit there are flaws and as things grow, you have to make compromises, but you still name based on function, not application. I agree with Sergiy here that the same proc used by different apps is still doing the same function.

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 5

In the case that it's used by multiple applications we usually generalize the object portion a bit. It's really evaluated on an issue by issue basis.

The prefix p_ is a holdover from another job where I didn't make these decisions (and old habits die hard), however when you see an action word it could also indicate a function. It's obvious which is which when you see how they're used of course, but sometimes when it's listed in documentation or something this can help to eliminate confusion.

It's not a perfect system but it's worked for us so far.

MikeP-477808 SSC Rookie Points: 47 More actions · Answer 6

One way I have managed to convince "we've always done it this way" types (or their bosses) is to show them the MS SQL Best Practices Analyzer.

Be warned though, this will probablty uncover many more problems with your databases than the sp_ prefixes and guess whose job it will be to fix them?

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 7

Every new version of SQL Server introduces new reserved words, so using prefixes for all types of objects (including tables) will ensure that Microsoft will not break your application in their next version.

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Going back thou here is an older article I wrote on the whole "sp_" thing

What is the search order for Procedures prefixed sp_?[/url]

But anyway, I have have to say without regard to what you choose as a Naming Convention it should be documented and understood as to the convention or eventually the whole thing will fall into chaos. If you choose to prefix or postfix then make sure everyone follows the same rules.

Now as for the comment about naming things so they can be readily identified, the idea that code (or in this case a database) should be self documenting is a poor practice. Documentation should always exist and be maintaned to ensure one persons meaning is not lost on another. But there are simple ways to look at every system to determine exactly what an object is without a prefix/postfix of any kind.

And then the last arguement about changes to the server by new reserved keywords and other types of changes. Well I choose to adopt a style of the

ISO/IEC 11179 Information technology — Metadata

registries (MDR)

from

Part 5: Naming and identification principles

this naming convention style works very well especially becaue I have yet to see many reserved keywords which are plural in format. But even if you prefix/postfix you table name you can still conflict with reserved keywords elsewhere. It is very common for people to use Name instead of breaking into component pieces such as Given_Name/First_Name and Surname/Last_Name.

But again, even thou you will see many differences of opinion on this and many people will have potentially valid reasons for thier choice, it is your choice. Set a naming convention and stick with it across the board, document throughly the method and the system, and above all else let noone stray unless they can provide a ligitimate reason other than they feel it is the better way. I gave my reasons for the name in managing things, I don't prefix any objects (but I used to) and I feel my designs have been far more flexible and easier to understand becuase of it. And when I find an issue in the design I can alter many things without breaking the app side until I either have a chance to or a specific need to, usually can wait until the next release cycle thou.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 9

Sergiy (12/1/2007)
Jeff Moden (12/1/2007)
Hmmm... and what do you do for sprocs that are used by multiple applications?
Well, procedure is doing the same thing to the same object (or, better, "object").
If this object gas another name in another application then you need just create kind of "mapping document" for object names.
I just don't see any point of that prefix "p_".
If you want to see that an object is a procedure from it's name then action part in the name will tell you that.

Concur... my point was the proc should be named after what it does, not which application uses it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)