坚定地站在“存储过程不利于CRUD/业务逻辑使用”的阵营。我了解在报告、数据导入等方面的需求

写在这里…

Definitely easier to maintain if you put it in a stored procedure. If there's difficult logic involved that will potentially change in the future it is definitely a good idea to put it in the database when you have multiple clients connecting. For example I'm working on an application right now that has an end user web interface and an administrative desktop application, both of which share a database (obviously) and I'm trying to keep as much logic on the database as possible. This is a perfect example of the DRY principle.

存储过程更易于维护，因为:

你不需要重新编译你的c#应用每当你想改变一些SQL 您最终会重用SQL代码。

当您试图构建可维护的应用程序时，代码重复是最糟糕的事情!

当您发现需要在多个地方纠正的逻辑错误时会发生什么?您更容易忘记更改复制和粘贴代码的最后一个位置。

在我看来，性能和安全性的提高是一个额外的加分项。您仍然可以编写不安全/低效的SQL存储过程。

更容易移植到另一个DB -没有pros到移植

在另一个DB中创建所有存储过程并不难。事实上，它比导出表更容易，因为不需要担心主键/外键。

Something that I haven't seen mentioned thus far: the people who know the database best aren't always the people that write the application code. Stored procedures give the database folks a way to interface with programmers that don't really want to learn that much about SQL. Large--and especially legacy--databases aren't the easiest things to completely understand, so programmers might just prefer a simple interface that gives them what they need: let the DBAs figure out how to join the 17 tables to make that happen.

话虽如此，用于编写存储过程的语言(PL/SQL就是一个臭名昭著的例子)是相当残酷的。它们通常不提供您在当今流行的命令式语言、OOP或函数式语言中看到的任何细节。认为COBOL。

因此，请坚持使用仅抽象了关系细节的存储过程，而不是那些包含业务逻辑的存储过程。

你的编程语言和应用程序框架可能是:

高级的，特别是与SQL相比 易于通过自动化流程进行版本和部署，特别是与SQL相比

如果这两个条件是两个，则跳过存储过程。

CON

我发现在存储过程中进行大量的处理会使您的DB服务器在扩展您的行为时成为一个单一的不灵活点。

然而，如果您有多个服务器运行您的代码，那么在您的程序中进行所有这些处理而不是sql-server，可能会允许您进行更多的扩展。当然，这并不适用于只进行正常获取或更新的存储procs，而是适用于执行更多处理(如在数据集上循环)的存储procs。

PROS

Performance for what it may be worth (avoids query parsing by DB driver / plan recreation etc) Data manipulation is not embedded in the C/C++/C# code which means I have less low level code to look through. SQL is less verbose and easier to look through when listed separately. Due to the separation folks are able to find and reuse SQL code much easier. Its easier to change things when schema changes - you just have to give the same output to the code and it will work just fine Easier to port to a different database. I can list individual permissions on my stored procedures and control access at that level too. I can profile my data query/ persistence code separate from my data transformation code. I can implement changeable conditions in my stored procedure and it would be easy to customize at a customer site. It becomes easier to use some automated tools to convert my schema and statements together rather than when it is embedded inside my code where I would have to hunt them down. Ensuring best practices for data access is easier when you have all your data access code inside a single file - I can check for queries that access the non performant table or that which uses a higher level of serialization or select *'s in the code etc. It becomes easier to find schema changes / data manipulation logic changes when all of it is listed in one file. It becomes easier to do search and replace edits on SQL when they are in the same place e.g. change / add transaction isolation statements for all stored procs. I and the DBA guy find that having a separate SQL file is easier / convenient when the DBA has to review my SQL stuff. Lastly you don't have to worry about SQL injection attacks because some lazy member of your team did not use parametrized queries when using embedded sqls.