为 t-sql 命名存储过程的最佳实践是什么？

What's the best practice of naming stored procedure for t-sql?

提问by Galkin

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?

命名的最佳做法是什么？我怎样才能以适当的方式组织它们？

回答by Guffa

The sp_prefix stands for System Procedure, and it should notbe used as prefix for regular procedures. If you do, it will first make an extra trip to the masterdatabase 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.

该sp_前缀代表系统过程，它应该不会被用来作为前缀的正常程序。如果这样做，它master每次都会首先额外访问数据库以查找过程，如果它与那里的过程之一具有相同的名称，则将执行该过程而不是您的过程。

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.

除此之外，您可以自由地编写您喜欢的任何命名约定。我们公司使用的一种是subsystem_object_action，例如main_Customer_Get。这将在列表中彼此靠近的程序放在一起。

回答by Aaron Alton

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.

我倾向于避免 sp_、usp_ 等，因为我发现它们是多余的。例如，名为 InsertCustomer 的 sproc 显然是一个 sproc，绝不能与表、视图或任何其他类型的对象混淆。尤其应该避免 sp_。

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:

我更喜欢 CamelCase，但同样，这是一个偏好问题。我喜欢我的 proc 名称可以很好地表明 proc 的作用 - 例如：

InsertSalesOrder PopulateItemStagingTables CalculateOrderSummary PrepareCustomerStatements

InsertSalesOrder PopulateItemStagingTables CalculateOrderSummary PrepareCustomerStatements

etc.

等等。

回答by rvarcher

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

我喜欢给它们加上前缀，这样处理特定对象的 SP 就组合在一起了。所以而不是像这样的东西：

    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.

我发现这对我来说更容易在我的 SQL 管理应用程序和我的代码中进行管理。

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

就像其他人说的，不要前缀sp_

回答by PaulR

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.

我不知道在这种情况下是否真的有特定的“最佳实践”。对于我现在所在的公司，标准是 usp[ProcedureName]（无下划线）。我个人更喜欢没有前缀，但是如果您是新公司或项目的新手，并且他们有预先存在的标准，除非他们使用 sp_ 有技术原因不使用它，否则它可能不是值得辩论的问题，因为我当然认为在这种情况下它根本不是一个令人震惊的标准。

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

一般重新命名约定，如果你确实有争论而其他团队成员不同意你并且共识标准不同，最好的策略是迅速放手并接受共识；一致性通常比实际标准本身更重要，因为与其他团队成员相处融洽，不要因“困难”而出名。

回答by John Saunders

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

不是“sp_”，也不是“usp_”。我们知道它们是存储过程，命名方案不需要告诉我们。

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.

我通常只是根据它们的作用来命名它们，可能会在模式上对它们进行分区。例外情况是，我将对存储过程使用“ssis_”前缀，这些存储过程不直接用作“正常”数据库操作的一部分，而是由 SSIS 包用于引用数据库。我可能会使用“fn_”来表示一个函数，以将其与存储过程区分开来。

回答by tpdi

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.

好吧，在名称前加上“SP_”是多余的：它是实现的命名（它是一个 SP，而不是一个表或视图）。有很多其他方式（systebales、information_schema、你如何使用它）会告诉你它是如何实现的。

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.

一般来说，我发现数据库对象的通用命名约定使用下划线而不是 CamelCase；这只是约定俗成的问题。不仅仅是约定俗成的，还有对数据库对象使用所有小写字母的通用约定；这允许您忽略可能不区分大小写的服务器设置。

回答by James Black

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

例如：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.

我还避免了 sp_ 和 usp_ 以限制名称冲突的任何机会。

回答by Muflix

Im not a pro but i like this way

我不是专业人士，但我喜欢这种方式

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

应用前缀 = XY；视图 = v; 存储过程 = p; 函数 = 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 ?

你怎么认为 ？我知道有些人使用这两个字符来识别对象类型，但在大多数情况下，一个字符就足够了，对吗？

回答by user2275860

Better create schema for seperate module.

更好地为单独的模块创建架构。

Then Give Meaningful and simple name.

然后给有意义和简单的名字。

For Example: if you are working school project.

例如：如果您正在从事学校项目。

create Studentschema

创建学生模式

procedure name :AddStudent

程序名称：AddStudent

So it will look like Student.AddStudentin procedurelist

所以它看起来像程序列表中的Student.AddStudent

same thing for Teacher Module

教师模块同样的事情

回答by leandronn

This may help. As I am front/backend programmer, I use the following for both MySQL and SQLServer:

这可能会有所帮助。由于我是前端/后端程序员，我对 MySQL 和 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.

x：R 表示读取，I 表示插入，U 表示更新，W 表示写入（如果索引不存在则合并插入，如果存在则更新）和 D 表示删除。

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