If I have all my business logic in code and make use of Entity Framework, in what situations (if any) would I be better moving some business logic to a stored procedure, instead of keeping it all in code?
To be clear, I mean in conjunction with the current setup (business logic in code), not instead of. I have seen a number of similar questions that are asking for the pros and cons of having all business logic in stored procedures, but I haven't found much regarding using stored procedures sparingly for edge case logic, while keeping the rest of the business logic in code.
If it makes a difference, I am using MSSQL and Entity Framework.
These are the situations where I have used stored procedures before:
- A complicated report that was taking minutes to run (this was a page in a web app). I found I could write SQL that was much more efficient (only taking seconds to run) than what LINQ was providing.
- A web application needed to read and write to a few tables on a separate database which contained a lot of other, sensitive information that was irrelevant to the application. Rather than giving it access to everything, I used a stored procedure that only does what is needed, and only returns limited information. The web application could then be given access to this stored procedure only, without access to any tables etc.
Other posts I have looked at before asking this question:
- Stored Procedures a bad practice at one of worlds largest IT software consulting firms?
- Pros and Cons of holding all the business logic in stored procedures in web application
- http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code
- http://dba.stackexchange.com/questions/2450/what-are-the-arguments-against-or-for-putting-application-logic-in-the-database/2452#2452
- When not to use ORM and prefer stored procedures?