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.

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.

Solution 3

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?

Solution 4

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.

Solution 5

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.

View more solutions

Comments

[Stevan Trajkoski]

What does usp stand for?

[DOK]

I believe that usp is short for "user procedure". That distinguishes it from the system procedures prefixed "sp_". That is an important distinction, as you can read in the answers.

[Stevan Trajkoski]

thanks dok. grazie mille

[mg1075]

sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/… (also see: stackoverflow.com/questions/871612/…)

[Robino]

usp = user stored proceedure

[tsilb]

I'm upvoting this just because it's closed, hopefully to show the powers that be that questions like this are useful to the community.

[leandronn]

Sorry for delayed comment, but it may help. As I am front/backend programmer, I use the following for both MySQL and SQLServer: SPx_PAGE/MODULE_ACTION_OBJECT x: R for read, I for insert, U for update, W for write (combines insert if index not exists or update if exists) and D for delete. SPR_DASHBOARD_GET_USERS

[DOK]

Yes, thanks particularly for addressing abbreviations.

[DOK]

Prefixing every sproc with _usp doesn't help distinguish among them. I think some DBA's like that prefix because it indicates the database object type. Maybe we'll hear from one of them who likes it.

[DOK]

Well, you never know, when you're working on a database dedicated to one app, whether there will be another app later using the same database. In your situation, it sure does help segregate the sprocs.

[DOK]

spu_, interesting. Dodges the SQL Server sp_ issue.

[Ant]

Naming sprocs "sp_" is a really bad idea for speed, too, because SQL Server tries to optimise its lookups for those based on the assumption that they are system procedures. Have a look here, 5th point down: rakph.wordpress.com/2008/04/19/tips-store-procedure

[Ant]

Sooo, how do you know whether you're interacting with a procedure, a function, a view, a table, or anything else?

[Mark Stock]

I would imagine that functions might begin with "Get" or be a name that doesn't begin with a verb. Everything else would be a procedure because after all, they are called stored procedures. Procedures hide the specifics like views, tables and anything else.

[Steven A. Lowe]

@[DOK]: you're welcome - what, no upvote? ;-)

[DOK]

What if more than one Object is involved? For example, what if the sproc queries information from both the Customer and the Orders table?

[DOK]

I generally abhor the use of underscores, but the way you use it -- not just to segregate the prefix, but also to segregate the operation -- would make it easier to find when scanning a list of hundreds of sprocs. Pretty_neat_idea.

[DOK]

Now, there's an interesting take on the prefix. That looks like a good way to segregate sprocs by their usage.

[DOK]

Steve, you got an upvote. I was too busy reading the flurry of answers and comments, and agonizing about which answer is "best".

[dnolan]

Modified question to answer that.

[Mitch Wheat]

What happens is more than one application uses a stored proc?

[DOK]

Thanks Mitch, let's clarify. That "App" prefix is a placeholder for another abbreviation indicating the actual app's name (or acronym). With 3 apps sharing one database, then, there might be ICA_Product_Add, CRM_Product_Add and BPS_Product_Add.

[DOK]

Segregating the "z" items from the rest sounds like a great idea.

[Jason Kester]

Why would you duplicate every procedure 3 times for 3 apps? The whole point of Store Procedures is to have a single place where a given action happens. "ICA_Product_Add, CRM_Product_Add and BPS_Product_Add" destroys that.

[DOK]

Jason, those sprocs may be inserting to different tables. They might have different input parameters or return values. Or they may have different behavior. If the sprocs do the same thing, I agree, there should only be one version. As someone else suggested, shared sprocs might have no prefix.

[DOK]

Even if the same sproc doesn't appear in multiple app's, it still makes it easier to locate the sprocs that are used by a particular app by prefixing it with an app indicator so they're all grouped together.

[dnolan]

If you have multiple applications calling the same procedure then you need to be extra careful, any modification to that proc could break those multiple apps. Naming wise, it is a grey area, but you could name it common/global or anything you see fit. @localghosts: thanks for being informative.

[Steven A. Lowe]

@[DOK]: thanks; the 'best' answer is probably the combination that makes sense for your situation.

[DOK]

Interesting. I've never seen it done quite this way, but it's easy to remember, or guess, the correct names.

