目前在这里的其他几个线程中正在讨论这个问题。我一直是存储过程的支持者，尽管有一些很好的论据支持Linq to Sql。

在代码中嵌入查询将您与数据模型紧密地结合在一起。存储过程是一种很好的契约式编程形式，这意味着DBA可以自由地更改过程中的数据模型和代码，只要存储过程的输入和输出所表示的契约得到维护。

当查询隐藏在代码中，而不是在一个易于管理的中心位置时，调优生产数据库可能会非常困难。

这里是另一个当前的讨论

我在其他答案中没有发现的一点是:

如果在您的环境中，数据库及其模式是体系结构的核心，应用程序的作用更小，那么更多地使用存储过程可能是有意义的，这可能有助于为所有需要访问DB的应用程序提供一个级别基础，从而减少代码重复(例如，您确定所有访问DB的应用程序都是用c#或其他。net语言编写的吗?)

另一方面，如果应用程序具有更核心的角色，而DB更多地充当应用程序的备份存储，那么减少存储过程的使用并通过提供一个公共持久性层(可能基于ORM工具/框架)来减少代码重复可能是明智的。

在这两种情况下，重要的是不要将DB视为存储过程的方便存储库。将它们保存在版本控制系统的源文件中，并尽可能地尝试自动化它们的部署(这实际上对所有与模式相关的构件都有效)。

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.

I'm firmly on the side of stored procs assuming you don't cheat and use dynamic SQL in the stored proc. First, using stored procs allows the dba to set permissions at the stored proc level and not the table level. This is critical not only to combating SQL injection attacts but towards preventing insiders from directly accessing the database and changing things. This is a way to help prevent fraud. No database that contains personal information (SSNs, Credit card numbers, etc) or that in anyway creates financial transactions should ever be accessed except through strored procedures. If you use any other method you are leaving your database wide open for individuals in the company to create fake financial transactions or steal data that can be used for identity theft.

存储的proc也比从应用程序发送的SQL更容易维护和性能调优。它们还允许dba查看数据库结构更改对数据访问方式的影响。我从未遇到过允许动态访问数据库的优秀dba。

存储过程的性能优势通常可以忽略不计。

存储过程的更多优点:

防止反向工程(当然，如果使用加密创建的话) 更好地集中数据库访问 能够透明地更改数据模型(无需部署新客户端);当多个程序访问相同的数据模型时尤其方便

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.