SQL 存储过程的命名约定是什么？

What is your naming convention for stored procedures?

提问by DOK

I have seen various rules for naming stored procedures.

我见过各种命名存储过程的规则。

Some people prefix the sproc name with usp_, others with an abbreviation for the app name, and still others with an owner name. You shouldn't use sp_ in SQL Server unless you really mean it.

有些人使用 usp_ 作为 sproc 名称的前缀，其他人使用应用程序名称的缩写，还有一些人使用所有者名称。你不应该在 SQL Server 中使用 sp_，除非你真的是这个意思。

Some start the proc name with a verb (Get, Add, Save, Remove). Others emphasize the entity name(s).

有些以动词（Get、Add、Save、Remove）开头 proc 名称。其他人强调实体名称。

On a database with hundreds of sprocs, it can be very hard to scroll around and find a suitable sproc when you think one already exists. Naming conventions can make locating a sproc easier.

在具有数百个 sproc 的数据库中，当您认为某个 sproc 已经存在时，可能很难滚动并找到合适的 sproc。命名约定可以使定位 sproc 更容易。

Do you use a naming convention? Please describe it, and explain why you prefer it over other choices.

你使用命名约定吗？请描述它，并解释为什么你更喜欢它而不是其他选择。

Summary of replies:

回复摘要：

• Everybody seems to advocate consistency of naming, that it might be more important for everyone to use the same naming convention than which particular one is used.
• Prefixes: While a lot of folks use usp_ or something similar (but rarely sp_), many others use database or app name. One clever DBA uses gen, rpt and tsk to distinguish general CRUD sprocs from those used for reporting or tasks.
• Verb + Noun seems to be slightly more popular than Noun + Verb. Some people use the SQL keywords (Select, Insert, Update, Delete) for the verbs, while others use non-SQL verbs (or abbreviations for them) like Get and Add. Some distinguish between singluar and plural nouns to indicate whether one or many records are being retrieved.
• An additional phrase is suggested at the end, where appropriate. GetCustomerById, GetCustomerBySaleDate.
• Some people use underscores between the name segments, and some avoid underscores. app_ Get_Customer vs. appGetCustomer -- I guess it's a matter of readability.
• Large collections of sprocs can be segregated into Oracle packages or Management Studio (SQL Server) solutions and projects, or SQL Server schemas.
• Inscrutable abbreviations should be avoided.

• 每个人似乎都提倡命名的一致性，因为每个人使用相同的命名约定可能比使用特定的命名约定更重要。
• 前缀：虽然很多人使用 usp_ 或类似的东西（但很少使用 sp_），但许多其他人使用数据库或应用程序名称。一位聪明的 DBA 使用 gen、rpt 和 tsk 将一般的 CRUD sproc 与用于报告或任务的那些 sproc 区分开来。
• 动词+名词似乎比名词+动词更受欢迎。有些人使用 SQL 关键字（选择、插入、更新、删除）作为动词，而其他人使用非 SQL 动词（或它们的缩写），如 Get 和 Add。有些人区分单数名词和复数名词以指示正在检索一个还是多个记录。
• 在适当的情况下，建议在末尾添加一个短语。GetCustomerById、GetCustomerBySaleDate。
• 有些人在名称段之间使用下划线，有些人避免使用下划线。app_ Get_Customer 与 appGetCustomer —— 我想这是可读性的问题。
• 大型 sproc 集合可以分为 Oracle 包或 Management Studio (SQL Server) 解决方案和项目，或 SQL Server 架构。
• 应避免使用难以理解的缩写。

Why I choose the answer I did:There are SO many good responses. Thank you all! As you can see, it would be very hard to choose just one. The one I chose resonated with me. I have followed the same path he describes -- trying to use Verb + Noun and then not being able to find all of the sprocs that apply to Customer.

为什么我选择我所做的答案：有很多好的答案。谢谢你们！如您所见，很难只选择一个。我选择的那个引起了我的共鸣。我遵循了他描述的相同路径 - 尝试使用动词 + 名词，然后无法找到适用于客户的所有 sproc。

Being able to locate an existing sproc, or to determine if one even exists, is very important. Serious problems can arise if someone inadvertently creates a duplicate sproc with another name.

能够定位一个现有的 sproc，或者确定一个是否存在，是非常重要的。如果有人无意中使用另一个名称创建了重复的 sproc，则会出现严重的问题。

Since I generally work on very large apps with hundreds of sprocs, I have a preference for the easiest-to-find naming method. For a smaller app, I might advocate Verb + Noun, as it follows the general coding convention for method names.

由于我通常在具有数百个 sproc 的大型应用程序上工作，因此我更喜欢最容易找到的命名方法。对于较小的应用程序，我可能会提倡动词 + 名词，因为它遵循方法名称的一般编码约定。

He also advocates prefixing with app name instead of the not very useful usp_. As several people pointed out, sometimes the database contains sprocs for multiple apps. So, prefixing with app name helps to segregate the sprocs AND helps DBAs and others to determine which app the sproc is used for.

他还提倡使用应用程序名称作为前缀，而不是使用不太有用的 usp_。正如一些人指出的那样，有时数据库包含多个应用程序的 sproc。因此，使用应用程序名称作为前缀有助于隔离 sproc，并帮助 DBA 和其他人确定 sproc 用于哪个应用程序。

采纳答案by dnolan

For my last project i used usp_[Action][Object][Process] so for example, usp_AddProduct or usp_GetProductList, usp_GetProductDetail. However now the database is at 700 procedures plus, it becomes a lot harder to find all procedures on a specific object. For example i now have to search 50 odd Add procedures for the Product add, and 50 odd for the Get etc.

对于我的上一个项目，我使用了 usp_[Action][Object][Process]，例如 usp_AddProduct 或 usp_GetProductList、usp_GetProductDetail。然而，现在数据库有 700 个以上的过程，要找到特定对象上的所有过程变得更加困难。例如，我现在必须为产品添加搜索 50 个奇数添加程序，为获取等搜索 50 个奇数。

Because of this in my new application I'm planning on grouping procedure names by object, I'm also dropping the usp as I feel it is somewhat redundant, other than to tell me its a procedure, something I can deduct from the name of the procedure itself.

因此，在我的新应用程序中，我计划按对象对过程名称进行分组，我也放弃了 usp，因为我觉得它有点多余，除了告诉我它是一个过程，我可以从名称中推断出程序本身。

The new format is as follows

新格式如下

[App]_[Object]_[Action][Process]

App_Tags_AddTag
App_Tags_AddTagRelations
App_Product_Add 
App_Product_GetList
App_Product_GetSingle

It helps to group things for easier finding later, especially if there are a large amount of sprocs.

它有助于将事物分组以便以后更容易查找，尤其是在有大量 sproc 的情况下。

Regarding where more than one object is used, I find that most instances have a primary and secondary object, so the primary object is used in the normal instance, and the secondary is refered to in the process section, for example App_Product_AddAttribute.

关于多对象使用的地方，我发现大部分实例都有一级和二级对象，所以一级对象用在普通实例中，二级在流程部分引用，例如App_Product_AddAttribute。

回答by DOK

Here's some clarification about the sp_ prefix issue in SQL Server.

下面是对 SQL Server 中 sp_ 前缀问题的一些说明。

Stored procedures named with the prefix sp_ are system sprocs stored in the Master database.

以前缀 sp_ 命名的存储过程是存储在 Master 数据库中的系统过程。

If you give your sproc this prefix, SQL Server looks for them in the Master database first, then the context database, thus unnecessarily wasting resources. And, if the user-created sproc has the same name as a system sproc, the user-created sproc won't be executed.

如果你给你的 sproc 这个前缀，SQL Server 首先在 Master 数据库中查找它们，然后是上下文数据库，从而不必要地浪费资源。并且，如果用户创建的 sproc 与系统 sproc 具有相同的名称，则不会执行用户创建的 sproc。

The sp_ prefix indicates that the sproc is accessible from all databases, but that it should be executed in the context of the current database.

sp_ 前缀表示可以从所有数据库访问 sproc，但它应该在当前数据库的上下文中执行。

Here'sa nice explanation, which includes a demo of the performance hit.

这是一个很好的解释，其中包括性能影响的演示。

Here'sanother helpful source provided by Ant in a comment.

这是Ant 在评论中提供的另一个有用的来源。

回答by S?ren Kuklau

Systems Hungarian(like the above "usp" prefix) makes me shudder.

系统匈牙利语（如上面的“usp”前缀）让我不寒而栗。

We share many stored procedures across different, similarly-structured databases, so for database-specific ones, we use a prefix of the database name itself; shared procedures have no prefix. I suppose using different schemas might be an alternative to get rid of such somewhat ugly prefixes altogether.

我们在不同的、结构相似的数据库中共享许多存储过程，因此对于特定于数据库的存储过程，我们使用数据库名称本身的前缀；共享过程没有前缀。我想使用不同的模式可能是完全摆脱这种有点丑陋的前缀的替代方法。

The actual name after the prefix is hardly different from function naming: typically a verb like "Add", "Set", "Generate", "Calculate", "Delete", etc., followed by several more specific nouns such as "User", "DailyRevenues", and so on.

前缀后的实际名称与函数命名几乎没有什么不同：通常是一个动词，如“添加”、“设置”、“生成”、“计算”、“删除”等，后面跟着几个更具体的名词，如“用户” ”、“DailyRevenues”等。

Responding to Ant's comment:

回应蚂蚁的评论：

1. The difference between a table and a view is relevant to those who design the database schema, not those who access or modify its contents. In the rare case of needing schema specifics, it's easy enough to find. For the casual SELECT query, it is irrelevant. In fact, I regard being able to treat tables and views the same as a big advantage.
2. Unlike with functions and stored procedures, the name of a table or view is unlikely to start with a verb, or be anything but one or more nouns.
3. A function requires the schema prefix to be called. In fact, the call syntax (that we use, anyway) is very different between a function and a stored procedure. But even if it weren't, the same as 1. would apply: if I can treat functions and stored procedures the same, why shouldn't I?

1. 表和视图之间的区别与设计数据库模式的人有关，而不是与访问或修改其内容的人有关。在极少数需要模式细节的情况下，很容易找到。对于随意的 SELECT 查询，它是无关紧要的。事实上，我认为能够将表和视图一视同仁是一个很大的优势。
2. 与函数和存储过程不同，表或视图的名称不太可能以动词开头，也不太可能是一个或多个名词。
3. 函数需要调用模式前缀。事实上，函数和存储过程之间的调用语法（无论如何我们使用的）是非常不同的。但即使不是，与 1. 一样适用：如果我可以将函数和存储过程视为相同，为什么不呢？

回答by Pittsburgh DBA

I have used pretty much all of the different systems over the years. I finally developed this one, which I continue to use today:

这些年来，我几乎使用了所有不同的系统。我终于开发了这个，我今天继续使用它：

Prefix :

字首 ：

• gen - General: CRUD, mostly
• rpt - Report: self-explanatory
• tsk - Task: usually something with procedural logic, run via scheduled jobs

• gen - 一般：CRUD，主要是
• rpt - 报告：不言自明
• tsk - 任务：通常具有程序逻辑的东西，通过计划作业运行

Action Specifier:

动作说明符：

Ins - INSERT
Sel - SELECT
Upd - UPDATE
Del - DELETE

