Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Stored Procedure Naming Conventions Expand / Collapse
Author
Message
Posted Monday, October 3, 2005 12:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 10, 2013 4:59 PM
Points: 79, Visits: 183

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.



Gary Johnson
Sr Database Engineer
Post #225621
Posted Monday, October 3, 2005 7:30 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 2:01 AM
Points: 11, Visits: 2

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.




Kindest Regards,

Charles Wilkinson
Database Administrator
Sastek Pty Ltd
www.sastek.com
Post #225678
Posted Monday, October 3, 2005 8:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:16 AM
Points: 24, Visits: 51

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




Post #225683
Posted Monday, October 3, 2005 10:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 24, 2010 11:46 PM
Points: 3, Visits: 3

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.

 

Post #225688
Posted Tuesday, October 4, 2005 12:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 9:12 AM
Points: 31, Visits: 169
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.aspx

This 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
Post #226005
Posted Wednesday, October 5, 2005 9:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:24 AM
Points: 328, Visits: 395

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.

Post #226283
Posted Wednesday, October 19, 2005 1:43 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:11 AM
Points: 1,895, Visits: 435

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




Post #230382
Posted Tuesday, October 3, 2006 8:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 27, 2011 6:49 AM
Points: 357, Visits: 88
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.
 
 
Post #312926
Posted Tuesday, October 3, 2006 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 11, 2007 5:42 PM
Points: 46, Visits: 1
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.
Post #312984
Posted Friday, October 6, 2006 7:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 4, 2007 1:10 AM
Points: 82, Visits: 1
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_Get
Customer_Insert
Customer_Delete

.. etc.
Post #313823
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse