What is your naming convention for stored procedures?

Question

I have seen various rules for naming stored procedures.

Some people prefix the sproc name with usp_, others with an abbreviation for the app name, and still others with an owner name. You shouldn't use sp_ in SQL Server unless you really mean it.

Some start the proc name with a verb (Get, Add, Save, Remove). Others emphasize the entity name(s).

On a database with hundreds of sprocs, it can be very hard to scroll around and find a suitable sproc when you think one already exists. Naming conventions can make locating a sproc easier.

Do you use a naming convention? Please describe it, and explain why you prefer it over other choices.

Summary of replies:

• Everybody seems to advocate consistency of naming, that it might be more important for everyone to use the same naming convention than which particular one is used.
• Prefixes: While a lot of folks use usp_ or something similar (but rarely sp_), many others use database or app name. One clever DBA uses gen, rpt and tsk to distinguish general CRUD sprocs from those used for reporting or tasks.
• Verb + Noun seems to be slightly more popular than Noun + Verb. Some people use the SQL keywords (Select, Insert, Update, Delete) for the verbs, while others use non-SQL verbs (or abbreviations for them) like Get and Add. Some distinguish between singluar and plural nouns to indicate whether one or many records are being retrieved.
• An additional phrase is suggested at the end, where appropriate. GetCustomerById, GetCustomerBySaleDate.
• Some people use underscores between the name segments, and some avoid underscores. app_ Get_Customer vs. appGetCustomer -- I guess it's a matter of readability.
• Large collections of sprocs can be segregated into Oracle packages or Management Studio (SQL Server) solutions and projects, or SQL Server schemas.
• Inscrutable abbreviations should be avoided.

Why I choose the answer I did: There are SO many good responses. Thank you all! As you can see, it would be very hard to choose just one. The one I chose resonated with me. I have followed the same path he describes -- trying to use Verb + Noun and then not being able to find all of the sprocs that apply to Customer.

Being able to locate an existing sproc, or to determine if one even exists, is very important. Serious problems can arise if someone inadvertently creates a duplicate sproc with another name.

Since I generally work on very large apps with hundreds of sprocs, I have a preference for the easiest-to-find naming method. For a smaller app, I might advocate Verb + Noun, as it follows the general coding convention for method names.

He also advocates prefixing with app name instead of the not very useful usp_. As several people pointed out, sometimes the database contains sprocs for multiple apps. So, prefixing with app name helps to segregate the sprocs AND helps DBAs and others to determine which app the sproc is used for.

Answer 1

I always encapsulate the stored procedures in packages (I'm using Oracle, at work). That will reduce the number of separate objects and help code reuse.

The naming convention is a matter of taste and something you should agree with all the other developers at project start.

Answer 2

for small databases, i use uspTableNameOperationName, e.g. uspCustomerCreate, uspCustomerDelete, etc. This facilitates grouping by 'main' entity.

for larger databases, add a schema or subsystem name, e.g. Receiving, Purchasing, etc. to keep them grouped together (since sql server likes to display them alphabetically)

i try to avoid abbreviations in the names, for clarity (and new people on the project don't have to wonder what 'UNAICFE' stands for because the sproc is named uspUsingNoAbbreviationsIncreasesClarityForEveryone)

Answer 3

I think the usp_ naming convention does nobody any good.

In the past, I've used Get/Update/Insert/Delete prefixes for CRUD operations, but now since I use Linq to SQL or the EF to do most of my CRUD work, these are entirely gone. Since I have so few stored procs in my new applications, the naming conventions no longer matter like they used to ;-)

Answer 4

For the current, application I am working on, we have a prefix that identifies the application name (four lowercase letters). The reason for this is that our application must be able to co-exist with a legacy application in the same database, so the prefix is a must.

If we did not have the legacy constraint, I am quite sure that we would not be using a prefix.

After the prefix we usually start the SP name with a verb that describes what the procedure does, and then the name of the entity that we operate on. Pluralization of the entity name is allowed - We try to emphasize readability, so that it is obvious what the procedure does from the name alone.

Typical stored procedure names on our team would be:

shopGetCategories
shopUpdateItem

Answer 5

Systems Hungarian (like the above "usp" prefix) makes me shudder.

We share many stored procedures across different, similarly-structured databases, so for database-specific ones, we use a prefix of the database name itself; shared procedures have no prefix. I suppose using different schemas might be an alternative to get rid of such somewhat ugly prefixes altogether.

The actual name after the prefix is hardly different from function naming: typically a verb like "Add", "Set", "Generate", "Calculate", "Delete", etc., followed by several more specific nouns such as "User", "DailyRevenues", and so on.

Responding to Ant's comment:

1. The difference between a table and a view is relevant to those who design the database schema, not those who access or modify its contents. In the rare case of needing schema specifics, it's easy enough to find. For the casual SELECT query, it is irrelevant. In fact, I regard being able to treat tables and views the same as a big advantage.
2. Unlike with functions and stored procedures, the name of a table or view is unlikely to start with a verb, or be anything but one or more nouns.
3. A function requires the schema prefix to be called. In fact, the call syntax (that we use, anyway) is very different between a function and a stored procedure. But even if it weren't, the same as 1. would apply: if I can treat functions and stored procedures the same, why shouldn't I?

Answer 6

I don't think it really matters precisely what your prefix is so long as you're logical and consistent. Personally I use

spu_[action description][process description]

where action description is one of a small range of typical actions such as get, set, archive, insert, delete etc. The process description is something short but descriptive, for example

spu_archiveCollectionData 

or

spu_setAwardStatus

I name my functions similarly, but prefix with udf_

I have seen people attempt to use pseudo-Hungarian notation for procedure naming, which in my opinion hides more than it reveals. So long as when I list my procedures alphabetically I can see them grouped by functionality then for me that seems to be the sweet spot between order and unnecessary rigour

Answer 7

sp_ is bad in SQL Server because the system sprocs all start with sp_. Consistent naming (even to the extent of hobgoblin-dom) is useful because it facilititates automated tasks based on the data dictionary. Prefixes are slightly less useful in SQL Server 2005 as it supports schemas, which can be used for various types of namespaces in the way that prefixes on names used to. For example, on a star schema, one could have dim and fact schemas and refer to tables by this convention.

For stored procedures, prefixing is useful for the purpose of indentifying application sprocs from system sprocs. up_ vs. sp_ makes it relatively easy to identify non-system stored procedures from the data dictionary.

Answer 8

For my last project i used usp_[Action][Object][Process] so for example, usp_AddProduct or usp_GetProductList, usp_GetProductDetail. However now the database is at 700 procedures plus, it becomes a lot harder to find all procedures on a specific object. For example i now have to search 50 odd Add procedures for the Product add, and 50 odd for the Get etc.

Because of this in my new application I'm planning on grouping procedure names by object, I'm also dropping the usp as I feel it is somewhat redundant, other than to tell me its a procedure, something I can deduct from the name of the procedure itself.

The new format is as follows

[App]_[Object]_[Action][Process]

App_Tags_AddTag
App_Tags_AddTagRelations
App_Product_Add 
App_Product_GetList
App_Product_GetSingle

It helps to group things for easier finding later, especially if there are a large amount of sprocs.

Regarding where more than one object is used, I find that most instances have a primary and secondary object, so the primary object is used in the normal instance, and the secondary is refered to in the process section, for example App_Product_AddAttribute.

Answer 9

I always use:

usp[Table Name][Action][Extra Detail]

Given a table called "tblUser", that gives me:

• uspUserCreate
• uspUserSelect
• uspUserSelectByNetworkID

The procedures are alphabetically sorted by table name and by functionality, so it's easy to see what I can do to any given table. Using the prefix "usp" lets me know what I'm calling if I'm (for example) writing a 1000-line procedure that interacts with other procedures, multiple tables, functions, views and servers.

Until the editor in the SQL Server IDE is as good as Visual Studio I'm keeping the prefixes.

Answer 10

Avoid sp_* in SQl server coz all system stored prcedures begins with sp_ and therefore it becomes more harder for the system to find the object corresponding to the name.

So if you begin with something other than sp_ things become easier.

So we use a common naming of Proc_ to begin with. That makes it easier to identify the procedures if presented with one big schema file.

Apart from that we assign a prefix that identify the function. Like

Proc_Poll_Interface, Proc_Inv_Interface etc.

This allows us to find all stored procs which does the job of POLL vs that does Inventory etc.

Anyhow the prefix system depends on your problem domain. But al said and done something similar ought to be present even if it be just to allow people to quicly locate the stored procedure in the explorere drop down for editing.

other eg's of function.

Proc_Order_Place
Proc_order_Delete
Proc_Order_Retrieve
Proc_Order_History

We followed the function based naming coz Procs are akin to code / function rather than static objects like tables. It doesnt help that Procs might work with more than one table.

If the proc performed more functions than can be handled in a single name, it means your proc is doing way much more than necessary and its time to split them again.

Hope that helps.

Answer 11

Here's some clarification about the sp_ prefix issue in SQL Server.

Stored procedures named with the prefix sp_ are system sprocs stored in the Master database.

If you give your sproc this prefix, SQL Server looks for them in the Master database first, then the context database, thus unnecessarily wasting resources. And, if the user-created sproc has the same name as a system sproc, the user-created sproc won't be executed.

The sp_ prefix indicates that the sproc is accessible from all databases, but that it should be executed in the context of the current database.

Here's a nice explanation, which includes a demo of the performance hit.

Here's another helpful source provided by Ant in a comment.

Answer 12

application prefix_ operation prefix_ description of database objects involved (minus the spaces between underscores - had to put spaces in for them to appear).

operation prefixes we use -

• “get” – returns a recordset
• “ins” – inserts data
• “upd” – updates data
• “del” – deletes data

e.g

wmt_ ins _ customer _details

"workforce management tool, insert details into customer table"

advantages

All stored procedures relating to the same application are grouped together by name. Within the group, stored procedures that carry out the same kind of operation (e.g. inserts, updates, etc.) are grouped together.

This system works well for us, having approx. 1000 stored procedures in one database off the top of my head.

Haven't come across any disadvantages to this approach so far.

Answer 13

I have used pretty much all of the different systems over the years. I finally developed this one, which I continue to use today:

Prefix :

• gen - General: CRUD, mostly
• rpt - Report: self-explanatory
• tsk - Task: usually something with procedural logic, run via scheduled jobs

Action Specifier:

Ins - INSERT
Sel - SELECT
Upd - UPDATE
Del - DELETE

(In cases where the procedure does many things, the overall goal is used to choose the action specifier. For instance, a customer INSERT may require a good deal of prep work, but the overall goal is INSERT, so "Ins" is chosen.

Object:

For gen (CRUD), this is the table or view name being affected. For rpt (Report), this is the short description of the report. For tsk (Task) this is the short description of the task.

Optional Clarifiers:

These are optional bits of information used to enhance the understanding of the procedure. Examples include "By", "For", etc.

Format:

[Prefix][Action Specifier][Entity][Optional Clarifiers]

Examples of procedure names:

genInsOrderHeader

genSelCustomerByCustomerID
genSelCustomersBySaleDate

genUpdCommentText

genDelOrderDetailLine

rptSelCustomersByState
rptSelPaymentsByYear

tskQueueAccountsForCollection

Answer 14

I currently use a format which is like the following

Notation:

[PREFIX][APPLICATION][MODULE]_[NAME]

Example:

P_CMS_USER_UserInfoGet

I like this notation for a few reasons:

• starting with very simple Prefix allows code to be written to only execute objects beggining with the prefix (to reduce SQL injection, for example)
• in our larger environment, multiple teams are working on different apps which run of the same database architecture. The Application notation designates which group owns the SP.
• The Module and Name sections simply complete the heirarchy. All names should be able to be matched to Group/App, Module, Function from the heirarchy.

Answer 15

GetXXX - Gets XXX based on @ID

GetAllXXX - Gets all XXX

PutXXX - Inserts XXX if passed @ID is -1; else updates

DelXXX - Deletes XXX based on @ID

Answer 16

TableName_WhatItDoes

• Comment_GetByID

• Customer_List

• UserPreference_DeleteByUserID

No prefixes or silly hungarian nonsense. Just the name of the table it's most closely associated with, and a quick description of what it does.

One caveat to the above: I personally always prefix all my autogenerated CRUD with zCRUD_ so that it sorts to the end of the list where I don't have to look at it.

Answer 17

I joined late the thread but I want to enter my reply here:

In my last two projects there are different trends like, in one we used:

To get Data : s<tablename>_G
To delete Data : s<tablename>_D
To insert Data : s<tablename>_I
To update Data : s<tablename>_U

This naming conventions is also followed in front-end by prefixing the word dt.

Example:

exec sMedicationInfo_G
exec sMedicationInfo_D
exec sMedicationInfo_I
exec sMedicationInfo_U

With the help of above naming conventions in our application we have a good and easy to remember names.

While in second project we used the same naming conventions with lill difference:

To get Data : sp_<tablename>G
To delete Data : sp_<tablename>D
To insert Data : sp_<tablename>I
To update Data : sp_<tablename>U

Example:

exec sp_MedicationInfoG
exec sp_MedicationInfoD
exec sp_MedicationInfoI
exec sp_MedicationInfoU