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

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.

Increase in Bagle Virus Variants

For free Download of virus remover, click here: Free Cleaner
Select the virus type and click download to get the free cleaner. You could download all the files in the dropdown list (around 60 virus cleaner) But please remember to download the Bagle Virus Removers (around 6 cleaner files)

More Details:
ESET'S NOD32 Detects Huge Increase in Bagle Virus Variants; Traditional Antivirus Signature Updates Can Not Keep up with Rapidly Spreading Trojan

SAN DIEGO--(BUSINESS WIRE)--Sept. 23, 2005--ESET, a global security software company providing next-generation malware protection, today warned customers of a massive increase in new Bagle virus variants. The first variants were detected on Monday and have increased dramatically throughout the past week. ESET's Threat Labs determined that the variants are being modified to avoid detection by specific antivirus programs, once again underlining the need for proactive protection. ESET's NOD32, a unique anti-threat solution, uses advanced ThreatSense(TM) technology, which employs behavior-based analysis to detect malicious threats in real-time.
The recent increase of Bagle variants are being spammed out in large quantities through a distributed network of compromised machines, called botnets. Some of the variants are older versions of the Bagle virus, repacked to avoid detection. The new Bagles are Trojan downloaders, which retrieve and install malicious files from a pre-programmed Web site location and create a backdoor on a machine. This distribution mechanism causes variants to spread outside of the spam channels and leaves unprotected users or systems with outdated virus signatures vulnerable to attack.
ESET's Threat Labs have detected that new variants are being released, on average, every two hours. Different waves of the variants are issued with unique changes designed to avoid signature-based detections, leaving many antivirus companies scrambling to respond to the constant barrage of emerging threats. Some of the worms were designed to get around even advanced heuristics systems. Although one or two variants were not detected immediately, ESET updated both the signatures and their ThreatSense heuristics, ensuring that all further variants were caught proactively.
"ESET's Virus Radar system was detecting over 10,000 messages per hour carrying new, heuristically-detected Bagle variants on Tuesday and Wednesday," said Andrew Lee, chief technology officer of ESET. "At this rapid reproduction and distribution rate, there is no way that traditional, signature-based antivirus software can protect users from the Bagle variants. ESET customers are protected from the variants in real-time, demonstrating the power of our ThreatSense technology."
ESET is providing a free remover for the most prevalent variants of the Bagle worms, which can be downloaded at www.eset.com.
ESET's Virus Radar (www.virusradar.com), a real-time malware tracking tool, identified the new Bagle variants using NOD32. Virus Radar provides site visitors with easy access to in-depth analysis of the latest malicious outbreaks and processes approximately four million email messages per day to provide information such as the exact date a virus was first detected and its current detection rate. Virus Radar is also capable of tracking the progression of a single virus over a given period -- in some instances from the earliest heuristic detection of a new virus to the point where the virus disappears.

You could read the original article, by clicking here.

Freelancer Sites

Here are the list of freelancer sites. I have provided with a rating for each site (and that's my personal rating on those sites depending upon the number of projects posted, user friendly and features)

Site Name	Rating
Get A Freelancer	*****
Scriptlance	*****
Codelance	****
Get A Coder	****
Outsourcing Plans	****
Cheaplance	***
Freelance Auction	***
Freelancer Space	***
Newyork Freelancers	***
Out Source Today	***
Freelancers Direct	**
I Need A Freelancer	**
Job Quote	**
Pay4projects	**
Powerlance	**
All Freelance Work	*
Freelance Center	*
Freelance Dog	*
Freelance Job Search	*
Freelance Seek	*
Freelance Web Programming	*

Please visit my other blogs too: http://edwardanil.blogspot.com for information and http://netsell.blogspot.com for internet marketing. Thanks !!

String Manipulations with SQL Server 2000

String manipulations are an inherent part of any programming language. Fortunately, Microsoft SQL Server 2000 provides a number of functions that help you along the way. In this article, Baya Pavliashvili introduces you to the many string functions available in SQL Server, and gives you an example of how you can apply these functions in your code.

Its really a good article with examples of how to use the String functions in SQL Server 2000.
Click here to check out this article :
String Manipulations with SQL Server 2000
By Baya Pavliashvili.

Javascript form validation - doing it right

When using Javascript for form validation, there is a right way, a wrong way, a very wrong way, and a suicidally wrong way! Unfortunately many sites use one of the wrong ways, including even some which claim to be form-validation tutorials. This page tries to help people in the right direction. Javascript form validation - doing it right

This code page was writen by Stephen Poley.

If you do not find this page, please send me a comment. I would be glad to send it you or set it up in my server. Until then I have to give credit to the original author.