对于我的最后一个项目，我使用了usp_ [操作] [对象] [过程]，例如，usp_AddProduct或usp_GetProductList，usp_GetProductDetail。但是现在数据库已经有700个存储过程，找到特定对象的所有过程变得更加困难。例如，现在我必须搜索50个奇怪的Add过程以获取产品添加，还有50个奇怪的Get过程等。

因此，在我的新申请中，我计划按照对象对过程名称进行分组，我还将放弃usp，因为我觉得它有些冗余，除了告诉我它是一个过程，我可以从过程的名称中推断出来。

新的格式如下

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

App_Tags_AddTag
App_Tags_AddTagRelations
App_Product_Add 
App_Product_GetList
App_Product_GetSingle

这有助于将事物分组以便以后更容易找到，特别是如果有大量的存储过程。

关于使用多个对象的情况，我发现大多数实例都有主要和次要对象，因此主要对象在正常情况下使用，次要对象在过程部分中被引用，例如 App_Product_AddAttribute。

这里是有关 SQL Server 中 sp_ 前缀问题的一些澄清。

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

如果您给您的存储过程加上此前缀，SQL Server 首先会在 Master 数据库中查找它们，然后再在上下文数据库中查找，因此会浪费资源。而且，如果用户创建的存储过程与系统存储过程同名，则用户创建的存储过程将不会被执行。

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

这里有一个好的解释，其中包括性能损失的演示。

这里有另一个有用的来源，Ant在评论中提供了。(Zhèlǐ yǒu lìng yīgè yǒuyòng de láiyuán, Ant zài pínglùn zhōng tígōngle.)

“系统匈牙利命名法”（例如上方的“usp”前缀）让我感到不舒服。

我们在不同但结构相似的数据库之间共享很多存储过程，因此对于特定于数据库的存储过程，我们使用数据库名称前缀; 共享的存储过程没有前缀。我认为使用不同的架构可能是一个替代方案，可以完全摆脱这些有点丑陋的前缀。

前缀后的实际名称与函数命名几乎没有什么区别：通常是像“添加”、“设置”、“生成”、“计算”、“删除”等动词，接着是更具体的名词，如“用户”、“每日收入”等。

回应Ant的评论：

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?

表名_它的作用

• 评论_通过ID获取

• 客户清单

• 用户偏好_DeleteByUserID

没有任何前缀或愚蠢的匈牙利游戏。只是与其最密切相关的表格名称和对其功能的简要描述。

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

我多年来使用过几乎所有不同的系统。我最终开发出了这个系统，今天仍在使用：

前缀：

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

动作说明：

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

在操作包含多个步骤的情况下，总体目标被用来选择操作的特定说明。例如，客户端的插入操作可能需要做很多准备工作，但总的目标是插入，因此选择“Ins”。

对象:

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

可选的澄清者：

这些是可选的信息位，用于增强程序的理解。例如：“By”，“For”等。

格式：

[前缀][行动限定词][实体][可选描述语]

程序名称示例：

genInsOrderHeader

genSelCustomerByCustomerID
genSelCustomersBySaleDate

genUpdCommentText

genDelOrderDetailLine

rptSelCustomersByState
rptSelPaymentsByYear

tskQueueAccountsForCollection

在SQL Server中以sp_作为储存过程名称是不好的，因为系统的储存过程都以sp_开头。一致的命名（甚至包括鬼怪）是有用的，因为它方便了基于数据字典的自动化任务。在SQL Server 2005中，前缀略微不太有用，因为它支持架构，可以像以前使用名称的前缀一样用于各种类型的名称空间。例如，在星型模式上，可以有dim和fact模式，并按照这种约定引用表。

对于存储过程，前缀有助于识别应用程序过程和系统过程。 "up_"与 "sp_"相比，可以相对容易地从数据字典中识别非系统存储过程。

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

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

我尽量避免在名称中使用缩写，以确保清晰易懂（而且新加入项目的人不必想知道 UNAICFE 代表什么，因为存储过程被命名为 uspUsingNoAbbreviationsIncreasesClarityForEveryone）。

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.

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

I currently use a format which is like the following

Notation:

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

Example:

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.

I always use:

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

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

• uspUserCreate
• uspUserSelect
• 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.

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

application prefix_ operation prefix_ description of database objects involved (minus the spaces between underscores - had to put spaces in for them to appear).

operation prefixes we use -

• “get” – returns a recordset
• “ins” – inserts data
• “upd” – updates data
• “del” – deletes data

e.g

wmt_ ins _ customer _details

"workforce management tool, insert details into customer table"

advantages

All stored procedures relating to the same application are grouped together by name. Within the group, stored procedures that carry out the same kind of operation (e.g. inserts, updates, etc.) are grouped together.

This system works well for us, having approx. 1000 stored procedures in one database off the top of my head.

Haven t come across any disadvantages to this approach so far.

GetXXX - Gets XXX based on @ID

GetAllXXX - Gets all XXX

PutXXX - Inserts XXX if passed @ID is -1; else updates

DelXXX - Deletes XXX based on @ID

I think the usp_ naming convention does nobody any good.

In the past, I ve used Get/Update/Insert/Delete prefixes for CRUD operations, but now since I use Linq to SQL or the EF to do most of my CRUD work, these are entirely gone. Since I have so few stored procs in my new applications, the naming conventions no longer matter like they used to ;-)

For the current, application I am working on, we have a prefix that identifies the application name (four lowercase letters). The reason for this is that our application must be able to co-exist with a legacy application in the same database, so the prefix is a must.

If we did not have the legacy constraint, I am quite sure that we would not be using a prefix.

After the prefix we usually start the SP name with a verb that describes what the procedure does, and then the name of the entity that we operate on. Pluralization of the entity name is allowed - We try to emphasize readability, so that it is obvious what the procedure does from the name alone.

Typical stored procedure names on our team would be:

shopGetCategories
shopUpdateItem

I don t think it really matters precisely what your prefix is so long as you re logical and consistent. Personally I use

spu_[action description][process description]

where action description is one of a small range of typical actions such as get, set, archive, insert, delete etc. The process description is something short but descriptive, for example

spu_archiveCollectionData 

or

spu_setAwardStatus

I name my functions similarly, but prefix with udf_

I have seen people attempt to use pseudo-Hungarian notation for procedure naming, which in my opinion hides more than it reveals. So long as when I list my procedures alphabetically I can see them grouped by functionality then for me that seems to be the sweet spot between order and unnecessary rigour

Avoid sp_* in SQl server coz all system stored prcedures begins with sp_ and therefore it becomes more harder for the system to find the object corresponding to the name.

So if you begin with something other than sp_ things become easier.

So we use a common naming of Proc_ to begin with. That makes it easier to identify the procedures if presented with one big schema file.

Apart from that we assign a prefix that identify the function. Like

Proc_Poll_Interface, Proc_Inv_Interface etc.

This allows us to find all stored procs which does the job of POLL vs that does Inventory etc.

Anyhow the prefix system depends on your problem domain. But al said and done something similar ought to be present even if it be just to allow people to quicly locate the stored procedure in the explorere drop down for editing.

other eg s of function.

Proc_Order_Place
Proc_order_Delete
Proc_Order_Retrieve
Proc_Order_History

We followed the function based naming coz Procs are akin to code / function rather than static objects like tables. It doesnt help that Procs might work with more than one table.

If the proc performed more functions than can be handled in a single name, it means your proc is doing way much more than necessary and its time to split them again.

Hope that helps.

I joined late the thread but I want to enter my reply here:

In my last two projects there are different trends like, in one we used:

To get Data : s<tablename>_G
To delete Data : s<tablename>_D
To insert Data : s<tablename>_I
To update Data : s<tablename>_U

This naming conventions is also followed in front-end by prefixing the word dt.

Example:

exec sMedicationInfo_G
exec sMedicationInfo_D
exec sMedicationInfo_I
exec sMedicationInfo_U

With the help of above naming conventions in our application we have a good and easy to remember names.

While in second project we used the same naming conventions with lill difference:

To get Data : sp_<tablename>G
To delete Data : sp_<tablename>D
To insert Data : sp_<tablename>I
To update Data : sp_<tablename>U

Example:

exec sp_MedicationInfoG
exec sp_MedicationInfoD
exec sp_MedicationInfoI
exec sp_MedicationInfoU