Stored Procedure Naming Conventions
By Joseph Sack
Discussions of naming conventions can, and usually do get ugly. There are so many ways you can do it. Some people don't give standards any thought, while other database developers are zealous to the point of renaming system generated constraints. How far should one go to implement standards
across your databases? Do you apply standards to all database object types or just some (for example just views, tables, columns, stored procedures)? What about third party applications where you can't control or modify their object names and source code? The larger the team of database developers, the harder it is to build consensus. Even if you are able to pound out a standard, enforcing it can be difficult.
Why then, is it worth the effort? The best argument I've seen for establishing
and using database object naming conventions is for ongoing supportability.
Intuitively named database objects can be located more easily when troubleshooting
a problem.
Consistently named columns communicate their usage and "class" to
the developer having to reference them. When it comes to the scope of your standard,
I would recommend
that any "home grown" code developed in your company should follow a standard.
Third party products
are outside of your scope, but that doesn't mean the rest of your code need
be neglected.
Stored Procedure Naming Conventions
This article specifically discusses stored procedure naming standards.
If your application
uses stored procedures,
defining a naming convention is absolutely critical for future supportability.
If all of your
Transact-SQL code is embedded in stored procedure, you'll need to know where to
find
exactly what you are looking for. This is particularly important for
databases that contain procedures from multiple applications. You need a
visual means of defining which procedures belong to what application, and have a
general understanding of each procedures purpose.
First off, if I am absolutely positive that a single database will only
be used for a
single,
less complex application, I’ll use the following naming conventions:
usp_ PredominantAction_TableNameOrBusinessProcess
The usp_ prefix is used to let me know that this is a user stored procedure.
Some people don't like this prefix. I prefer it because I can
easily identify stored procedure objects when querying system tables.
I don’t want to be using sp_, as SQL Server interprets this as a system stored procedure.
The PredominantAction
part describes what the stored procedure is intended to do.
I’ve used an array of values over time, and I’m sure
they will keep changing once I migrate to SQL Server 2005, but here
is the list I’ve amassed so far:
SEL – Select – returns multiple rows
GET – Returns 1 row
UPD – Update
DEL – Delete
INS – Insert
EXT – Extract
IMP – Import
SAV – Combines insert/update
The TableNameOrBusinessProcess is used to tell me what the
procedure references or does. If the procedure just works with a
single table, I’ll use the table name. If it works with
multiple tables to perform some kind of operation, I’ll use a
brief process description.
Examples of this naming convention:
usp_SEL_SaleDetail
usp_UPD_Salesperson
usp_GET_ProcessIndicator
usp_IMP_MainframeFiles
If a database is shared across multiple applications or meaningful segments,
I usually add a third element to the stored procedure name:
usp_NNN_ PredominantAction_TableName or Business Process
NNN indicates the project, application, or logical segment
abbreviation. For example if you have a project called “Master Shipping
Application”, you can use MSA as the code, for example:
usp_MSA_INS_ShipOrders
Or if you would like to segment procedures by areas in your application,
you could designate the Sales site as SLS and Employee sit as EMP, for example:
usp_SLS_UPD_Budget
usp_EMP_SEL_AnnualReview
Making it Work for Your Code
This standard allows for much improvisation based on your company standards
and application requirements. No matter what standard you come up with,
do at least choose to standardize stored procedures.
I'm not talking about perfectionism here, just a general respect for how much time
naming conventions will save you and your company down the road.
