When should I use stored procedures?

Question

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
• https://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code
• https://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?

Answer 1

You have a couple of perfectly good scenarios already.

There are lots of other reasons too. EF is really good at CRUD and at pretty straight forward reporting. Sometimes, though, EF is not the perfect tool. Some other reasons (scenarios) to consider using stored procedures in combination with Entity Framework would include:

• You have complex units of work, perhaps involving many tables, that cannot be wrapped in a transaction easily using the features of EF.
• Your database does not play well with EF because it fails to take advantage of declarative referential integrity (foreign key constraints). This is usually a bad scenario to find yourself in, but there are sometimes appropriate scenarios, such as databases used for ETL processes.
• You need to work with data that crosses server boundaries with linked servers.
• You have very complex data retrieval scenarios where "bare metal" SQL is needed in order to ensure adequate performance. For example, you have complex joins that need query hints to work well in your specific situation.
• Your application does not have full CRUD permissions on a table but your application can be allowed to run under a security context that your server trusts (application identity, rather than user identity, for example). This may come up in situations where DBAs restrict table access to stored procs only because it gives them more granular control over who can do what.

I'm sure there are many more besides these. The way to determine the best path forward in any particular circumstance is to use some common sense and to focus on the primary goal, which should be to write high quality, easily maintainable code. Pick the tool(s) that give you this result in each case.

Answer 2

Perhaps it makes sense, to turn the question around and find usecases where only stored procedures could do, what you want to achieve. Perhaps there are really use cases, where stored procedures stand out.

If it makes a difference, I am using MSSQL and Entity Framework.

My knowledge on EF is limited, but as far as I can see, EF is (just) an ORM like any other; and fortunately is capable of using raw SQL.

If I take your two major points:

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.

LINQ / EF was falling short, when doing a report. And as you noticed, was SQL way faster, than using an ORM. But speaks this in favour of stored procedures or only against using the ORM for everything?

Your problem could obviously solved with SQL. If that query was stored and version controlled in your codebase makes - according to your example - at least no difference.

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.

Same thing here: a simple connection string and and UPDATE and your problem is done. This problem could be solved even with an ORM: simply use a webservice in front of the other DB and the same compartementalization / isolation is achieved.

So nothing to see here.

Looking at some points others have made:

You have complex units of work, perhaps involving many tables, that cannot be wrapped in a transaction easily using the features of EF.

But SQL can do that. No magic involved here.

Your database does not play well with EF

Again: Use EF when appropriate.

You need to work with data that crosses server boundaries with linked servers

I do not see, how stored procedures help. So I can not see an advantage of stored procedures; but perhaps someone sheds some light on that.

You have very complex data retrieval scenarios where "bare metal" SQL is needed in order to ensure adequate performance

Again: "Boundaries of EF".

Your application does not have full CRUD permissions on a table but your application can be allowed to run under a security context that your server trusts

Okay. I go with a maybe.

So far only a half point was made in favor of stored procedures.

---

Perhaps there are performance considerations, which speak in favour of stored procedures.

1) Storing queries has the benefit of a simple call to the stored procedure which encapsulates the complexity. Since the query planer knows the query, it is "easier" to optimize. But the saves are with the current sophisticated query planer _minimal.

On top of that, even if there is a slight cost using ad hoc queries, if your data is well structured and carefully indexed, the database is merely the bottleneck of your application. So even if there is a small delta, it is neglectable considering other factors.

2) Nevertheless it was argued for storing complex queries in a DB. There are two things to consider:

a) complex queries make massive use of the DB infrastructure, which increases asnwering times for every other query. You can not run many costly queries in parallel. This speaks neither pro nor contra stored procedures, but against complex queries.

b) If the query takes time anyways, why bother with small speed wins of a stored procedure.

tl;dr

Nothing speaks directly against stored procedures. So using stored procedures is okay - if it makes you happy.

But on the other hand: I couldn't imagine a proper use case which speaks unequivocally pro.

When should I use stored procedures?

The proper answer is: Whenever you like. But there are other options.

Answer 3

For your specific case, since you are using the entity framework, one should consider using stored procedures when the entity framework fails to meet a requirement or concern.

The entity framework does a decent job and performance will be close or equal to using stored procedures. You chose the entity framework because you did not want to be concerned with SQL and will let the framework generate the SQL for you.

But there may be an edge case where the entity framework falls short and cannot meet your needs. In this case one would write the SQL by hand using a stored procedure or parameterized query and then use the entity framework to call that stored procedure/SQL statement directly.

So, I wouldn't move anything to a stored procedure unless the framework that is being employed cannot meet the requirement.

I think the worst thing that can happen is that one choses to use the entity framework and then writes all stored procedures. Now one has an extra layer that adds no value.

Answer 4

Having a technical need is not the most likely choice. Programmers prefer their tools and are usually more adapt at solving their problems with them. Putting logic in a sproc will be the exception. Technical debt and future devs having to take time to work with an exception should be considered. There may be a performance boost in your particular RDBMS that is just easier to implement in a sproc or maybe even another db object like an indexed view.

There will be times when you need data from a database and you just can't get it from your application code. In many large company/enterprise situations, business needs can exceed the IT Department's grasp. Companies get bought and sold and their applications come and go with them. Regulating agencies and banks don't care if you can't build your highly-scalable and beautifully coded app. They can make life difficult for the business, so you have to get it done.

I've run into situations where Third-party applications or report writing tools don't let you get at your code. No open source code, no API, no web interface and no services. The only thing they have is their own special report writing tool that only works with database objects: tables, views, sprocs, user-defined functions, etc. You put the logic wherever you can get at it.

If you have a DBA that is very good at writing stored procedures, you can leverage that talent in some situations. You may want to trigger a backup from your app because you're going to make a major data change, so why not use what your dba uses?

Some Ad hoc requests are just easier to write a proc until you can get all the functionality in your app. We hate it. We push back, but the show must go on.

Answer 5

I would strongly advice against placing any business logic in a stored procedure. However, there might be a case (you listed two fine examples) where it is preferable to place data access logic there.

Generally speaking, if you feel tempted to use SP's it is a sign (a code smell) hinting that your data model is not well suited to its needs.

Edit: When developers ask me about business/data access logic, I say that business logic is about how data behaves and interacts, while data access logic is about how data is stored and retrieved.

For exemple: In your domain model you might have the entities "Student" and "Teacher", both derived from "Person". (Not saying this is preferred, just an example). This can be stored in a relational databas as one, two or three tables. The choice depends on how many properties they share, and on read/write performance requirements.

In Business logic it should not matter how those entities are stored. (or wether they reside in a RDB at all). Data access logic is all about how they are physically stored.

So, regarding the original question: If a stored procedure makes storing and retrieving data more efficient (or more robust) you have a case. If the stored procedure is merely for imposing a rule that i valid regardless of how data is stored, avoid it. It makes your code more tightly coupled to the database.

Answer 6

I think there are three issues here.

1. Business logic in SQL is bad. Even if it runs faster individualy, it doesnt scale.

2. Tradiionally SPROCs should always be used for all data access as an abstraction layer. Enabling a DBA to introduce performance or other datalayer changes without affecting the consuming applications.

3. EF does not play nice with sprocs. You will lose a lot of the 'good' features and productivity gains by switching away from the dynamic query generation of Linq.

So my overall advice would be to

• Use SPROCs for all SQL queries,

• Don't put business logic or any kind of loops in them,

• Don't use EF.