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.