25

I have worked with several big databases and the names of stored procedures were very different: 

SP_PrefixXXX
PrefixYyyXxx
Prefix: Rep, Act

What's the best practice of naming? How can I organize them in a proper way?

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
Galkin
  • 791
  • 2
  • 9
  • 25
  • See also: http://stackoverflow.com/questions/238267/what-is-your-naming-convention-for-stored-procedures?noredirect=1#comment22043254_238267 – DOK Mar 21 '13 at 18:37

11 Answers11

52

The sp_ prefix stands for System Procedure, and it should not be used as prefix for regular procedures. If you do, it will first make an extra trip to the master database each time to look for the procedure, and if it would have the same name as one of the procedures there, that procedure will be executed instead of your procedure.

Other than that, you are free to make up any naming convention you like. One used by our company is subsystem_object_action, e.g. main_Customer_Get. That puts procedures that belong together close to each other in the listing.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • We use something similar to that where I am, except that a "Get" will usually include a bit more description, such as: main_Customer_Get_By_ID – Tom H May 16 '09 at 12:58
  • @Tom: Getting by id can be seen as implied in the "Get" if one likes, but yes there are of course other actions that need a longer description, like main_Customer_GetByRegion. As you see we prefer to keep the underscores to separate the components of the name and use pascal case for the table names and actions. – Guffa May 16 '09 at 13:31
  • 5
    I tested your "it will first make an extra trip to the master" on SQL Server 2008R2: 1. Created sp_Mine in both my database and in master, the first one printing "mine#1" and the other one saying "master#2". 2. Run the proc in my db - and got "mine#1" printed out! I'd expect if SQL Server first goes to master, it would have found the other proc and would have printed "master#2" instead.. 3. But then I created "sp_autostats" in my db (named after a sys proc in master db) and in this case it called the master proc. Maybe your statement is correct only for [sys] procs in master? – andreister Feb 24 '11 at 15:57
7

The best naming convention is the one that is consistent throughout your database :)

Really, it's up to you and your team. So long as it's clear and sensible, you have a fair bit of leeway. Just make sure that whatever you decide on, everyone adheres to it. Far more important than the convention itself is the fact that everyone sticks with it.

I tend to avoid sp_, usp_ and the like, because I find them redundant. For instance, a sproc called InsertCustomer is clearly a sproc, and in no way could be confused for a table, view, or any other sort of object. sp_ in particular should be avoided.

I prefer CamelCase, but again, that's a matter of preference. I like my proc name to give a good indication of what the proc does - for instance:

InsertSalesOrder PopulateItemStagingTables CalculateOrderSummary PrepareCustomerStatements

etc.

Aaron Alton
  • 22,728
  • 6
  • 34
  • 32
7

I like prefixing them so SP's dealing with specific objects are grouped together. So instead of something like:

    InsertUser
    UpdateUser
    DeleteUser
    GetUsers

I do it like this:

    AppName_User_GetUser
    AppName_User_InsertUser
    AppName_User_UpdateUser
    AppName_User_DeleteUser

I find this is easier for me to manage in my SQL management app and in my code too.

Like the other folks said, don't prefix with sp_

rvarcher
  • 1,566
  • 1
  • 12
  • 14
  • 1
    This is an old post but I found it helpful to confirm that I was naming sp's correctly or using best practices.I started using the app name (or initials) as a prefix for stored procedures only when the procedure is solely being used in that app. During a modificaton of a very old app, I converted all inline sql to sp's. After creating many sp's I found this naming convention allowed them to be grouped together and easier to find when it came time later to re-create them prior to moving them to production database. – Doreen Apr 08 '15 at 15:19
  • 3
    _**AppName**_User_GetUser_ is not a good name for a stored procedure IMO. Stored procedures are objects that should be generic reusable by any application that connect to the database. What if you have multiple applications connecting to the database? what if your application name changes? I have seen many stored procedures named after applications that have long gone. Database objects tend to outlive most applications, so name them wisely. – Yasir May 14 '15 at 10:28
6

Not "sp_", nor "usp_". We know they're stored procedures, the naming scheme doesn't need to tell us.

I generally just name them for what they do, possibly partitioning them on schemas. The exceptions are that I will use an "ssis_" prefix for stored procedures which aren't directly used as part of the "normal" database operations, but which are used by an SSIS package to reference the database. I may use "fn_" to indicate a function, to distinguish it from a stored procedure.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
6

I don't know if there really is a specific 'best practice' in this case. With the company I am at now, the standard is usp[ProcedureName] (no underscore). I would personally prefer no prefix at all, but if you are coming in new to a company or project and they have pre-existing standards, unless they are using sp_ where there is a technical reason not to use this, it is probably not an issue worth debating as I certainly don't think it is in this case at all an egregious standard.

Generally re naming conventions, if you do have a debate and other team members disagree with you and the consensus standard is different, the best policy is to quickly let it go and accept the consensus; consistency is generally more important than the actual standard itself, as is getting along well with other team members and not developing a reputation for being 'difficult'.

PaulR
  • 346
  • 1
  • 4
2

Well, prefixing the name with "SP_" is pretty much redundant: it's naming for the implementation (it's an SP, as opposed to a table or a view). There are plenty of other ways (systebales, information_schema, how you use it) that will tell you hw it's implemented.

Instead you should name it for its interface, for what it does for you. For convenience (as many things end up ordered alphabetically), I like to group like things under like names, possibly using the same prefix.

But again, name it for what it does, not how it happens to be implemented.

In general, I find that the common naming conventions for database objects use underscores instead of CamelCase; this is just a matter of convention. What is not mere convention is the also common convention of using all lowercase letters for database objects; this allows you to ignore server settings which may or may not be case-insensitive.

tpdi
  • 34,554
  • 11
  • 80
  • 120
1

I tend to try to give names that not only give an idea what the function is for, but what the input variables will be.

For example: ProcessMathEquationWithFieldIdPlantId

This will help give information immediately to anyone else using it, I believe.

I also avoid sp_ and usp_ to limit any chance of name collisions.

James Black
  • 41,583
  • 10
  • 86
  • 166
0

Im not a pro but i like this way

Prefix of application = XY; View = v; Stored Procedure = p; Function = f

Table: XY_Name
View: vXY_Name
Procedure: sXY_Name
Function: fXY_Name

What do you think ? I know some people use the two characters for identifying object type but one character is enough for most cases, right ?

Muflix
  • 6,192
  • 17
  • 77
  • 153
0

Better create schema for seperate module.

Then Give Meaningful and simple name.

For Example: if you are working school project.

create Student schema

procedure name :AddStudent

So it will look like Student.AddStudent in procedurelist

same thing for Teacher Module

user2275860
  • 57
  • 3
0

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

page/module: the place who calls the procedure

Examples:

SPR_DASHBOARD_GET_USERS //reads users data
SPW_COMPANIES_PUT_COMPANY //creates or modifies a company
leandronn
  • 173
  • 1
  • 17
0

We have used this convention: Prefix_Factory_Action

Prefix

  • up: short for User Procedure It is intended for use in the Namespace
  • ap: short for Administrator Procedure It is intended for use by the Database Administrators only
  • sp: short for System Procedure. Should only be used by Microsoft because master is checked first
  • xp: short for eXternal Procedure. Used by Microsoft and other developers

Factory

A name used in our Namespace, or any other logical construct. It's not a table, although it often has the same name as one. Examples include Paper, Staff, and Student

Action

The type of action being performed, such as Get, Set, Insert, Update, Delete, Save, Populate, Repopulate, etc.

The Action is sometimetime followed by a qualifier, such as For/By/In

Examples

  • ap_Paper_Repopulate
  • up_Staff_Save
  • up_Enrolment_GetForStudent
John Hunter
  • 19
  • 1
  • 4