SQLServerCentral / Discuss Content Posted by Joseph Sack / Article Discussions / Article Discussions by Author / Stored Procedure Naming Conventions / Latest Posts

RE: Stored Procedure Naming Conventions

nishad.jadhav — Mon, 13 Feb 2012 08:21:53 GMT

Thanks!:-)

RE: Stored Procedure Naming Conventions

sushila — Mon, 13 Feb 2012 07:33:29 GMT

Something that uses "multiple columns", "multiple conditions" in any abbreviated form/combination that you may find makes sense in your environment?! :unsure:I would tend to stick as closely as possible to the purpose of the procedure without having to make it into a long sentence.

RE: Stored Procedure Naming Conventions

nishad.jadhav — Mon, 13 Feb 2012 03:18:09 GMT

Hi, Any suggestions on how one can name SPs which update different columns of a same table, based on diffrenet conditions?Thanks!

RE: Stored Procedure Naming Conventions

shobin-1086015 — Fri, 19 Mar 2010 03:20:45 GMT

I suggest not to make use of any prefixes.Just we can use Sel_User or User_SelThis will no cause any performance issue as we are not using sp_.Also it will help us to search the SP if we have a large number of SPs say 500.If we want to make a change to user details section of our application. all SPs related to a table can be sorted out easily by starting alphabet. from there we can get the select or Get sp

RE: Stored Procedure Naming Conventions

Matt Chatterley-373086 — Fri, 06 Oct 2006 07:07:00 GMT

Prefixing SPs with "usp" seems redundant to me - u - user - it must be a user object, because last time I checked, I wasn't SQL Server - sp - well, it's "CREATE PROCEDURE.." therefore it's an SP!The remainder of the prefixes, I dislike, as procedures will get grouped together by the type of action they perform, rather than by their region of the system, I generally use:Customer_GetCustomer_InsertCustomer_Delete.. etc.

RE: Stored Procedure Naming Conventions

John Laska — Tue, 03 Oct 2006 10:32:00 GMT

Like the author, I use the 'usp_' prefix on all of my stored procs. The rest of the name is descriptive text. Personally, I like to be able to take a quick look at the list of stored procedures and see which ones are mine, which ones belong to the system and which ones belong to the application. Obviously, I don't use 'usp_' for those procedures which are directly involved in the application, assuming I am actually writting it. Unfortuntaly, I've been stuck having to muck about with boxed applications to produce results outside of what was originally intend. For example, I have a couple of minor tweaks that I have done to our MS Project database (mostly for reporting stuff), which required stored procs. The last thing I would want to do is lose my procedures inside the massive list of application procedures, so all of my procs are named 'usp_InsertdescriptiveTextHere'. When looking at the list of procs, I just scroll past the hundered or so 'MSP_ProjectProcName', to my little list at the bottom.I had not considered adding an extra prefix to my procs to define the object(s) they touched, but I think I may steal that idea.Good article overall, if nothing else, it has spawned a good discussion.

RE: Stored Procedure Naming Conventions

Emil Fridriksson — Tue, 03 Oct 2006 08:38:00 GMT

The approach my company uses is very handy for our application.

All tables, functions and stored procs start with a 3 letter abbrevation of the system its related to, for example acc for accounting, aut for authentication, inv for inventory, nws for the news/forum system, npc for the Non player characters system, etc.

To give an example of a the relationship between tables and procs, we have a table for agent research, the table is called agtResearch. The proc to start research is called AgtResearchStart. So the procs are usually named after the main table they interact with+the operation you are performing. We also, for sorting purposes mostly, add an X before the name of procs that are rarely used or are only used for administrative purposes and procs and tables only inteneded for DBAs have names starting with ZDBA, the Z for sorting purposes and DBA to indicate that its a DBA only proc.

We have almost 1900 stored procs and this system is very handy to get an overview of what the stuff does. If you want to find out what you do to create a new item, just search our DB script repository for "CREATE PROCDEURE dbo.InvItem" and you'll get all inventory related procs.

RE: Stored Procedure Naming Conventions

bill_twomey — Wed, 19 Oct 2005 13:43:00 GMT

Say you have a database with 100 tables, with 4 procs each, 400 stored procs beginning with usp_. Your eyes quickly start to filter out the usp_. That prefix is noise. Save the characters for something useful, or just keep your proc names short.

System_Action_Object descriptor or just Action_Object_descriptor for a database used for just one module/system/subject

RE: Stored Procedure Naming Conventions

Jacob Pressures — Wed, 05 Oct 2005 09:48:00 GMT

I think it is a decent article at least for newbies. I had no naming convention and haven't had time to develop one as i work on this project. Having SOME IDEAS verses NONE is better than none at all. And this has been a worry on my part but i had no where to begin.

This discussion has also cleared up some things that are found in the books i've read taht do suggest using another prefix other than sp_.

Since i'm programming i do feel i need to be able to tell the difference between an SP in code an other variables or constants. Knowing for sure it is an SP can be valuable.

I will give more thought to my naming convention now even if i haven't decided how to implement it.

RE: Stored Procedure Naming Conventions

John Eisbrener — Tue, 04 Oct 2005 12:58:00 GMT

I'm sure we all have our own opinions on this, but why would you ever append usp_ or sp_ in front of a stored procedure. I've not worked on most other applications than SQL Server, so my opinion is mute on all other platforms, but in regards to SQL Server, all SPs are already stored seperately from other objects, so appending usp_ or sp_ in front of the procedure name is just redundant information. Yes, we know it's an SP, not too concerned with what type, all system SPs are pretty easy to identify. For comparision reasons, here's an article I've been pointed to regarding naming practices which I think offers some very valid points.http://weblogs.asp.net/jamauss/articles/DatabaseNamingConventions.aspxThis should definately be on any SQL developer's mind when it comes to DB creation (and especially for maintenance concerns) so I believe a lengthier article should have been written. Nice attempt, but little worth in my opinion. Sorry, I think this article is more of an opinion and less of an in-depth look of this inherant problem with DB design.John

RE: Stored Procedure Naming Conventions

Bob Dobson — Mon, 03 Oct 2005 22:04:00 GMT

In our environment SQL development is divided between 3 groups and about 1/2 "freelancers".

Two of the groups and all of the freelancers have no convention.

In my team we following something like the function_subfunction_Description_Parameter format.

I set the function

UI - stored procs used to deliver functionality to a user interface

MAINT - used to perform application maintenance functions

REPORT - used for reports

The sub function is something like AR, ORDERS

The Parameter would CURRENT month, etc.

Its not documented well, but the three or four us work well together on it.

RE: Stored Procedure Naming Conventions

IsaacGoGo — Mon, 03 Oct 2005 20:25:00 GMT

Charles - I agree 100%. There are many workable standards out there - but the best one is having one and sticking with it.

At first I was worried about putting my pet standard out there in this article, but I'm now glad I did it, because I think it has produced an interesting discussion. Also, I'm seeing that no one standard solves every potential situation - as almost every comment on this article has a point-counter-point.

Best Regards,

Joe

RE: Stored Procedure Naming Conventions

Charles Wilkinson — Mon, 03 Oct 2005 19:30:00 GMT

Just a personal prefix that I like to use rather than "usp_" is "up_" this way I can perform a find for every occurance of "up_" to find all user procedures in a large script, "sp_" to find all system procedures in a large script and "p_ to find all procedures, user or system, in a large script.

But my personal preference for any standard naming conventions is to simply have one and have it documented. Unless Microsoft decide to set it in stone as part of the SQL code, no-one will ever agree on a common convention across the board but as long as we have a convention and have it documented this can make it all the more easier for outsiders or fellow co-workers to navigate our work and save far more time than it costs.

RE: Stored Procedure Naming Conventions

Gary Johnson-259336 — Mon, 03 Oct 2005 12:58:00 GMT

Naming conventions are difficult to make useful ALL OF THE TIME. I'm currently working at a place that uses a strict naming convention. Our SPs are named by project_method_action_sp. Unfortunately when they have something common like adding a customer they do not have a good way to use the above naming convention. And my personal feeling is that the suffix _sel_sp is just rediculous as you will find an sp that the developer may have thought would be a simple select but in actuality does much more so the _sel_sp has no meaning at all. Also, why bother with the suffix _sp? I do like having the SPs grouped by project and method. But for the generic ones I would go with something along the lines of global_customer_add. That way you can find the reusable ones very easily and the project specific ones will be grouped together as well. My biggest gripe with our current method is that there is no correlation between the functional spec "method" name and the stored procedure name. This makes it very hard to validate that the functional spec has been correctly implemented.

