SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


usp stored procedure prefix


usp stored procedure prefix

Author
Message
Olja Dimitrijevic
Olja Dimitrijevic
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 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





Eddie Wuerch
Eddie Wuerch
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 2200

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
kevin mann
kevin mann
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 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.
JeffB
JeffB
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 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


Fred Williams-134192
Fred Williams-134192
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 118
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.
kevin mann
kevin mann
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 119
hey Jeff...

Is Customer a view or table?
JeffB
JeffB
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 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...)


kevin mann
kevin mann
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 119
it matters when you're in a team environment. are you selecting from a view or from a table?
JeffB
JeffB
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 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?


kevin mann
kevin mann
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search