August 17, 2016 at 8:49 am
What are some common good practice naming conventions for sprocs? For example, I've seen some naming conventions where the main table name comes first. This is helpful because it groups related sprocs for a table which makes it easier to find sprocs with a particular purpose:
UserCreate
UserReadAll
UserReadById
UserUpdate
UserDelete
Is there a common standard that you can provide a url for?
August 17, 2016 at 8:53 am
Probably most common is verb_object, such as Get_UserName, or some such. I've never seen the "main" table name used as part of the proc name, and it seems like a terrible idea.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 17, 2016 at 8:57 am
There are tools which automate the generation of sprocs for a given database. At least 1 of these tools generate sprocs using the naming convention I mentioned.
August 17, 2016 at 9:18 am
sqlguy-736318 (8/17/2016)
There are tools which automate the generation of sprocs for a given database. At least 1 of these tools generate sprocs using the naming convention I mentioned.
I can't speak for anyone else but that's one reason why I don't use such tools.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2016 at 9:21 am
why hand-code that which can be automated?
August 17, 2016 at 9:29 am
Lots of different standards on this. My one suggestion, regardless of the method you arrive at, be consistent in applying it. Doing some stuff one way, other stuff another, is crazy frustrating.
Based on my own experiences with larger databases (although not some of the monsters I've heard about), I'd lean towards NounAction like MovieListGet or MovieListCreate to retrieve a MovieList or save one. The reason I put the nouns in front is because it will naturally group common objects together. I'm not crazy about the VerbNoun approach because it groups actions, but we seldom just work in INSERT or SELECT. Instead we work on an area of functionality. Being able to quickly go to that area of functionality will give you faster access to the objects.
Just an opinion though. Let's define a standard that is clear, easy to use, and then use it consistently.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 17, 2016 at 9:35 am
yeah I was introduced to the NounVerb way of doing things about 10 years ago. These names were generated automatically by a tool. That particular naming convention really made the most sense to me after that project.
August 17, 2016 at 9:42 am
Grant Fritchey (8/17/2016)
Lots of different standards on this. My one suggestion, regardless of the method you arrive at, be consistent in applying it. Doing some stuff one way, other stuff another, is crazy frustrating.Based on my own experiences with larger databases (although not some of the monsters I've heard about), I'd lean towards NounAction like MovieListGet or MovieListCreate to retrieve a MovieList or save one. The reason I put the nouns in front is because it will naturally group common objects together. I'm not crazy about the VerbNoun approach because it groups actions, but we seldom just work in INSERT or SELECT. Instead we work on an area of functionality. Being able to quickly go to that area of functionality will give you faster access to the objects.
Just an opinion though. Let's define a standard that is clear, easy to use, and then use it consistently.
I will have to think about this option. Currently on things I am working I have found myself doing VerbNoun approach. I hadn't thought of going NounVerb.
August 17, 2016 at 9:52 am
I prefer to name procedures first by topic area as well. I was merely pointing out that it's more common to use verb_object.
Personally I despise a prefix that represents "procedure", such as "usp_" or "up_". That's just as bad to me as prefixing a table with "tbl_". Worst is "sp_" outside the master db because it forces a master db lock and lookup.
I also dislike mixed case. I've worked on case-sensitive servers, where it's a royal pita. Some people write "GetSSN" others prefer "GetSsn". Both could be considered "right", so you have to look up the exact spelling for every db. Instead, I prefer all lower case. Theoretically a rule of "always" capitalize only the first letter of each word would work, but I've found that "rule" gets broken due to common abbreviations (such as SSN vs Ssn).
I also prefer underscores between words because it's much more readable (studies have proven this).
Movie_Get_First_Showing_Date
vs
MovieGetFirstShowingDate
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 17, 2016 at 9:56 am
I also prefer underscores between words because it's much more readable (studies have proven this).
Movie_Get_First_Showing_Date
vs
MovieGetFirstShowingDate
If you're coming from C#, Java, Javascript then the underscores would look hokey and less readable. All methods/properties in the languages I mentioned use standard Pascal casing without underscores
August 17, 2016 at 10:36 am
My personal preference for queries which return recordsets is:
usp_Get[TableName]_By[ColumnList]
EX:
usp_GetStudent_ByLastName
For queries which perform INSERT/UPDATE/DELETE:
usp_UpdateStudent
The INSERT/UPDATE/DELETE are all performed in one single query, generally through an upsert or MERGE statement.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply