Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Stored Procedure Naming Conventions

By Joseph Sack,

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.

Total article views: 22398 | Views in the last 30 days: 19
 
Related Articles
FORUM

Standard Store procedure look like

Correct code for standard store procedure

FORUM

stored procedures in a database

stored procedures in a database

FORUM

how to run a stored procedure from C# application

how to run a stored procedure from C# application

FORUM

Stored Procedure & 'other' database

How to have a Stored Procedure work on another database

FORUM

how to run stored procedure from C# application

how to run stored procedure from C# application

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones