Whilst I agree with the general tenor of the article I have found that the suggested naming convention can become unwieldy when there are a large number of stored procedures in a database (say more than a couple of hundred).
In this situation it becomes difficult to locate particular stored procedures, especially if they are doing something out of the ordinary. Hence for larger databases we now use a naming convention with a prefix which defines the area that the stored procedure relates to (for example Stk... for a stock control section, Del... for a delivery section, Enq... for enquiries, etc.). The remainder of the stored procedure's name can then either continue as suggested in the article or as per some other convention.
Regards,
David SavilleAldex Software Ltd.www.aldex.co.uk
If you need to divide the procedures into different functional areas and you're using SQL 2005 then you might want to look at using schemas; conventional best practice prior to 2005 says that all objects should be owned by dbo so you probably don't want to head in that direction.
I agree with the concept of naming procedures with the predominant table or view being accessed and describing the action being performed but I would reverse the order of the two. Having the table/view name first makes it easier to identify the procedures that need to change when the table/view is changed since they will all sort together.
As Ian said, I would also throw out the "usp_" prefix; it's just noise and doesn't pose a problem unless you have a table or view called "sp" (unlikely).
This was a good, simple presentation of a common topic. It would be refreshing to have someone write an article that looked at several naming schemes and compared them. I am partial to mine but would be open to other suggestions. I have promised an article or two already and not delivered so I'm not going too far out on a limb here
I agree with Ian and Bryant regarding using a prefix such as usp_ to define a stored procedure. I always use object type prefixes where there is potential for ambiguity, so for example I would still differentiate Tables and Views (eg tblCustomers or vwCustomers). However a Stored Procedure cannot readily be confused with any other object so the rationale for an object type prefix dissapears.
I also note that Microsoft are moving away from object type prefixes in, for example, the later versions of Visual Studio. But maybe this is because there are now so many objects that a simple prefix is not enough to disambiguate them (with thanks to Ken Getz who introduced me to this 'word' during one of his forays to the UK) :
Hi,
I figured this article would get some discussion! I like the usp_ convention - but I know of those who do not. 100 DBAs = 100 Naming conventions. That was why I was a bit afraid to write this article.
In any case, walking into a new client's site - I'm just happy if they are actually following any convention in the first place (and if they are sticking to it). Usually I find that this is almost never the case.
Best Regards,
Joe
