What is your naming convention for stored procedures? [closed]

Question

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.

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.

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?

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.

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

Starting a stored procedure name withsp_ 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.