Having a naming standard is great. But it can be taken too far. For instance we have a standard that states that all variable names, stored procedure names, function names, temp/variable tables have to be in lower case and all their field names in lower case yet the table names and field names of regular tables have to be mixed (are you confused yet? I know I get that way when I'm coding trying figure out what case I need to be using!). They also don't allow the use of aliases on the tables unless used in a subquery. All in all the coding standards they have set up make for a lot of bloated code and extra time spent coding. But since their code review process is also very strict the code is pretty and formatted the same way!

A comment on prefixes. If you use the <project>_<Method>_<action> and do your coding in Visual Studio Enterprise edition with a database project you can access the SPs in the database by hitting the first letter on your keyboard in the server explorer panes tree view. If all your SPs use usp_ as a prefix you don't get to use this handy feature.

RE: Stored Procedure Naming Conventions

JT Lovell — Mon, 03 Oct 2005 12:17:00 GMT

The prefix is helpful, but only in certain conditions.

If you're using the Query Analyzer object viewer or using Enterprise Manager to browse for your stored procedures, then a prefix probably won't help you much. I don't think it would make it more difficult either.

Remember that the naming convention for stored procs is for more than just the DBA though. Developers use these stored procs and in a complex script with many stored procs, tables, views, functions, and other objects, it takes out the ambiguity when looking at a script. It's especially helpful if you're looking at the script via a text editor, Visual Studio, or something else where you don't have the full support of Query Analyzer or the ability to quickly run a SQL statement every time you see an object name and can't remember what it is. It won't make or break you, it just makes development easier if you can limit the guesswork.

Good article though. I'm a big supporter of naming conventions. I have used several different ones at various client sites and the ones who have a standard and follow it are much easier to support than those who don't -- irregardless of whether they include or discard the prefix.

RE: Stored Procedure Naming Conventions

ian.stone — Mon, 03 Oct 2005 11:43:00 GMT

As Sushila said it may be obvious to some that it is sensible to move column names into variables of the same name prefixed by @ but I have seen yards of off-shore code which does not follow this convention. Also in the interest of laziness tables like Purchase_order_header (I wonder what that might be?) are called something like Purordhd. A good example of this is Sage, a leading UK accountancy package. It has table names like stockm uses tables as if they were flat files and does not have any constraints. There is also ignorance about the use of triggers, functions and transaction processing.

RE: Stored Procedure Naming Conventions

jettand — Mon, 03 Oct 2005 11:38:00 GMT

I know it's almost universal to have Verb-first, then the Object as a naming convention:

Upd_Users
Upd_Inventory
Upd_Vendors

But I use the reverse -- Object-first, then Verb -- for a specific reason

Users_Upd
Inventory_Upd
Vendors_Upd

The reason is that when they are listed alphabetically (as they are everywhere) I want to see all of the User-oriented sprocs together, then all of the Inventory-oriented sprocs together, etc.; rather than all of the Delete sprocs together, then all of the Insert sprocs together, etc.

RE: Stored Procedure Naming Conventions

IsaacGoGo — Mon, 03 Oct 2005 11:02:00 GMT

Wangkhar,

With the correct scripting or migration tools, explicit constraint names aren't necessary. I don't object to constraint naming conventions - but I find that for a team of developers and dbas, they are difficult to enforce. I try to focus on tables, views, stored procedures, and other major object types instead. If you have the environment control to keep constraint-level names standardized - I envy you.

RE: Stored Procedure Naming Conventions

RichB — Mon, 03 Oct 2005 10:14:00 GMT

So whats so extreme about conventions for naming of 'system created constraints'?

It's damnably hard work to issue database upgrade scripts from Dev --> QA --> Live without having standard, explicit constraint names...

RE: Stored Procedure Naming Conventions

AndreQ1 — Mon, 03 Oct 2005 10:05:00 GMT

We use Mark Sandy's approach as well. Many of our stored procs deal with one table, and so the Module_Table_Action has worked EXTREMELY well. If you want to see which procs are acting on a table, its very easy to find.

Even better, when training someone else on the architecture, it takes all of 5 minutes for them to comprehend - the ultimate in sustainability.

Since I also code on the client side, its made that part simpler as well.

The last thing I'll mention is that the unique identifier for a table I always make the table name + "ID", e.g., CustomerID for the Customer table. Makes running queries by string concatenation really useful. Once I know my table name, I know that the proc to retrieve information is RPO_[TableName]_GET, based off of ID [TableName]ID.

I can't say enough good things about this technique.

RE: Stored Procedure Naming Conventions

Tatsu — Mon, 03 Oct 2005 09:57:00 GMT

I can divide my stored procedures to operate in different parts of the DB (CRM, Statistics, Reporting etc.). That's a module (call it whatever you want) - Mark Sandy

This is where some of the confusion comes in with naming conventions. Mark's standard is great for him as an application developer since he is concerned with the individual modules and the stored procedures that go with them. What about the database developer though? Or even another application working on a different module that needs to make a change to the Customer table? It's much more difficult for these folks to identify the procedures they need to touch to make sure the impact of their change is minimized.

Is there a naming convention that covers all situations? Probably not.

RE: Stored Procedure Naming Conventions

Mark Sandy — Mon, 03 Oct 2005 09:35:00 GMT

I use a slightly different approach (despite of the usp_)>> Module_Object_ActionI can divide my stored procedures to operate in different parts of the DB (CRM, Statistics, Reporting etc.). That's a module (call it whatever you want)Then, I have objects. This includes all tables and views I'm operating on, but can also be something abstract like sales, inventory or so. Finally, I would have actions operation on the objects. I would use 'Edit','Delete','Insert','Generate','Calculate' or whatever.Now, if I sort the SP in the Enterprise mangager, I have them nicely grouped by module, object and action. procedures which operate on the same object in the context of a module are listed together. That's exactly what I need.Examples are: CRM_Customer_AppendCRM_Customer_DeleteCRM_Customer_ModifyStats_Sales_ReportStats_Customer_ListingStats_UnfinishedOrders_Listing

RE: Stored Procedure Naming Conventions

BobAtDBS — Mon, 03 Oct 2005 09:11:00 GMT

I tend to dislike the prefix concept. Stored Procedures are all grouped together anyway. We all know not to use SP_, so the lack of that prefix is a standard in itself.

I like the rest of the idea and carry it a bit further. I want the name of the SP to tell me not only what table(s) it's accessing but what it's purpose is. That gives me clues as to whether I can extend it or use it elsewhere.

A SP named GetSalesReportInfoBySalesRep tells me a lot more than usp_Sel_Sales. In the second case, can I use the SP elsewhere in my program without expecting it to get modified later? Can I add columns to the report without breaking things?

I also comment the SP pretty clearly on the inside. I put it's complete purpose as designed and even a date and where used, although I know that will be out of date and incomplete eventually.

I guess I name SPs the way I name variables. Long, descriptive and as specific as possible. In VB, where my front ends are almost always written, long doesn't matter since IntelliSense always fills in the blanks anyway. (Well, not for SPs but for a lot of things.)

RE: Stored Procedure Naming Conventions

Mike C — Mon, 03 Oct 2005 08:55:00 GMT

If you want to "appeal to authority", check out Celko's SQL Style book. He gives a well-rounded discussion of his interpretation of ISO naming convention standards.

RE: Stored Procedure Naming Conventions

Roger Cogswell — Mon, 03 Oct 2005 08:03:00 GMT

Without going out on a limb, I concur with Bryant's appeal for an article comparing various naming conventions. We have a team that is currently in the throws of a naming conventions discussion and we could certainly use an outside arbiter!

RE: Stored Procedure Naming Conventions

sushila — Mon, 03 Oct 2005 07:58:00 GMT

Couldn't agree more...the more hands used, the further things get away from conventions...after many hands and much time nobody'd ever guess that it started out with any standards at all..."too many cooks..." and each with their own recipes..

RE: Stored Procedure Naming Conventions

P Jones — Mon, 03 Oct 2005 07:52:00 GMT

It's easy to have a convention and stick to it yourself (closely anyway). What's really difficult is getting others to use it!

RE: Stored Procedure Naming Conventions

IsaacGoGo — Mon, 03 Oct 2005 06:51:00 GMT

Hi,

I figured this article would get some discussion! I like the usp_ convention - but I know of those who do not. 100 DBAs = 100 Naming conventions. That was why I was a bit afraid to write this article.

In any case, walking into a new client's site - I'm just happy if they are actually following any convention in the first place (and if they are sticking to it). Usually I find that this is almost never the case.

Best Regards,

Joe

RE: Stored Procedure Naming Conventions

Aldex — Mon, 03 Oct 2005 06:41:00 GMT

I agree with Ian and Bryant regarding using a prefix such as usp_ to define a stored procedure. I always use object type prefixes where there is potential for ambiguity, so for example I would still differentiate Tables and Views (eg tblCustomers or vwCustomers). However a Stored Procedure cannot readily be confused with any other object so the rationale for an object type prefix dissapears.

I also note that Microsoft are moving away from object type prefixes in, for example, the later versions of Visual Studio. But maybe this is because there are now so many objects that a simple prefix is not enough to disambiguate them (with thanks to Ken Getz who introduced me to this 'word' during one of his forays to the UK) :

Regards,

David SavilleAldex Software Ltd.www.aldex.co.uk

RE: Stored Procedure Naming Conventions

sushila — Mon, 03 Oct 2005 06:38:00 GMT

Nice article Joe...while this may seem very basic to some, there are developers out there who don't work within the confines of defined standards...I find it easy to organise my database objects if I can order them by name and I have my own naming convention (whatever that may be)...the important thing is to have a convention and consistently stick to it!The other protocol I strictly adhere to is naming my variables the same as the column names in use - so if I have a column called BudgetPK my variable is @BudgetPK - while this may seem "doh!!! Talk about stating the obvious!!!" to some, I've seen heaps of procedures where the developers don't follow this "rule"!

RE: Stored Procedure Naming Conventions

Tatsu — Mon, 03 Oct 2005 06:25:00 GMT

If you need to divide the procedures into different functional areas and you're using SQL 2005 then you might want to look at using schemas; conventional best practice prior to 2005 says that all objects should be owned by dbo so you probably don't want to head in that direction.

I agree with the concept of naming procedures with the predominant table or view being accessed and describing the action being performed but I would reverse the order of the two. Having the table/view name first makes it easier to identify the procedures that need to change when the table/view is changed since they will all sort together.

As Ian said, I would also throw out the "usp_" prefix; it's just noise and doesn't pose a problem unless you have a table or view called "sp" (unlikely).

This was a good, simple presentation of a common topic. It would be refreshing to have someone write an article that looked at several naming schemes and compared them. I am partial to mine but would be open to other suggestions. I have promised an article or two already and not delivered so I'm not going too far out on a limb here

RE: Stored Procedure Naming Conventions

ian.stone — Mon, 03 Oct 2005 06:05:00 GMT

Prefixing stored procedure names adds clutter and makes finding thim in Query analiser difficult. After all SELECT * FROM sysobjects WHERE xtype='P' get them all!

RE: Stored Procedure Naming Conventions

Aldex — Mon, 03 Oct 2005 03:41:00 GMT

Whilst I agree with the general tenor of the article I have found that the suggested naming convention can become unwieldy when there are a large number of stored procedures in a database (say more than a couple of hundred).

In this situation it becomes difficult to locate particular stored procedures, especially if they are doing something out of the ordinary. Hence for larger databases we now use a naming convention with a prefix which defines the area that the stored procedure relates to (for example Stk... for a stock control section, Del... for a delivery section, Enq... for enquiries, etc.). The remainder of the stored procedure's name can then either continue as suggested in the article or as per some other convention.

Regards,

David SavilleAldex Software Ltd.www.aldex.co.uk

Stored Procedure Naming Conventions

IsaacGoGo — Thu, 25 Aug 2005 14:02:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jsack/storedprocedurenamingconventions.asp