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

Solution 1:

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.

Solution 2:

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.