At 01:35 PM 2/16/2004, Tiseo, Paul wrote (in part) >I don't have a real problem with views/sprocs that provide SELECT * like >some seem to have. Furthermore, I can think of many scenarios that will >cause views and/or sprocs to break based on some approach that makes a >developer/DBA formally stipulate the SELECTed fields. Ex: splitting columns, >splitting tables (as J. Merill says), the need to rename a column, etc. >There are many (relatively) common scenarios that will break a formally >commalisted SELECT... more than you indicate.
There are huge differences between a view definition, a stored procedure defn, and client code. Adding a column to a view is just like adding one to a table -- it won't affect any client, unless that client has used "select *" (and isn't coded to handle the extra column(s)). On the other hand, if a stored proc's API is that it returns a single result-set (let's leave out SPs that have multiple result-sets for the moment), making a change to the result-set that the SP returns will almost always break client code -- that's the result of the unfortunate consequence that the result of "exec stored_proc" -- even when the SP returns a single result-set -- is not a "virtual table." You cannot write select desiredcol1, desiredcol2 from (exec stored_proc) or anything similar. That is, the client must always be ready to get all the columns (and all the rows) of the result-set. In many client environments, the client code must explicitly define the number and datatypes of the columns in an SP's result-set, because there's no clue from the fact that you're calling an SP that there'll even _be_ a result-set, let alone how many columns there will be (and what their types are). It is perhaps bad practice, but there are SPs that return different result-sets depending on the values of the parameter(s) passed. In such cases, the client _must_ rely on the SP's API (aka documentation) to know what the result-set will look like. Therefore, you can't change the API for a result-set-returning SP -- in any way -- without potentially breaking client code. Period. But that says nothing about adding columns to a view (or table). If you know you're using SQL Server 2000 or later, SPs that return a single result-set should almost certainly be re-written to be table-valued functions. (See the BOL topic "table-valued functions" that basically says it should always be done.) Adding columns to a table-valued function's result won't cause any problem for properly-coded client apps, just as adding columns won't break code that references tables or views. Traditional single-result-set-returning stored procs are an "ugly stepchild" due to SQL Server's inability (or is it its designers' refusal?) to use their results as virtual tables. J. Merrill / Analytical Software Corp =================================== This list is hosted by DevelopMentorŪ http://www.develop.com Some .NET courses you may be interested in: NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles http://www.develop.com/courses/gaspdotnetls View archives and manage your subscription(s) at http://discuss.develop.com
