我们在设计企业应用程序和放大器的过程;我们的技术架构师认为，让有在code没有SQL查询，即使到存储过程应在数据库和放大器写入数据库的一个简单的选择调用;在code应该调用它。

we're in the process of designing an enterprise application & our technical architects suggest that "lets have no sql queries in the code. even if its a simple select call to the database a stored procedure should be written for it in the database & the code should call it."

我的问题是，它似乎是一个愚蠢的想法具有的只有的存储过程和放大器;没有查询什么那么整个code！我们的球员相信这个想法，但我有我的怀疑它...你们有什么要说的？

my issue with this is that it seems like a dumb idea to have only stored procedures & no queries what so ever in the whole code! our guys are convinced on this idea but i have my doubts about it... what do you guys have to say?

如果它是一个不错的主意，我喜欢它，如果有人能告诉我理由支持它。

if its a good idea, i'd love it if someone could tell me reasons supporting it.

如果它是一个坏主意，请告诉我什么参数可以present给他们，否则说服他们。

and if its a bad idea, please tell me what argument can present to them to convince them otherwise.

AP preciate你的帮助。

appreciate your help.

THX -ksm

推荐答案

我在具有通过存储过程执行的所有查询的意见

My opinion on having ALL queries executed via stored procedures

第一点：

有一个DAL层不是很常见，因为通过SP的做提供的同样的信心，你的数据库的更改绝不会影响任何在撒谎层除1时，这是在未来的日子非常有用的。

It was REALLY useful in the days when having a DAL layer was not very common because doing it via SP's provided the same confidence that your DB changes would never impact any over lying layers other than 1.

如今，随着DAL层是常态，你使用的是DAL相同的分离，我实在看不出太多的附加价值，具有SP在这方面的。 （有一个DAL层假定你的SQL查询只在您的DAL和无处）

Nowadays, with DAL layers being the norm, you get the same separation using a DAL and i do not really see too much additional value to having SP's in this regard. (Having a DAL layer assumes that your SQL queries are only in your DAL and nowhere else)

第2点：

此外，在旧版本的数据库（ATLEAST的SQL Server 7，甚至2000年），有一个性能优势，由于SP的执行计划缓存，其中作为直接执行SQL会比较慢缘于此。

Also, in older databases (atleast SQL Server 7 and maybe even 2000), there was a performance benefit due to caching of SP execution plans where as executing direct SQL would be slower due to this.

Nowdays，大多数数据库高速缓存的即席查询也和这样的好处也不再是一个标准。

Nowdays, most databases cache ad-hoc queries also and this benefit too is no longer a criteria.

第3点：

另外，在旧系统中，有很多的商业逻辑的用于将要被嵌入在数据库中。 具有嵌入数据库中的业务逻辑意味着写作漫长而复杂的过程。所以，既然程序是反正present，它成为让说这是一致有用让让一切程序

Also, in older systems, a lot of business logic used to be embedded in the database. Having business logic embedded in the database meant writing LONG and complicated procedures. So, since procedures were anyways present, it became useful to make it consistent by saying "lets make everything a procedure"

Nowdays，大多数应用程序从把业务逻辑的任意位置在数据库中避免。 所以这是非常可能的是比SELECTS其它，所述的CRUD语句通常会非常简单明了，在大多数面向对象的系统。

Nowdays, most applications refrain from putting business logic anywhere in the database. So it is very likely that other than SELECTS, the CRUD statements will usually be very simple and straightforward in most object oriented systems.

摘要：我个人认为，迫使SP的每个查询是老式的。有无伤害以这样做。但我pssed想到的任何实际的好处真的很难$ P $来这样做只要你有一个DAL /业务层和数据库引擎足以缓存查询计划的查询

Summary : I personally think that forcing a SP for every query is old fashioned. There is no harm to doing it. But i am really hard pressed to think of any real benefit to doing it provided you have a DAL / Business layer and a DB engine good enough to cache your query plans for your queries