Never do in code, what you can get the SQL Server to do well for you
When should one consider to perform database specific operation in your code base?
RDBMS are build to handle a common data storage and manipulation operations such as:
- Filtering Data
- Aggregating Data
- Relationship Integrity
believe it or not some architectural practices force you do all the above in code base with tons of lines of code with no added value.
Advantages of Stored Procs
- Stored procedures give you code reuse, and encapsulation - two pillars of software development
- Security - you can grant/revoke permissions on an individual stored proc
- Protects you from SQL injection attacks
- Help with speed
One false assumption in most of the above architectures styles is, data always being accessed via application, which is not true with modern ORM frameworks/Technology, such as:
- SQL-To-XML CodeProject Implementation
- Entity Framework Data Source (WCF Data Service)
- ADO.NET Data Service (Currently not part of VS 2013, hope to see it back in future versions)
- More to come....
The intention behind inventing/discovering SQL Server is to delegate much of the heavy lifting to the SQL Server (for things it is good at) and reduce the amount of Application Server/ IO as much as possible and let SQL do the job and returning smaller data set.