[Gaurav Arora]

Thanks DOK, Yes, its easy to remember and we developer feel free from any complexity in names

[onedaywhen]

Why not _C _R _U _D?

[Guilherme]

I'm very late to this... but I'm intrigued @Simon Shaw why you "hate" storedprocedures for things like "Product_GetList". How would you prefer to do it? Even if your way is better surely to hate what seems a very reasonable use of a storedprocedure is a bit over the top?

[ConcernedOfTunbridgeWells]

@DOK - note that these packages have no footprint in the database itself, though. They are purely artifacts of the front-end tool. You can't query by package in the data dictionary. Oracle packages are first class objects in the system data dictionary and have their own scope.

[Gaurav Arora]

@onedaywhen - its a good idea, I will suggest to our DBA so, we can maintain the naming conversions accordingly. But, main motive to this naming convention to present all the object correctly, unless I missed anything ...

[user2609980]

Hmm I don't understand. Why does sp give a performance hit? Is usp or gsp okay?

[GôTô]

@user2609980 DOK says SQL Server searches for sp_ prefixed proc in Master DB first, then in current DB if not found

[astrosteve]

I like this method. They need to be easy to find. When I'm looking thru a list of verb first sprocs and see 200 Gets, 200 Inserts, 200 updates, it's hard to find all the ones for a specific table or grouping. I've used the verb method first, and it gets to be a mess quick. Table name first solves this issue. So for example above in the answer, all your Comment or Customer ones would be grouped together, easy to find.

[leandronn]

And what if you have a query joining several tables?

[asus3000]

But it's not Hungarian. The "usp" isn't a Hungarian variable declaration. The "u" doesn't stand for "update", it stands for "user", as in "user defined stored procedure", and it's merely protecting from SQL Server looking in the Master DB every time it's searching for your stored procedure. Naturally, there are other ways, but "usp" is generally widely considered a standard in many corps, and from what I have seen it works well. It's also taught by Microsoft, and a Microsoft recommended naming convention: msdn.microsoft.com/en-us/library/ms124456(v=SQL.100).aspx

[Piyey]

"sp_" prefix is not recommended.

[Hameed Syed]

Very late but still couldn't understand after reading all answers , if you don't prefix with usp or something, how would one know it's a stored procedure not a view or function?

[undrline - Reinstate Monica]

+1 for clearly stating something that has more convoluted explanations elsewhere. Not news to me, but I think this is simple and concise explanation to someone starting out.

[Profex]

@HameedSyed, You can't execute a view, or a function, or a table…see sp_prefix for more info.

[Profex]

@dnolan, Instead of specifying an App prefix, wouldn't you just use a different Schema for each app? I guess this leads me down the road to looking into what I should be using Schemas for...

[Michael J Swart]

The link to the demo of the performance hit is from an article written in 2001. It's changed since then, here's a more thorough article (from 2012) by Aaron Bertrand: sqlperformance.com/2012/10/t-sql-queries/sp_prefix

[dylanthelion]

Per the sp prefix (WITHOUT the underscore. Don't use sp_, those are special system objects): It may seem redundant to you, if all you use is object explorer. But if you regularly query INFORMATION_SCHEMA or the sys tables, being able to order by name rather than include the object type column, has easily saved me dozens of hours over the years. It also allows for prefixes like zz and tmp to separate procs at a higher level than <app name>.

[Dai]

@Ant You can directly infer the type of object from its syntax, e.g. SELECT * FROM foo is clear that foo is a TABLE or VIEW. SELECT * FROM dbo.MyFunction() is a UDF, SELECT * FROM @tvv is a table-valued variable, and Stored Procedures can only be invoked via EXEC. So there's no ambiguity.

[Dai]

@Ant As for SELECT * FROM foo not showing the type-of foo (as foo could be a VIEW or a TABLE) - that shouldn't matter (it could also be a synonym!) because they're intentionally interchangable - you can also INSERT INTO and UPDATE a VIEW too, don't forget. When databases make breaking changes to their schemas they'll often add VIEWs as stand-ins for older tables - so if the table was named tbl_Foo and it was converted to CREATE VIEW tbl_Foo then that's just silly and wrong by your own standards. Hence: don't use Systems Hungarian prefixes in databases!