What's the best practice of naming stored procedure for t-sql?

Question

I have worked with several big databases and the names of stored procedures were very different:

SP_PrefixXXX
PrefixYyyXxx
Prefix: Rep, Act

What's the best practice of naming? How can I organize them in a proper way?

Answer 1

The best naming convention is the one that is consistent throughout your database :)

Really, it's up to you and your team. So long as it's clear and sensible, you have a fair bit of leeway. Just make sure that whatever you decide on, everyone adheres to it. Far more important than the convention itself is the fact that everyone sticks with it.

I tend to avoid sp_, usp_ and the like, because I find them redundant. For instance, a sproc called InsertCustomer is clearly a sproc, and in no way could be confused for a table, view, or any other sort of object. sp_ in particular should be avoided.

I prefer CamelCase, but again, that's a matter of preference. I like my proc name to give a good indication of what the proc does - for instance:

InsertSalesOrder PopulateItemStagingTables CalculateOrderSummary PrepareCustomerStatements

etc.

Answer 2

Well, prefixing the name with "SP_" is pretty much redundant: it's naming for the implementation (it's an SP, as opposed to a table or a view). There are plenty of other ways (systebales, information_schema, how you use it) that will tell you hw it's implemented.

Instead you should name it for its interface, for what it does for you. For convenience (as many things end up ordered alphabetically), I like to group like things under like names, possibly using the same prefix.

But again, name it for what it does, not how it happens to be implemented.

In general, I find that the common naming conventions for database objects use underscores instead of CamelCase; this is just a matter of convention. What is not mere convention is the also common convention of using all lowercase letters for database objects; this allows you to ignore server settings which may or may not be case-insensitive.

Answer 3

Not "sp_", nor "usp_". We know they're stored procedures, the naming scheme doesn't need to tell us.

I generally just name them for what they do, possibly partitioning them on schemas. The exceptions are that I will use an "ssis_" prefix for stored procedures which aren't directly used as part of the "normal" database operations, but which are used by an SSIS package to reference the database. I mayuse "fn_" to indicate a function, to distinguish it from a stored procedure.

Answer 4

I tend to try to give names that not only give an idea what the function is for, but what the input variables will be.

For example: ProcessMathEquationWithFieldIdPlantId

This will help give information immediately to anyone else using it, I believe.

I also avoid sp_ and usp_ to limit any chance of name collisions.

Answer 5

The sp_ prefix stands for System Procedure, and it should not be used as prefix for regular procedures. If you do, it will first make an extra trip to the master database each time to look for the procedure, and if it would have the same name as one of the procedures there, that procedure will be executed instead of your procedure.

Other than that, you are free to make up any naming convention you like. One used by our company is subsystem_object_action, e.g. main_Customer_Get. That puts procedures that belong together close to each other in the listing.

Answer 6

I like prefixing them so SP's dealing with specific objects are grouped together. So instead of something like:

    InsertUser
    UpdateUser
    DeleteUser
    GetUsers

I do it like this:

    AppName_User_GetUser
    AppName_User_InsertUser
    AppName_User_UpdateUser
    AppName_User_DeleteUser

I find this is easier for me to manage in my SQL management app and in my code too.

Like the other folks said, don't prefix with sp_

Answer 7

I don't know if there really is a specific 'best practice' in this case. With the company I am at now, the standard is usp[ProcedureName] (no underscore). I would personally prefer no prefix at all, but if you are coming in new to a company or project and they have pre-existing standards, unless they are using sp_ where there is a technical reason not to use this, it is probably not an issue worth debating as I certainly don't think it is in this case at all an egregious standard.

Generally re naming conventions, if you do have a debate and other team members disagree with you and the consensus standard is different, the best policy is to quickly let it go and accept the consensus; consistency is generally more important than the actual standard itself, as is getting along well with other team members and not developing a reputation for being 'difficult'.

Answer 8

A very common question. Canned answer:

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx

Answer 9

Is it ok for sprocs to have long names, like GetTradesForInvestorsWithoutCash?