Stored Procedure Naming Conventions

  • RichB

    SSCrazy Eights

    Points: 9651

    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...

  • IsaacGoGo

    SSC Eights!

    Points: 944

    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.

     

     

  • jettand

    SSC Enthusiast

    Points: 108

    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.

     

  • ian.stone

    SSC Enthusiast

    Points: 113

    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.

  • JT Lovell

    Ten Centuries

    Points: 1158

    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.

     

  • Gary Johnson-259336

    SSCommitted

    Points: 1673

    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

  • Charles Wilkinson

    Old Hand

    Points: 323

    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

  • IsaacGoGo

    SSC Eights!

    Points: 944

    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

  • Bob Dobson

    SSC Rookie

    Points: 31

    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.

     

  • John Eisbrener

    SSC-Addicted

    Points: 479

    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

  • Jacob Pressures

    SSCertifiable

    Points: 5635

    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.

  • bill_twomey

    SSCrazy

    Points: 2301

    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

  • Emil Fridriksson

    Mr or Mrs. 500

    Points: 547

    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.
     
     
  • John Laska

    SSC-Addicted

    Points: 454

    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.

  • Matt Chatterley-373086

    SSC Enthusiast

    Points: 104

    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.

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply