Reusability of stored procedures

[MikeP]

I've been in a spirited debate with a co-worker over the reusability of stored procedures. The two positions are as follows:

 

He states that all "select" procedures should use "select *" versus selecting specific fields, in order to be able to reuse the stored procedure by other apps. Also, if the table structure changes, you are covered.

 

My position is that I shouldn't have to clog my bandwidth and ram with fields stuff that my app doesn't need. Also, with very rare exceptions, there aren't many situations where the selection criteria is broad enough to be used by more than one application. Plus, pulling all the additional information could result in a performance hit.

 

Both arguments have merit. What say you? :cool:

[samsmithnz]

I say only select the fields you need. Why?

 

1. If the table structure does change, having the columns specified guarantees that new columns will not affect you, using a select * means that you will get unknown columns... which can lead to unknown events (a bound data grid for example)

 

2. Your apps are always trying to be fast. If you use Select * you are clogging up bandwidth for no reason.

 

3. Also when you look at what you have, there are very few select * from table stored procedures, you're more likely to want to sort in a specific direction or group by a special field, or more commonly join with another table.

 

P.S. When I get in silly arguments like this I just like jumping around the person singing "your wrong! your wrong!" just cos its funny. :rolleyes:

[Robby]

I'd have to agree with you Mike (Sam too), although I have used the generic method a couple of times in the past, as of late (with .NET) the way I setup datagrids and business logic I need to map to specific columns and don't really care about a one-size-fits-all philosophy.

[JABE]

For me, performance is the #1 reason why I wouldn't use SELECT *; if your co-worker is after reusability of SELECT outputs and protection against schema modifications, database views may be a better option.

[mocella]

I say only select the fields you need. Why?

P.S. When I get in silly arguments like this I just like jumping around the person singing "your wrong! your wrong!" just cos its funny. :rolleyes:

 

Or, just march this guy down to the DBA group and have him pitch "SELECT*" for all SELECT stored procedures and let the DBAs deal with him! :cool:

[PlausiblyDamp]

Another problem with select * is that changes to underlying tables can have hard to track down side effects.

Saw this happen once - overnight a particular page in a web application had gone from approx 5 seconds to about 3 minutes to display.

some bright spark had used SELECT * to retreive information which was used to populate a couple of drop down lists. The amount of data returned wasn't large about 5 -10K. Cause of slowdown - somebody added a picture field to the table, the 5-10K to retrieve the entire table jumped to about 500K!

[Arch4ngel]

My opinion is :

 

Use specific instruction. In most case, we don't use all fields... and if you have to add some just add a ", tab2.fields2" and your in. See ? Not to much of a problem and you decide the order of columns.

 

But for those who preach for the easiest... I could only blame you :p

[CattleRustler]

I agree with Sam, not only with the sproc issue, but with the jumping around and saying "you're wrong - you're wrong" just cuz it's funny :)