Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2008
    Posts
    52

    Stored procedure naming conventions?

    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!)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Try to avoid redundancy.

    Do what you can to avoid repeating yourself too.

    -PatP

  4. #4
    Join Date
    Mar 2008
    Posts
    52
    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:

    Code:
    aspnet_Applications_CreateApplication
    aspnet_Membership_ChangePasswordQuestionAndAnswer
    aspnet_Membership_CreateUser
    aspnet_Membership_FindUsersByEmail
    aspnet_Membership_FindUsersByName
    aspnet_Membership_GetAllUsers
    aspnet_Membership_GetNumberOfUsersOnline
    aspnet_Membership_GetPassword
    aspnet_Membership_GetPasswordWithFormat
    aspnet_Membership_GetUserByEmail
    aspnet_Membership_GetUserByName
    aspnet_Membership_GetUserByUserId
    aspnet_Membership_ResetPassword
    aspnet_Membership_SetPassword
    aspnet_Membership_UnlockUser
    aspnet_Membership_UpdateUser
    aspnet_Membership_UpdateUserInfo
    aspnet_Profile_DeleteInactiveProfiles
    aspnet_Profile_DeleteProfiles
    aspnet_Profile_GetNumberOfInactiveProfiles
    aspnet_Profile_GetProfiles
    aspnet_Profile_GetProperties
    aspnet_Profile_SetProperties
    aspnet_Roles_CreateRole
    aspnet_Roles_DeleteRole
    aspnet_Roles_GetAllRoles
    aspnet_Roles_RoleExists
    aspnet_Setup_RemoveAllRoleMembers
    aspnet_Setup_RestorePermissions
    aspnet_Users_CreateUser
    aspnet_Users_DeleteUser
    aspnet_UsersInRoles_AddUsersToRoles
    aspnet_UsersInRoles_FindUsersInRole
    aspnet_UsersInRoles_GetRolesForUser
    aspnet_UsersInRoles_GetUsersInRoles
    aspnet_UsersInRoles_IsUserInRole
    aspnet_UsersInRoles_RemoveUsersFromRoles
    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?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I have come to the conclusion that Microsoft developers are noobs, mostly 15 year old nephews of various Microsoft Marketing staff.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2008
    Posts
    52
    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.)

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tmpuzer
    I agree with avoiding redundancy and repetition.
    Code:
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    aspnet_...
    ...
    uh...........
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2008
    Posts
    52
    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)

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by tmpuzer
    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)
    "...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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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!)
    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.

  12. #12
    Join Date
    Mar 2008
    Posts
    52
    Quote Originally Posted by r937
    ...but if microsoft advocates prefixes like that on your tables, then you'd better do it
    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.

    Quote Originally Posted by blindman
    "...inject the schema into an arbitrary database..."? I'm afraid I can't parse that syntax.
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •