What's the best practice of naming stored proc

问题:

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?

回答1:

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.

回答2:

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.

回答3:

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_

回答4:

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

回答5:

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.

回答6:

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.

回答7:

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.

回答8:

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 ?

回答9:

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

回答10:

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