June 12, 2007 at 1:46 pm
Well, as developers we always run across problems that are too complex for views and we implement SP's. What I really mean is: Do you use SP's all the time and views never? Or, do you fit pick the retrieval method based on the situation.
I ask because as a SQL Developer I pretty much always build the schema, implement procedures for inputs & business logic & retrieval, and my .NET guys simply consume them using the defined parameters. There are lots of times when only a procedure will do (complex logic based on local variables, dynamic SQL, etc.), but I tend to neglect views even for simple retrievals where they would be sufficient.
I've asked my .NET guys (as in web developers) and they say whether they are retrieving via views or SP's makes no difference for them as long as they aren't haven't to write the SP's.
Yet, I've looked lately at systems designed by developers much more experienced than I (not necessarily in DB...but in total years they definitely have me) and one such system uses views whenever possible and only resorts to SP's when absolutely necessary.
So, my question for the SQL community is simply, how are all of you balancing your SP's and Views for data retrieval? Am I making something more difficult or less extensible (or any other signficant CON) by not using views?
June 12, 2007 at 2:09 pm
Personal preference... I always try to use procedures rather than letting the application query views. If down the line I need to do other actions and/or check more complex settings before returning the data I can just edit the procedure... we don't need to work that logic into the app.
June 12, 2007 at 2:46 pm
I am a development DBA, and now somewhat production as well.
I implement views all the time; however, I don't want app code accessing anything other than a stored procedure. Where the schema is complex, I use views more or less as subroutines to my stored procedures. I see no reason for the app to access views directly.
Of course, there are exceptions to any rule, but in general, procs should be perfect.
- - Herb
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply