What do you think are effective stored procedure naming convention? Especially for CRUD operations. I like this style with the object prefix:


Customer_CreateCustomer
Customer_DeleteCustomer
Customer_UpdateCustomer

I'm not sure about the "Read" part. I'd like to call it Customer_ReadCustomer to be consistent with the CRUD acronym but something doesn't quite feel right about it. Probably the most common variation I've seen is Customer_GetCustomer, but also I've seen Customer_SelectCustomer

And when filtering?

Customer_GetCustomerByCustomerID? (that's a lot of Customer!)

Redundant.

Start with the name of the primary table, so that related procedures are grouped together.

While I am a big advocate of CamelBack naming, I use underscore after the table name to prevent sprocs for similarly named tables from intermingling.

Lose the redundancy.

Examples:
Customer_Create
Customer_Delete
Customer_Update
Customer_Select
Customer_GetByCustomerID

One last suggestion: avoid redundancy.

Try to avoid redundancy.

Do what you can to avoid repeating yourself too.

-PatP

I agree with avoiding redundancy and repetition. Here is a list of the names of stored procedures used by the Microsoft ASP.NET 2.0 User schema:

Notice the use of aspnet_Users_CreateUser and others like it. Why do you suppose Microsoft decided to do this instead of aspnet_Users_Create? Is there some rationale? Is there some reason?

I have come to the conclusion that Microsoft developers are noobs, mostly 15 year old nephews of various Microsoft Marketing staff.

I've been thinking about this. I think the motivation must be to increase clarity about exactly what the stored procedure does. Take the following procedure for example:

aspnet_Roles_DeleteRole

If it was named as:

aspnet_Roles_Delete

Then there would be a slight ambiguity about what it does. Does it delete all roles or single role? With the latter naming you either have to look at documentation or waste valuable brain cycles to remember what it does.

(You could argue to change the prefix from "Roles" to "Role" but that gets into the whole plural vs singular debate which is another thread.)

uh...........

Microsoft allows you to inject the user schema into an arbitrary database. The "aspnet" prefix prevents name conflicts. (Although my guess is you already new this)

i knew it not

i'm not a microsoft type of guy, but if microsoft advocates prefixes like that on your tables, then you'd better do it

"...inject the schema into an arbitrary database..."? I'm afraid I can't parse that syntax.
If you want to prevent conflicts, use a separate legitimate schema rather than a goofy naming convention.

So there'd be a new proc for every variation of a possible where clause??? Is the idea to replicate every SQL command (and variation on the where clause) as a stored proc? I understand you can track what sprocs depend on what tables but it looks unwieldy in my eyes. I'll admit I've never had to deal with a CRUD system though.

I assume that if you needed to create a new user by adding records to 3 tables it would be something like aspnet_UsersInsert, aspnet_UserPermissionsInsert, aspnet_UserRolesInsert rather than just having one simply named proc aspnet_AddNewUser that does all the work.

It just looks odd to me. I always assumed that the best use of sprocs was to group logic together to make the whole application/database easier to use/maintain. Here we seem to be just encasing a single SQL command in a sproc for little gain - for instance (in the above example) where would the logic to add data into 3 tables (as in the example above) be? in another sproc? in the application? nowhere?

Saying that the place where I'm holed up at the moment has 3 capital letters followed by 4 or 5 numbers for the name of each sproc - at least it ties in well with the table names that have a similar convention. Working with such conventions is like having someone permanently scratching their finger nails down a blackboard behind you.... I try to just think of the money.

The list of stored procedures above is not my list, it's Microsoft's. AFAIK, they don't advocate any particular naming convention for your own database objects.

What I meant about injecting the schema, is that you can run a command line tool, aspnet_regsql.exe, with a database name as an argument and it will insert tables and stored procedures dealing with Membership, Users, and Roles into that database. I was using the word schema to refer to the database structure rather than the database construct used to create a namespace. As to why they chose to use a prefix as opposed to an actual schema, I don't know, I'm guessing because SQL Server 2000 doesn't support true schemas?


In any case, you can pretty much ignore the "aspnet" prefix part because it's not important to my question.