(In cases where the procedure does many things, the overall goal is used to choose the action specifier. For instance, a customer INSERT may require a good deal of prep work, but the overall goal is INSERT, so "Ins" is chosen.

（在程序做很多事情的情况下，总体目标用于选择操作说明符。例如，客户 INSERT 可能需要大量的准备工作，但总体目标是 INSERT，因此选择“Ins”。

Object:

目的：

For gen (CRUD), this is the table or view name being affected. For rpt (Report), this is the short description of the report. For tsk (Task) this is the short description of the task.

对于 gen (CRUD)，这是受影响的表或视图名称。对于 rpt（报告），这是报告的简短描述。对于 tsk（任务），这是任务的简短描述。

Optional Clarifiers:

可选澄清器：

These are optional bits of information used to enhance the understanding of the procedure. Examples include "By", "For", etc.

这些是用于增强对程序的理解的可选信息位。示例包括“By”、“For”等。

Format:

格式：

[Prefix][Action Specifier][Entity][Optional Clarifiers]

[前缀][动作说明符][实体][可选说明符]

Examples of procedure names:

过程名称示例：

genInsOrderHeader

genSelCustomerByCustomerID
genSelCustomersBySaleDate

genUpdCommentText

genDelOrderDetailLine

rptSelCustomersByState
rptSelPaymentsByYear

tskQueueAccountsForCollection

回答by Jason Kester

TableName_WhatItDoes

表名_WhatItDoes

• Comment_GetByID

• Customer_List

• UserPreference_DeleteByUserID

• Comment_GetByID

• Customer_List

• UserPreference_DeleteByUserID

No prefixes or silly hungarian nonsense. Just the name of the table it's most closely associated with, and a quick description of what it does.

没有前缀或愚蠢的匈牙利废话。只是与它最密切相关的表的名称，以及它的作用的快速描述。

One caveat to the above: I personally always prefix all my autogenerated CRUD with zCRUD_ so that it sorts to the end of the list where I don't have to look at it.

对上面的一个警告：我个人总是在我所有自动生成的 CRUD 前面加上 zCRUD_ ，这样它就可以排序到列表的末尾，我不必查看它。

回答by ConcernedOfTunbridgeWells

Starting a stored procedure name withsp_is bad in SQL Server because the system sprocs all start with sp_. Consistent naming (even to the extent of hobgoblin-dom) is useful because it facilititates automated tasks based on the data dictionary. Prefixes are slightly less useful in SQL Server 2005 as it supports schemas, which can be used for various types of namespaces in the way that prefixes on names used to. For example, on a star schema, one could have dimand factschemas and refer to tables by this convention.

sp_在 SQL Server 中以 开头的存储过程名称是错误的，因为系统进程都以 sp_ 开头。一致的命名（甚至到 hobgoblin-dom 的范围）很有用，因为它有助于基于数据字典的自动化任务。前缀在 SQL Server 2005 中不太有用，因为它支持架构，架构可以用于各种类型的命名空间，就像过去的名称前缀一样。例如，在星型模式上，可以有dim和fact模式，并按照此约定引用表。

For stored procedures, prefixing is useful for the purpose of indentifying application sprocs from system sprocs. up_vs. sp_makes it relatively easy to identify non-system stored procedures from the data dictionary.

对于存储过程，前缀对于从系统 sproc 中识别应用程序 sproc 很有用。 up_vs.sp_使得从数据字典中识别非系统存储过程变得相对容易。

回答by pearcewg

I currently use a format which is like the following

我目前使用的格式如下

Notation:

符号：

[PREFIX][APPLICATION][MODULE]_[NAME]

[前缀] [应用][模块]_[名称]

Example:

例子：

P_CMS_USER_UserInfoGet

P_CMS_USER_UserInfoGet

I like this notation for a few reasons:

我喜欢这个符号有几个原因：

• starting with very simple Prefix allows code to be written to only execute objects beggining with the prefix (to reduce SQL injection, for example)
• in our larger environment, multiple teams are working on different apps which run of the same database architecture. The Application notation designates which group owns the SP.
• The Module and Name sections simply complete the heirarchy. All names should be able to be matched to Group/App, Module, Function from the heirarchy.

• 从非常简单的 Prefix 开始允许编写代码以仅执行以前缀开头的对象（例如，以减少 SQL 注入）
• 在我们更大的环境中，多个团队正在开发运行相同数据库架构的不同应用程序。应用程序符号指定哪个组拥有 SP。
• Module 和 Name 部分只是完成了层次结构。所有名称都应该能够与层次结构中的 Group/App、Module、Function 相匹配。

回答by Gabriele D'Antona

I always encapsulate the stored procedures in packages(I'm using Oracle, at work). That will reduce the number of separate objects and help code reuse.

我总是将存储过程封装在包中（我在工作中使用 Oracle）。这将减少单独对象的数量并有助于代码重用。

The naming convention is a matter of taste and something you should agree with all the other developers at project start.

命名约定是一种品味问题，您应该在项目开始时与所有其他开发人员达成一致。

回答by Steven A. Lowe

for small databases, i use uspTableNameOperationName, e.g. uspCustomerCreate, uspCustomerDelete, etc. This facilitates grouping by 'main' entity.

对于小型数据库，我使用uspTableNameOperationName，例如uspCustomerCreate、uspCustomerDelete 等。这有助于按“主要”实体进行分组。

for larger databases, add a schema or subsystem name, e.g. Receiving, Purchasing, etc. to keep them grouped together (since sql server likes to display them alphabetically)

对于较大的数据库，添加模式或子系统名称，例如接收、采购等以将它们组合在一起（因为 sql server 喜欢按字母顺序显示它们）

i try to avoid abbreviations in the names, for clarity (and new people on the project don't have to wonder what 'UNAICFE' stands for because the sproc is named uspUsingNoAbbreviationsIncreasesClarityForEveryone)

为了清楚起见，我尽量避免在名称中使用缩写（项目中的新人不必想知道“UNAICFE”代表什么，因为 sproc 被命名为 uspUsingNoAbbreviationsIncreasesClarityForEveryone）

回答by Ant

I always use:

我总是使用：

usp[Table Name][Action][Extra Detail]

usp[表名][动作][额外细节]

Given a table called "tblUser", that gives me:

给定一个名为“tblUser”的表，它给了我：

• uspUserCreate
• uspUserSelect
• uspUserSelectByNetworkID

• usp用户创建
• usp用户选择
• uspUserSelectByNetworkID

The procedures are alphabetically sorted by table name and by functionality, so it's easy to see what I can do to any given table. Using the prefix "usp" lets me know what I'm calling if I'm (for example) writing a 1000-line procedure that interacts with other procedures, multiple tables, functions, views and servers.

这些过程按表名和功能的字母顺序排序，因此很容易了解我可以对任何给定表执行的操作。如果我（例如）正在编写一个 1000 行的过程，该过程与其他过程、多个表、函数、视图和服务器交互，则使用前缀“usp”让我知道我在调用什么。

Until the editor in the SQL Server IDE is as good as Visual Studio I'm keeping the prefixes.

在 SQL Server IDE 中的编辑器与 Visual Studio 一样好之前，我会保留前缀。