You're right, that's why I said I am not religious about any of the approaches other than that it is very important to follow the architectural design for a given system.

I don't feel that SPs are more or less functional than views on the server. However I do feel that following the design specs is critical to a successful system implementation. So I am agreeable to whatever approach the DBA team prefers.

Our major system uses a seperate database that has views for every table in the other databases. Nothing from the middle layer ever touches any table directly, all access is through the single database that has the views. This dataabse, as well as the others, has a bunch of SPs that get fired by the business layers data access layer. So the system is truly a hybrid using views, SPs, and a data access layer.

I feel strongly that this is not a right or wrong issue. Neither point of view (views or SPs) has any lock on performance or functionality. The only thing I see as worng about either perspective is feeling that one of them is somehow better than the others.


>>>Lots of people still push that idea. As I understand it, SQL Server 6.5 stored execution plans with Stored Procedures and not dynamic queries. I assume that they studied 6.5 in some institutional setting and like most such people I've met, are unwilling/unable to think for themselves and rely on vague memories of outdated training.
>>
>>Mike,
>>
>>I do not beleive that SPs are necessarily faster than SPT queries. However, using SPs does allow for a degree of isolation of the details of the data model implementation from the business layer. SPs allow the DBA to provide an interface to the data that is not dependent on the table names or the field names. This can allow the DBA to restructure the data for optimization without needing any changws to the business layer.
>
>Views would do the same.
>
>>
>>That being said, I do not follow the concept of using only SPs to access data. I follow whatever the system architecture is for the system I am working on. Sometimes it uses SPs exclusively and sometimes it uses a data access layer built in the application to isolate the data from the app. For reporting I see no reason to not use SPT to get the data, other than that the architecture design says not to.
>
>I'm for business logic in the middle layer, and no business logic in the data layer.
>
>Problem is, from what I've seen, the "architects" are more religious and blind and not open to discussion about improvements. Every DBA and many consultants I've ever encountered has this SP mythology stuck so deep, it's like I'm a heretic.