Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

usp stored procedure prefix Expand / Collapse
Author
Message
Posted Friday, February 17, 2006 8:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 26, 2010 11:48 PM
Points: 52, Visits: 25

I've been using the usp prefix for years and have been challenged by a collegue about its usage who prefers no prefixes. The only reasons that I can think of,  to continue using it,  are to distinguish it from the system or global procedures (sp). I am not sure how strong these reasons are. Are there other compelling arguments to do so?

Thanks,

Olja




Post #259469
Posted Friday, February 17, 2006 9:43 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:18 PM
Points: 442, Visits: 1,124

Well, this one often falls into the same religious-war category as allowing NULLs in columns; many people don't like to change their ways.  This thread should get rather heated.  Let's see if I can do this without stepping on a lot of toes...

That being said, I'll side-step putting 'usp' in front of procedures and address object prefixes in general.  In general, IMHO, they are rigid and unnecessary.  This affects tables, views, and table-valued functions more than procedures, but once you eliminate prefixes from many of your objects, you should get rid of prefixes for the rest of them.

The problem with table, view, and function prefixes is that tuning strategies and evolving application specs can force an object change.  For example, if I have a table called dbo.tblCustomer, and later turn it into a partitioned view over several tables, then either I leave the 'tbl' prefix on the new view - defeating the purpose of prefixes - or I rename the object to dbo.viwCustomer and go and update every piece of code that references it, a very expensive process to support using prefixes.

This thinking extends to column prefixes as well... ever had to change a column from an int to a decimal, etc.? 

In a more esoteric viewpoint, does the entity 'tblCustomer' hold rows of 'tblCustomers' or rows of 'Customers'.

In prefix-less systems with thousands of procedures, hundreds of tables, plus lots and lots of views and functions, I've found it quite easy to tell the difference in code between tables, procedures and functions.  Adding the prefix to every object just adds noise to the system.

-Eddie



Eddie Wuerch
MCM: SQL
Post #259504
Posted Friday, February 17, 2006 10:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 17, 2014 11:51 AM
Points: 226, Visits: 119
It's the same reason I indent and use extra sapces when I code. It pops out at you and says, "HEY! I'm a stored procedure!" There's no extra thought process. Also, when referencing a sp or vw to a colleague... I find myself saying, "hey check out s-p... " You visualize it alot faster. Maybe I'm just used to it, but people seem to react alot quicker, and it's easier to organize and identify. I think anyone who doesn't do it is just being lazy.
Post #259531
Posted Friday, February 17, 2006 12:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 25, 2014 8:34 AM
Points: 255, Visits: 74

I feel that using forms of hungarian notation is generally a mistake.  As has been previously stated, when changes occur the prefixes are generally not changed or require mass search and replaces. 

Secondly, using any type of SQL tool, all the type of objects are grouped together by type anyway, all the stored procedures are together, the views etc.  By prefacing all your stored procedures with sp it just add noise to the left when looking for a procedure in these lists.

Thirdly, based on the context of an object it is usually obvious what it is:

exec Customer  --a sproc

select * from Customer -- a view or table

select * from dbo.Customer() -- a function

Post #259568
Posted Monday, February 20, 2006 12:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:32 PM
Points: 123, Visits: 105
I agree with Eddie and Jeff 10000 percent, and will go further (not minding stepping on toes) to say that I believe "Hungarian notation" to be a curse on the entire software industry. It is uncalculable how much this gibberish has detracted from code clarity over the years.

That said, it seems less offensive to me in SQL than in many other languages, which are often rendered damn near unreadable.
Post #259977
Posted Monday, February 20, 2006 1:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 17, 2014 11:51 AM
Points: 226, Visits: 119
hey Jeff...

Is Customer a view or table?
Post #259985
Posted Tuesday, February 21, 2006 9:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 25, 2014 8:34 AM
Points: 255, Visits: 74

Does it matter when selecting data from it?  I does matter when doing inserts but the advantages of the ability to refactor parts of the database and not affect existing selects and reports usually wins.  In most of the databases that I develop there is one common procedure to do the insert of data but the table may be used in many other selects and joins.  The ability to refactor code such as:

Customer(CustomerName, Address1, Address2....)

into

tables CustomerName(CustomerName, AddressID) and CustomerAddress(AddressID, Address1, Address2...) and a view Customer(CustomerName, Address1, Address2...) and have no impact on any code that references Customer is immeasurable.  The only place that has to be modified is the procedure AddCustomer(@CustomerName, @Address1, @Address2...)

 

 

Post #260286
Posted Tuesday, February 21, 2006 10:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 17, 2014 11:51 AM
Points: 226, Visits: 119
it matters when you're in a team environment. are you selecting from a view or from a table?
Post #260299
Posted Tuesday, February 21, 2006 10:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 25, 2014 8:34 AM
Points: 255, Visits: 74

Why does it matter in any environment?  If I "Select CustomerName, City From Customer Where CustomerID = 1", when does it matter what the implementation of Customer is?  Do I care if it is one table or many?

Post #260304
Posted Tuesday, February 21, 2006 1:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 17, 2014 11:51 AM
Points: 226, Visits: 119
If I wrote the application and created a view called "customers", and then you came in and you were trying to learn the system.... You would think Customers was a table, and you would waste time trying to figure it out. The prefixes have worked well in every team environment I've been in. If you're working alone, it doesn't really matter does it?
Post #260375
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse